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.