Create a Cursor to take Backup and Restore of all SQL Server databases

------ Here is an example of CURSOR to take Backup of all SQL Server databases where backups are issued in a serial manner ------

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 = 'D:\BackUp\'  

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 = @path + @name + '_' + @fileDate + '.BAK'  

       BACKUP DATABASE @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name   

END   

CLOSE db_cursor   

DEALLOCATE db_cursor


----------- Create a CURSOR to Restore  SQL Server Databases ------------


DECLARE @DBName varchar(255)
DECLARE @DATABASES_Fetch int
DECLARE DATABASES_CURSOR CURSOR FOR
Select DATABASE_NAME = name From sys.sysdatabases
where name in ('Testing','amantrn') order by 1
OPEN DATABASES_CURSOR
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
exec ('RESTORE DATABASE ' + @DBName + ' FROM DISK =''D:\BackUp\' + @DBName +'.bak'' WITH REPLACE')
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END
CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR