Click here to Skip to main content
15,907,677 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
SQL
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 = 'C:\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


http://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/[^]
Posted
Updated 12-Apr-12 1:03am
v2

1 solution

In many situations you need to iterate through objects in Sql-Server so you need to use Cursors. In your example, a few databases are queried and then their backup files is created in a generated path one by one.
In order to find advantages/disadvantages of Cursors, take a look at this post:

http://www.allinterview.com/showanswers/61552.html[^]

Cheers.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900