Category: SQL Scripts

  • Script to get the SQL Server Installation Date and Authentication Mode

    Recently I got a request where I have to check the SQL Server installation date and SQL Server authentication mode. I have write the below script and execute it against all the servers registered in CMS.

    Here is the script

    select createdate as InstallationDate ,
CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
WHEN 1 THEN 'Windows Authentication'   
WHEN 0 THEN 'Windows and SQL Server Authentication'   
END as [AuthenticationMode],
SERVERPROPERTY('servername') as svrName 
from master..syslogins where name like 'NT AUTHORITY\SYSTEM'

  • How to attach a SQL Server database with a missing transaction log file?

    There may be situation where the transaction log file is corrupted or delete due to some issue, there is still hope to recover the database using data file. In the below script we will create the scenario and recover the database using data file only.

    -- create the below sample database
create database [SQLDBPool_5] 

-- execute the command to get the data log file location
sp_helpdb sqlhelpdesk

-- Copy the location of log file and data file from the output of the above command
-- C:\JSpace\sqldbpool_5_log.ldf, C:\JSpace\sqldbpool_5.mdf

-- Detatch the database
USE [master]
GO
ALTER DATABASE [SQLDBPool_5] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'SQLDBPool_5'
GO

-- Delete the log file
xp_cmdshell 'del "c:\JSpace\SQLDBPool_5_log.ldf"'


-- attach the database using MDF file only SQL Server will create the log file its self
USE [master]
GO
CREATE DATABASE [SQLDBPool_5]  ON 
( FILENAME = N'C:\JSpace\sqldbpool_5.mdf' )
FOR ATTACH
GO

-- You will the below message on the execution of the above command
/*
File activation failure. The physical file name "c:\JSpace\SQLDBPool_5_log.ldf" may be incorrect.
New log file 'c:\JSpace\SQLDBPool_5_log.ldf' was created.
*/

  • LiteSpeed – Restore Script

    Script to check the backup file – data and log file information

    exec master.dbo.xp_restore_filelistonly
@filename ='<BackupFilePath>\<BackupFileName.bak>'

    Script to check the backup file header information

    exec master.dbo.xp_restore_headeronly
@filename ='<BackupFilePath>\<BackupFileName.bak>'

    Script to verify the backup file if the backup file is valid or not

    EXEC master.dbo.xp_restore_verifyonly @filename ='<BackupFilePath>\<BackupFileName.bak>'

    Script to restore database using Full backup with the default options

    exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>'

    Script to restore database using Full backup with file move option

    exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>',
@with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"'

    Script to restore database using Full backup with replace option

    exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>',
@with = 'replace',
@with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"'

    Script to restore Full backup with no recovery

    exec master.dbo.xp_restore_database
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.bak>',
@with = 'replace',
@with = 'move "logical filename" to "physical file location.mdf"',
@with = 'move "logical filename" to "physical file location.ldf"',
@with='NORECOVERY'

    Script to restore log backup with no recovery

    EXEC master.dbo.xp_restore_log
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.trn>',
@with ='NORecovery'

    Script to restore log backup with recovery

    EXEC master.dbo.xp_restore_log
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.trn>',
@with ='Recovery'

    Script to do point in time recovery

    EXEC master.dbo.xp_restore_log
@database = '<dbname>',
@filename = '<BackupFilePath>\<BackupFileName.trn>',
@with ='Recovery',
@with = 'STOPBEFOREMARK = <LogMark>'

  • LiteSpeed – Backup Scripts

    full or complete database backup

    execute master.dbo.xp_backup_database
  @database = '<database name>',
  @filename = '<path>\<backup file name>.bak',
  @init = 1,
  @compressionlevel = 4

    differential backup

     
 execute master.dbo.xp_backup_database
  @database = '<database name>',
  @filename = '<path>\<backup file name>.bak',
  @init = 1,
  @compressionlevel = 4,
  @with =  differential

    transaction log backup

     
 execute master.dbo.xp_backup_log
  @database = '<database name>',
  @filename = '<path>\<backup file name>.trn',
  @init = 1,
  @compressionlevel = 4

    filegroup backup

       
 execute master.dbo.xp_backup_database 
  @database = '<database name>',
  @filename = '<path>\<backup file name>.bck',
  @init = 1,
  @compressionlevel = 4,
  @filegroup = 'filegroupname'

  • Table Hints – NoLock vs ReadPast

    When any data in a database is read or modified, the database engine uses special type of mechanism, called locks, to maintain integrity in the database. Locks will be used to make sure the transaction consistency.

    NoLock Table Hint
    – Will allow you to read the uncommited data
    – Only used with SELECT statement
    – Blocking will not occur
    – Will reduce the concurrency and improve the performance at some extent
    – Risk of doing Phantom reads

    Let’s create table for the NOLOCK and READPAST hint demo

    create table tranDemo
(
			id int identity(1,1),
			name varchar(10)
)

insert into tranDemo values ('Jugal')
insert into tranDemo values ('Nehal')

    –Now let’s update the values by specifying the explicit transaction and don’t commit/rollback the transaction

    begin transaction
   update tranDemo
   set name = 'DJ'
   where name = 'Jugal'

    Now open new query window and execute the below query and you will notice query will not return data and will continue running as it is blocked

    select * from trandemo

    Now open new query window and execute the below query to check the blocking, you can see the blocking SPID in the result set

    sp_who2 active

    Now open new query window and execute the below query using NOLOCK hint and it will return data, yet transaction is not committed but it will return the updated value.

    select * from trandemo(NOLOCK)

    READPast Table Hint: Less commonly used table hint than NOLOCK. This hint specifies that the database engine not consider any locked rows or data pages when returning results.
    – Will only read the commited rows which are not locked
    – Blocking will not occur
    – Only used with SELECT statement
    – Will reduce the concurrency and improve the performance at some extent
    – Result set returned by this hint is not perfect as it will not retun the locked rows or pages, so you can not make any decision based on data

    Now open new query window and run the below query it will return only one record (“Nehal”) which is not locked or modified

    select * from trandemo(ReadPast)