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.