Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Special Announcement: SQL Server Performance Tuning Tips and Tricks Webinar
 

Check for full SQL Server database backups before creating other backups


By:   |   Read Comments (4)   |   Related Tips: More > Backup

Problem

For very large databases, we usually include either differential, file or filegroup backups together with the full database backup in our backup plan. In some cases, differential backups are used before a major change is implemented in the database. The backups can, then, be used to rollback those changes if and when necessary. The challenge with using these other types of backups is that they require a full backup as the starting point for the restore process. How do we make sure that we have a copy of the full database backups before we generate these other types of backups?

Solution

In a previous tip on Differential Database Backups for SQL Server, you have seen how differential backups work in combination with full database backups. However, you need to make sure that, if you are generating differential backups or any other type of backups other than the full database backup, the full database backup should be available. This is specifically helpful in cases where you would need to implement a major change in your database and would like to have a backup available for rollback purposes in case the need arise. I have seen organizations where they do not have the benefits of having database snapshots in SQL Server for rollback purposes. As this is an Enterprise Edition-only feature, they would opt to use both a full and differential backups for the purpose of rolling back changes.

The script below will create a stored procedure that is used to generate differential backups. You can also change it to any other type of backups but, for the purpose of this tip, I will be using a differential backup.

The stored procedure works by checking the backup history and searches for the latest full database backup and the corresponding file. If the latest full database backup does not exist or the database has never been backed up, the differential backup will abort. The stored procedure accepts a parameter that will contain the location of the differential backup with a default value of your pre-defined backup location.

CREATE PROCEDURE sp_generateDifferentialBackups  
   @strBackupPath NVARCHAR (25) ='D:\backups\' --variable for location of DIFFERENTIAL backups  
AS  
SET NOCOUNT ON   
-- Get the name of all user databases    
DECLARE @strTimeStamp NVARCHAR(12)   --variable for timestamp value   
DECLARE @strSQL NVARCHAR(100)        -- used for generating dynamic SQL statements   
DECLARE @databaseName NVARCHAR(128)  -- used as variable to store database names   
DECLARE dbCursor CURSOR FOR          -- used for cursor allocation   
   SELECT NAME  
   FROM MASTER.SYS.DATABASES  
   WHERE [database_id] > 0  
   AND NAME NOT IN ('MASTER','MODEL','MSDB','TEMPDB') --does not include the system databases   

OPEN dbCursor   
   
FETCH NEXT FROM dbCursor INTO @databaseName   
   
