How To create One or more Database Backup in Loop using Sql server Cursor

Dhaval Patel
13y
6.8k
0
0

Blog

How To Get Database Backup using cursor and also only write down this code

Backup Database "database name" To Disk = "pathname"

 
DECLARE @name AS VARCHAR(50)
DECLARE @FileName AS VARCHAR(50)
DECLARE @filedate AS VARCHAR(50)

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

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 = 'E:\dhaval\' + @name + '_' + @filedate + '.Bak'
BACKUP DATABASE @name TO DISK = @FileName
END

And if you want to Restore it then write down this code

Set Single
Restore Database "Database Name" From Disk = "Path name"
Set Multiple

Set single and means single user database restore .then first of set single and database has restored then make multiple User.