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