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