aiDeeva.com

Tag: Script

  • Insert data from one table to another table

    You can insert the data from one table to another table using SELECT INTO and INSERT INTO with SELECT.. FROM clause.


    — Below statement will create the temp table to insert records
    select * INTO #tmpObjects from sys.sysobjects where type = ‘u’

    — Below statement will create the user table to insert records.
    — First will create the table and insert it details as well in new table
    select * INTO tmpObjects from sys.sysobjects where type = ‘u’

    –Below statement will insert new data into table
    insert into tmpObjects SELECT * from sys.sysobjects where type = ‘s’

  • Script to Verify the litespeed backup file

    Use the below script to verify the litespeed backup file

    exec master.dbo.xp_restore_verifyonly
    @filename = N'\\backups\full\mydbbackup.full.BAK',
    @filenumber = 1,
    @logging = 0

  • Script to list out important properties of database

    Use below script to list out the important properties of the database

     


    select
    sysDB.database_id,
    sysDB.Name as 'Database Name',
    syslogin.Name as 'DB Owner',
    sysDB.state_desc,
    sysDB.recovery_model_desc,
    sysDB.collation_name,
    sysDB.user_access_desc,
    sysDB.compatibility_level,
    sysDB.is_read_only,
    sysDB.is_auto_close_on,
    sysDB.is_auto_shrink_on,
    sysDB.is_auto_create_stats_on,
    sysDB.is_auto_update_stats_on,
    sysDB.is_fulltext_enabled,
    sysDB.is_trustworthy_on
    from sys.databases sysDB
    INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid

  • Script to change the database compatibility level

    Use below script to change the database compatibility level

    ALTER DATABASE DatabaseName
    SET SINGLE_USER
    GO
    EXEC sp_dbcmptlevel DatabaseName, 90;
    GO
    ALTER DATABASE DatabaseName
    SET MULTI_USER
    GO

    SQL Server Version Compatibility Level
    SQL Server 6.5 65
    SQL Server 7.0 70
    SQL Server 2000 80
    SQL Server 2005 90
    SQL Server 2008 100