SQL Server: Script to get backup of all the user databases on server

by Ashish Upadhyay

Below script is going to be very useful if you need to take full backup of all the user databases on the running on the SQL server.

USE master

GO

DECLARE @DatabaseName VARCHAR(50) -- database name 

              ,@Path VARCHAR(256) -- path for backup files 

              ,@FileName VARCHAR(256) -- filename 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 = ''

       SET @Path = 'Please provide backup location' 

       SELECT @FileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE DB_Cursor CURSOR FOR 

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

         

           SET @FileName = @Path + @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

              BACKUP DATABASE @DatabaseName TO  DISK = @FileName WITH NOFORMAT, NOINIT,  NAME = @BKPName, SKIP, NOREWIND, NOUNLOAD,  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

              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