Get Backup Of All Databases Without Using Cursor

The stored procedure ‘sp_MSforeachdb' allows us to run sql command to all the DBs, without using cursor.

Here we try to run BACKUP command on all dbs using this procedure.

1.  EXEC sp_MSforeachdb

2.  'IF ''?'' NOT IN(''master'',''model'',''msdb'',''tempdb'')

3.    BEGIN

4.          USE ?

5.                DECLARE @Name VARCHAR(50) = db_name()

6.                DECLARE @Path VARCHAR(256) =  ''C:\Backup\'' +@Name+        

7.                CONVERT(VARCHAR(20),GETDATE(),112)+ ''.BAK''

8.                BACKUP DATABASE @Name TO DISK = @Path

9.          END'
 

Let's try to do same thing using CURSOR

1.  DECLARE @Name VARCHAR(50)  

2.  DECLARE @FileName VARCHAR(256)  

3.  DECLARE DbCursor CURSOR FOR 

4.  SELECT name

5.  FROM MASTER.dbo.sysdatabases

6.  WHERE name NOT IN ('master','model','msdb','tempdb') 

7.  OPEN DbCursor   

8.  FETCH NEXT FROM DbCursor INTO @Name  

10. WHILE @@FETCH_STATUS = 0  

11. BEGIN  

12.       SET @FileName = 'C:\Backup\' + @Name + '_' +

13.                       CONVERT(VARCHAR(20),GETDATE(),112) + '.BAK' 

14.       BACKUP DATABASE @Name TO DISK = @FileName 

15.       FETCH NEXT FROM DbCursor INTO @Name  

16. END  

17. CLOSE DbCursor  

18. DEALLOCATE DbCursor 


Now compare number of lines using cursor and without cursor.


Note:

Create directory ‘Backup' in c: drive before executing above query.

Line numbers are for reference only.

Ebook Download
View all
Learn
View all