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.