Category: Backup & Recovery

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'

October 17, 2013

Script to get the Last Backup Date of database full and t-log backup

To get the max backup date of the database or to check whether database is backed up or not. You can execute below script to check the last backup date.

SELECT   d.name,
         MAX(b.backup_finish_date) AS backupfinishdate
FROM     master.sys.sysdatabases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'D'
GROUP BY d.name
ORDER BY backup_finish_date DESC


SELECT   d.name,
         MAX(b.backup_finish_date) AS backupfinishdate
FROM     master.sys.sysdatabases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'L'
GROUP BY d.name
ORDER BY backup_finish_date DESC

February 25, 2013

Script to get the database backup history

For the point time recovery or in case of failure it is essential that you should have the backup history of the database. You can execute the below query against the database to get the backup history.

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Database'  
       WHEN 'L' THEN 'Log'  
   END AS backup_type,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE 
(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= '2013-01-08 00:04:49.000')  and (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) <= '2013-01-08 23:59:59')  
and  msdb.dbo.backupset.database_name = 'Mention the database name'
ORDER BY  
   msdb.dbo.backupset.backup_finish_date desc

February 25, 2013

Important Trace Flags for DBA
A trace flag is used to set specific server characteristics or to switch off a particular behavior of the SQL Server.

You can enable the trace flag from Startup Parameters, Session and Global Session level.
```
DBCC TraceON(Flag Number) – at session level
DBCC TraceON(Flag Number, -1) -- Global
```
Trace Flag 610 : Minimally logged DML operations into Indexed tables, transaction log and allow bulk loading
Trace Flag 4199 : enable query optimizer fixes
Trace Flag 1222 : writes the deadlock information in XML format into error log
Trace flag 835 : Enable the Lock Pages in memory for standard edition
Trace flag 1118 : Directs SQL Server to allocate full extents to each tempDB objects
Trace flag 1204 : Write information about the deadlock in text format
Trace flag 1211 : Disable the lock escalation based on the memory pressure
Trace flag 3226 : Prevents successful backup operation being logged
Trace flag 3014 : Returns more information to error log about backup operation
Trace flag 3502 : Write information about the checkpoint in the error log
Trace flag 3505 : Disables automatic checkpoint
Trace flag 3004 : Returns more information about the instant file initialization
Trace flag 1806 : Disables the instant file initialization
February 5, 2013

T-SQL Script to identify the data, log and backup drive

You can use the below script to identify the data, log and backup file drive.

select 
@@SERVERNAME as svrName,
drivename, 
drivedescription
from
(
select distinct  SUBSTRING(filename,1,3) as drivename, 'data Drive' as drivedescription from master..sysaltfiles where filename like '%.mdf'
union
select distinct  SUBSTRING(filename,1,3) as drivename, 'Log Drive' as drivedescription from master..sysaltfiles where filename like '%.ldf'
union 
select distinct  SUBSTRING(physical_device_name,1,3) as drivename, 'Backup Drive' as drivedescription from msdb.dbo.backupmediafamily where physical_device_name like '%.bak'
) tab1

October 5, 2012