WHILE (@@FETCH_STATUS = 0)   
BEGIN   
   PRINT 'Checking for the latest FULL database backup for: [' + @databaseName +']'  
       
   DECLARE @strphysical_device_name NVARCHAR(100) -- variable for physical_device_name   
   DECLARE @cursor NVARCHAR(400)   

   -- Reads the MSDB database to check for the latest FULL database backup         
   SELECT @cursor=('DECLARE TabCursor CURSOR FAST_FORWARD GLOBAL FOR ' +   
   'SELECT TOP 1 physical_device_name   
   FROM msdb.dbo.backupset a INNER JOIN msdb.dbo.backupmediafamily b      
   ON a.media_set_id = b.media_set_id      
   WHERE database_name=''' + @databaseName + '''   
   AND type=''D''   
   ORDER BY backup_finish_date desc')   
   EXEC sp_executesql @cursor   
   OPEN TabCursor   
   
   FETCH NEXT FROM TabCursor INTO @strphysical_device_name   
   --Check if the database does not have any FULL backups at all  
   IF @@FETCH_STATUS <> 0  
   BEGIN  
      PRINT '*****WARNING: Database [' + @databaseName + '] does not have any FULL database backups at all. Generate the FULL database backup first. Aborting DIFFERENTIAL backup command.*****'  
   END  
   ELSE     
   --If the database has FULL backups,  
   WHILE @@FETCH_STATUS = 0   
   BEGIN   
      DECLARE @result INT   
      EXEC xp_fileexist @strphysical_device_name, @result output    
      IF (@result = 1)  [email protected] will return 1 if the specified file exists, if it doesn't, it will return 0   
      BEGIN   
         SET @strTimeStamp=CONVERT(CHAR(8), GETDATE(), 112)   
         SET @[email protected] + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')   
         EXEC ('BACKUP DATABASE ' + @databaseName + ' TO DISK=''' + @strBackupPath + @databaseName + '_DIFF_' + @strTimeStamp + '.BAK'' WITH INIT, DIFFERENTIAL, DESCRIPTION=''DIFFERENTIAL Backup for ' + @databasename + ' database''') -- change this value should you decide to change the backup type to something other than differential    
         PRINT '==========================================================================================='   
         PRINT 'DIFFERENTIAL Backup generated for database: [' + @databaseName  + ']'  
         PRINT 'Corresponding FULL database backup: ' + @strphysical_device_name   
         PRINT 'DIFFERENTIAL database backup: ' + @strBackupPath + @databaseName + '_DIFF_' + @strTimeStamp + '.BAK'   
      END   
      ELSE   
         PRINT  '*********WARNING: FULL database backup file is missing. Generate the FULL database backup first. Aborting DIFFERENTIAL backup command.*********'   
          
      FETCH NEXT FROM TabCursor INTO @strphysical_device_name   
   END   
   CLOSE TabCursor   
   DEALLOCATE TabCursor   
        
   PRINT '==========================================================================================='   
   PRINT '                                                                                           '   
   PRINT '                                                                                           '   
   PRINT '                                                                                           '   
    
   FETCH NEXT FROM dbCursor INTO @databaseName   
END   
   
CLOSE dbCursor   
DEALLOCATE dbCursor   

PRINT 'FINISHED'   
GO   

You can either pass a parameter that contains the backup location where you intend to store the differential backup or simply call the stored procedure as it is.

sp_generateDifferentialBackups 'D:\backups\'

Here sample output after creating and running the stored procedure.

full backup output report

Aside from using this stored procedure to generate ad-hoc differential backups, this can also be used as an alternative to your database maintenance plans. Instead of using the Backup Database Task, you can use an Execute TSQL Task and call this stored procedure. By doing so, you are practically sure that your differential backups will have their corresponding full database backups. You can customize this script to send alerts when the differential backup is aborted to let you know that you either have a missing full database backup or that your database has not been backed up yet.

Next Steps


Last Update:

First Published: 2009-11-10


next webcast button


next tip button



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, June 28, 2012 - 3:48:26 PM - Ludwig Guevara Back To Top

Hey

I want to suggest you this changes to the first cursor

SELECT NAME
FROM MASTER.SYS.DATABASES (NOLOCK)
WHERE --[database_id] > 0 -- Not Necesary

-- BD has to be Online, we don't want issues with database in recovery or mirror db
[state] = 0

-- Because you can only make a Differential backup, if your database is in FULL Recovery_model
AND [recovery_model] = 1 -- FULL

-- Does not include the system databases
-- THIS is "optional", some have DB 'MODEL' as FULL 
--AND NAME NOT IN ('MASTER','MODEL','MSDB','TEMPDB')

Thanks.


Thursday, June 28, 2012 - 2:10:01 PM - bass_player Back To Top

Unfortunately, there is no native object-level backup option from within SQL Server. You would need to use third-party tools like Quest's LiteSpeed for SQL Server to do this


Thursday, June 28, 2012 - 5:18:14 AM - Dinesh Vishe Back To Top

If i want atke 2-3 tables backup then what to do ?


Wednesday, February 23, 2011 - 4:29:21 PM - Ludwig Guevara Back To Top

Hi,

I read an article similar to your's about: check if the Database had his last backup on file, add the code to sp and then to a job that runs every day looking for any database in the server that miss a backup and if it miss a backup send an email

Here's the code:

SELECT
        s.name
        , b.backup_start_date
        , CASE WHEN ( b.backup_start_date > DATEADD( dd, -7, getdate() ) )
              THEN CASE b.type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Log' END +' Backup within ' +
          CASE WHEN ( b.backup_start_date > DATEADD( hh, -1, getdate() ) )
              THEN 'an hour' WHEN b.backup_start_date > DATEADD( dd, -1, getdate() )
              THEN 'a day' WHEN b.backup_start_date > DATEADD( dd, -7, getdate() )
              THEN 'a week' END ELSE '*****CHECK BACKUP!!!*****' END
    FROM master..sysdatabases    s
    LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name
        AND b.backup_start_date = ( SELECT MAX(backup_start_date) FROM msdb..backupset
                                                  WHERE database_name = b.database_name
                                                  AND type in ( 'D', 'I', 'L' ) ) -- FULL database backups only, not log backups

ORDER BY s.name

 

Thanks.


Learn more about SQL Server tools