Example – Cursor in SQL Server

The following gives a SQL Server cursor example. It is useful to back-up all the databases from SQL Server.

Please click here for more information about what a cursor is, how to use one, and its drawbacks.

/* Variable Declaration */
DECLARE @DB VARCHAR(50)
DECLARE @FolderPath VARCHAR(100)
DECLARE @FileNamePath VARCHAR(100)

— Make sure the specified path exists. SQL Server won’t create any folder.
— It will throw “The system cannot find the path specified” error.
SET @FolderPath = ‘E:\TestBackup\’

/* Cursor declaration */
DECLARE backupCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)

/* Open the Cursor and fetch the data from the cursor to variable*/
OPEN backupCursor

FETCH NEXT FROM backupCursor INTO @DB

WHILE @@FETCH_STATUS = 0

BEGIN
SET @FileNamePath = @FolderPath + @DB + ‘-‘ + CONVERT(VARCHAR(8), GETDATE(),112) + ‘.bak’
BACKUP DATABASE @DB TO DISK = @FileNamePath

/* Fetch the next record from the cursor */
FETCH NEXT FROM backupCursor INTO @DB
END

/* Close and deallocate the cursor */
CLOSE backupCursor

DEALLOCATE backupCursor