Initial work on cursors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Initial work on cursors

Author

Topic

cjp
Yak Posting Veteran

69 Posts

Posted - 2009-01-06 : 10:22:33

Using an internet article to guide me, I wrote the following procedure to back up several databases in my testbed installation of SQL Server 2005 (SP2):

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) -- date used within file name

SET @path = 'E:\dbase_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

This worked fine but, having never done this before, I am struggling to accomplish two other objectives:

1 - Re-iterating the procedure appears to add another backup to the pre-existing backup file (file sizes keep incrementing). I would like to specify that, each time I run the loop, the backup replaces whatever is already there under the same name. I might well back up several times each day, so I would want to keep getdate().

2 - I always verify my backups but cannot see how to get something like the following verify script into the procedure:

RESTORE VERIFYONLY FROM DISK = N'E:\dbase_backup\lookups_20090106.BAK' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

Basically, I would want the cursor to run the backup, then verify it, then move to the next backup and so on.

Can anyone straighten me out here? Thanks.

Chris

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-06 : 11:06:49

For your first point, You could delete the files using dos cmds. You can add something like this in your code,

xp_cmdshell 'del '+@path + @name + '_'+'*'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources