Showing posts with label SQL Server Backup Scripts. Show all posts
Showing posts with label SQL Server Backup Scripts. Show all posts

Friday, 9 June 2017

SQL Server Backup Scripts

SQL Server Backup Scripts


SQL Server Backup Scripts are the ones which every DBA needs often. We will have a look on few Backup Scripts today which will be very useful for your day to day tasks-

1.1.     Please use the below script if you want to get  Backup Details including Backup path and  most recent backup date


DECLARE @type char(1)
SET @TYPE='D'                                  --FULL Backup 'D' , 'L' for Log Backup

Select
ServerName=@@servername ,
Edition=Serverproperty('Edition'),
ProductLevel=Serverproperty('ProductLevel'),
ServerVersion=Serverproperty('Productversion'),DatabaseName =sd.name,
DBStatus=Databasepropertyex(sd.name,'Status'),
RecoveryModel =Databasepropertyex(sd.name,'Recovery'),
IsBackupDone =Case when Bkp.database_name  is Null then 'No' Else 'Yes' End,
Bkp.physical_device_name,Bkp.backup_finish_date
from sysdatabases sd
Left Join
(
select
        x.database_name,
        z.physical_device_name, --CONVERT(char(20), x.backup_finish_date, 108) FinishTime,
        x.backup_finish_date from msdb.dbo.backupset x
        JOIN ( SELECT a.database_name, max(a.backup_finish_date) backup_finish_date
                                        FROM msdb.dbo.backupset a     WHERE type = @TYPE
                                        GROUP BY a.database_name ) y
        ON x.database_name = y.database_name
        AND x.backup_finish_date = y.backup_finish_date
        JOIN msdb.dbo.backupmediafamily z
        ON x.media_set_id = z.media_set_id
)BKP
ON   sd.name=Bkp.database_name
where sd.name <> 'tempdb'


1.2.      Below script can be used if you want to get Full Database Backup history

SELECT x.database_name, z.physical_device_name,
CONVERT(char(20), x.backup_finish_date, 108) FinishTime, x.backup_finish_date
        from msdb.dbo.backupset x
join ( SELECT a.database_name, max(a.backup_finish_date) backup_finish_date
        FROM msdb.dbo.backupset a
        WHERE type = 'D'
        GROUP BY a.database_name ) y on x.database_name = y.database_name
        and x.backup_finish_date = y.backup_finish_date
        JOIN msdb.dbo.backupmediafamily z ON x.media_set_id = z.media_set_id


1.3.      Log Backup history can be seen with the help of below Query

SELECT x.database_name, z.physical_device_name,
CONVERT(char(20), x.backup_finish_date, 108) FinishTime, x.backup_finish_date
        from msdb.dbo.backupset x
join ( SELECT a.database_name, max(a.backup_finish_date) backup_finish_date
        FROM msdb.dbo.backupset a
        WHERE type = 'L'
        GROUP BY a.database_name ) y on x.database_name = y.database_name
        and x.backup_finish_date = y.backup_finish_date
        JOIN msdb.dbo.backupmediafamily z ON x.media_set_id = z.media_set_id



1.4.      Backup all User Databases will be displayed if you execute below query 

DECLARE @DB VARCHAR(300), @Dir  VARCHAR(300), @Stmt VARCHAR(1000), @RM sql_variant, 
@Status INT, @DT DateTime, @BakTime varchar(20) 
 
SELECT @DB = '', @Dir = '\\NetworkPath' 
SET @DT = GETDATE() 
SET @BakTime = dbo.fn_StringFormatDate(@DT) 
 
IF OBJECT_ID('tempdb.dbo.#Databases') IS NOT NULL DROP TABLE #Databases 
 
IF OBJECT_ID('tempdb.dbo.BackUpStatus') IS NULL 
 CREATE TABLE tempdb.dbo.BackUpStatus 
  (Row_Id int IDENTITY NOT NULL PRIMARY KEY, 
  dbName varchar (50) NULL, 
  StepName varchar (100) NOT NULL, 
  Status int NULL, 
  ActionDateTime datetime NOT NULL) 
ELSE 
 DELETE FROM tempdb.dbo.BackUpStatus WITH (ROWLOCK) 
 WHERE ActionDateTime < DATEADD(d, -90, GETDATE()) 
 
 
--SELECT @Stmt = 'MKDIR ' + @Dir 
--EXEC @Status = master..xp_cmdshell @Stmt, NO_OUTPUT 
SELECT @Stmt = @Dir + '\' + @DB + '' 
--EXEC @Status = master.dbo.xp_create_subdir @Stmt 
 
INSERT tempdb.dbo.BackUpStatus (dbName, StepName, Status, ActionDateTime) 
SELECT 'Job Requirement', 'Check Backup Directory' + @Dir, @Status, GETDATE() 
 
SELECT Name as dbName, DATABASEPROPERTYEX(name, 'Recovery') RecoveryModel 
INTO #Databases 
FROM master..SysDatabases WITH (NOLOCK) 
WHERE DATABASEPROPERTYEX(name, 'Status') <> 'OFFLINE' AND  
   DATABASEPROPERTYEX(name, 'Status') <> 'LOADING' AND  
   DATABASEPROPERTYEX(name, 'Status') <> 'Restoring' AND 
   DATABASEPROPERTYEX(name, 'Recovery') = 'FULL' AND 
   Name NOT IN ('master', 'distribution', 'msdb', 'model') AND 
   (Name NOT LIKE 'a%' AND 
   Name NOT LIKE 'HMXMeta%' AND 
   Name NOT LIKE 'xxx%' AND  
   Name NOT LIKE 'g%' AND 
                  Name NOT LIKE '%temp%' AND  
   Name NOT LIKE '%tmp%' AND  
   Name NOT LIKE '%test%' AND  
   Name NOT LIKE '%train%'  AND   
   Name NOT LIKE '%Secure%' AND  
   Name NOT LIKE '%LiteSpeedLocal%' OR 
   Name LIKE '%WCMeta%') 
 --  AND NAME LIKE @DBname 
    
 
