Wednesday, September 18, 2013

Script to backup all user databases on server

Sometime as a DBA you need to backup all your databases on a particular server and you may have more than hundred databases on that server. In the SQL environments that I manage have hundreds of databases on each servers, recently I was suppose to migrate few servers in or new SQL server farms and wanted to run a pilot migration without any downtime. So in order to handle this situation easily I develop this script that help me to backup all the databases in a particular location, so that U can move those files easily. I have developed another script that will generate script restore these databases on new SQL Server. I am will be sharing that script in next post.

This script takes backup takes backup of all the user databases on the server with compression. You can a specific backup location, if not this script will fetch your server's default backup location and place the backup files there. Backup file name will be prefix with the current date.  Microsoft has started supporting backup compression on the standard version of SQL server from SQL Server 2008 R2 onward. This was really a nightmare for the DBA until SQL server 2008, and SQL world had lot of dependencies on third party backup tools. I my environments I  have stopped using third party backup tools.

If you want to use this script on SQL Server 2008, please remove COMPRESSION from the backup database script. Because compression is not supported in SQL Server 2008.

Enjoy the script!


USE master
GO

--Script to backup all user databases on the server. 

DECLARE @DatabaseName VARCHAR(256) -- database name 
            ,@BackupDirectory VARCHAR(256) -- path for backup files 
            ,@FileName VARCHAR(256) -- file name for backup 
            ,@FileDate VARCHAR(20) -- used for file name
            ,@BKPName VARCHAR(256)
            ,@backupSetId int
            ,@StrError VARCHAR(250)


      SET @DatabaseName = ''
      SET @FileName = ''
      SET @FileDate = ''
      SET @BKPName = ''

      --You need to specify backup location here (without tailing back slash), leave blank if you want backup your database on server default backup directory
      SET @BackupDirectory = '' 

      IF @BackupDirectory = ''
            EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @BackupDirectory output
            --SET @BackupDirectory

      --You chnage date format if you want.
      SELECT @FileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE DB_Cursor CURSOR FOR
--this query for fetch all the databases ecept 'master' , 'model', 'msdb' & 'tempdb', you may add additional condition to exclude some other databases for example ReportServer (SSRS) databases
SELECT name FROM master.dbo.sysdatabases where dbid > 4

OPEN DB_Cursor  
FETCH NEXT FROM DB_Cursor INTO @DatabaseName  


WHILE @@FETCH_STATUS = 0  
BEGIN
   
   IF EXISTS (SELECT 1 FROM master.dbo.sysdatabases WHERE name = @DatabaseName)
      BEGIN
        
         --building complete path for backup, you can change file extension if you want.
          SET @FileName = @BackupDirectory + '\' + @DatabaseName + '_' + @FileDate + '.bak' 
            SET @BKPName = @DatabaseName + '-Full Database Backup'
            SET @StrError = 'Verify failed. Backup information for database' + @DatabaseName + ' not found.'
           
       
               
          BACKUP DATABASE @DatabaseName TO DISK = @FileName

            --this script will take backup with compression.
            --Note:Microsoft is now supporting backup compression for standard edition of SQL Server from SQL Server 2008 R2 onward.
            BACKUP DATABASE @DatabaseName TO  DISK = @FileName WITH NOFORMAT, NOINIT,  NAME = @BKPName, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
           
           
            select @backupSetId = position from msdb..backupset where database_name= @DatabaseName and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name= @DatabaseName)
            if @backupSetId is null begin raiserror(@StrError, 16, 1) end
            --Backup set will be verified with this script.
            RESTORE VERIFYONLY FROM  DISK = @FileName WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
           
         
          
    END
   FETCH NEXT FROM DB_Cursor INTO @DatabaseName  
END  

CLOSE DB_Cursor  
DEALLOCATE DB_Cursor
GO

No comments:

Post a Comment