SQL Server Cursor – Simple script to backup all SQL Server databases

·

·

This is an example cursor from tip Simple script to backup all SQL Server databases (mssqltips.com) where backups are issued in a serial manner.
With the use of T-SQL you can generate your backup commands and with the use of cursors you can cursor through all of your databases to back them up one by one.  This is a very straight forward process and you only need a handful of commands to do this.
The following script that will allow you to backup each database within your instance of SQL Server. You will need to change the @path to the appropriate backup directory.
File Naming Format DBname_YYYYDDMMHHMMSS.BAK

DECLARE @name VARCHAR(50) — Database name 

DECLARE @path VARCHAR(256) — Path for backup files 

DECLARE @fileName VARCHAR(256) — Filename for backup 

DECLARE @fileDate VARCHAR(20) — Used for file name

SET @path = ‘C:\TestBackup\’ 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),‘:’,)

DECLARE db_cursor CURSOR FOR 

SELECT name

FROM MASTER.dbo.sysdatabases

WHERE

name NOT IN (‘master’,‘model’,‘msdb’,‘tempdb’) 

OPEN

db_cursor  

FETCH

NEXT FROM db_cursor INTO @name

WHILE

@@FETCH_STATUS = 0  

BEGIN

SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’ 

BACKUP DATABASE @name TO DISK = @fileName 

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE

db_cursor  

DEALLOCATE

db_cursor

 

 



Leave a Reply

Your email address will not be published. Required fields are marked *