WHILE 1 = 1 BEGIN 
 SELECT TOP 1 @DB = ltrim(rtrim(dbName)), @RM = RecoveryModel 
 FROM #Databases 
 WHERE dbName > @DB 
 ORDER BY dbName 
 IF @@ROWCOUNT <> 1 OR @DB IS NULL BREAK 
 
 SET @Stmt = 'master..xp_cmdshell ''' + 'MKDIR ' + @Dir + '\' + @DB + ''', NO_OUTPUT' 
 EXEC(@Stmt) 
 
--SELECT @Stmt = @Dir + '\' + @DB + '' 
--EXEC @Status = master.dbo.xp_create_subdir @Stmt 
 
SET @Stmt = 'BACKUP DATABASE ['+ @DB + '] ' + 
   'TO DISK=''' + @Dir + '\' + @DB + '\' + @DB + @BakTime + '_FULL.BAK'',' + 
  '@INIT=0' 
 
 
PRINT @Stmt 
--EXEC (@Stmt) 
 SELECT @Status = @@ERROR 
 INSERT tempdb.dbo.BackUpStatus (dbName, StepName, Status, ActionDateTime) 
 SELECT @DB, 'Backup Log ', @Status, GETDATE() 
END 
 



1.5.      Missing Details is easier to find with the help of below Query

DECLARE @threshold INT

SET @threshold = 32 -- Hours

Select                              @@servername AS InstanceName
                        ,               d.name  AS Database_Name
                        ,               'Backup did not occurd within ' + CAST(@threshold as VARCHAR) + ' hours.' AS Message
                        ,               a.backup_start_date AS Last_Backup
                        ,               a.backup_size
                        ,               CASE         b.device_type
                                                                        when 2 then                             'Disk - Temporary'
                                                                        when 102 then         'Disk - Permanent'    
                                                                        when 5 then                             'Tape - Temporary'
                                                                        when 105 then         'Tape - Permanent'                   
                                                                        when 7 then                             'Network'
                                                                        else                                          'Unknown' END  AS Device_Type
                        ,               b.physical_device_name AS Physical_Path
from(
        select a.media_set_id, a.database_name, a.backup_start_date, a.backup_size,b.type
        from msdb.dbo.backupset a
        JOIN (
                        Select database_name, max(backup_finish_date) backup_finish_date, type
                        from msdb.dbo.backupset
                        where type='D'
                        group by database_name,type
              ) b
        on a.database_name = b.database_name
        and a.backup_finish_date = b.backup_finish_date
        ) a
JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
right join master..sysdatabases d on a.database_name=d.name
where d.name<>'tempdb'
and (DATEDIFF(hh, a.backup_start_date, getdate()) > @threshold OR a.backup_start_date IS NULL)
order by d.name



1.6.      Script to analyze space required for database if backups are failing due to space issue.

Select sum( cast((a.size * 8.00) / 1024 as numeric(12,2))) as DB_Size_in_MB
from sysaltfiles a
join sysdatabases b on a.dbid = b.dbid
where DATABASEPROPERTYEX(b.name, 'status') = 'ONLINE'
and b.name in (SELECT a.name         
FROM master..sysdatabases a         
LEFT JOIN (SELECT database_name, MAX(backup_finish_date) backup_finish_date         
           FROM msdb..backupset          
           WHERE Type = 'D'         
           GROUP BY database_name) b ON a.name = b.database_name         
WHERE (DATEDIFF(hh, b.backup_finish_date, GETDATE()) > 10 or b.database_name IS NULL) AND         
       (DATABASEPROPERTYEX(a.name, 'Status') = 'ONLINE' AND          
        a.Name NOT LIKE '%temp%' AND a.Name NOT LIKE '%tmp%' AND a.Name NOT LIKE '%test%' AND          
        a.Name NOT LIKE '%train%'  AND a.Name NOT LIKE '%LiteSpeedLocal%')
    
)
Group By b.name

Also if native backup is done, you may get the result using backupset table,but for compressed backup files result may differ.

SELECT a.name  ,b.backup_size     
FROM master..sysdatabases a         
LEFT JOIN (SELECT database_name,max(backup_size)/1024/1024 as backup_size, MAX(backup_finish_date) backup_finish_date         
           FROM msdb..backupset          
           WHERE Type = 'D'         
           GROUP BY database_name) b ON a.name = b.database_name         
WHERE (DATEDIFF(hh, b.backup_finish_date, GETDATE()) > 10 or b.database_name IS NULL) AND         
       (DATABASEPROPERTYEX(a.name, 'Status') = 'ONLINE' AND          
        --a.Name NOT IN ('master', 'distribution', 'msdb', 'model') AND         
        a.Name NOT LIKE '%temp%' AND a.Name NOT LIKE '%tmp%' AND a.Name NOT LIKE '%test%' AND          
        a.Name NOT LIKE '%train%'  AND a.Name NOT LIKE '%LiteSpeedLocal%')


So We have seen that the above SQL Server Backup Scripts are very useful in daily life of DBA.