Backups not always running

Last Post 06 Nov 2012 05:55 AM by gunneyk. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Kingfish
New Member
New Member

--
05 Nov 2012 06:23 AM
I have a procedure that runs nightly backups. The procedure runs without error every day but not all databases are backed up. If I run the script in QA the script executes but not all databases are backed up. The select that is in the cursor returns all the database names. The network share has plenty of disk space. Can anyone offer any advice on how to resolve this issue? The server is SQL Server 2005 SP4. Below is the procedure.

Thx


DECLARE @dbname varchar(200)
DECLARE @dbid smallint
DECLARE @backupsql varchar(1000)

DECLARE db_cursor CURSOR FOR
SELECT name, database_id FROM sys.databases
where database_id > 4

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname, @dbid

WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@dbid > 4)
BEGIN
SELECT @backupsql = 'BACKUP DATABASE ' + @dbname + ' TO DISK = ' + CHAR(39) + '\\servername\sqlbackups$\subdir\' + @dbname + '.BAK' + CHAR(39) + ' WITH INIT'
EXEC(@backupsql)
END
FETCH NEXT FROM db_cursor INTO @dbname, @dbid
END

CLOSE db_cursor
DEALLOCATE db_cursor
RETURN
gunneyk
New Member
New Member

--
05 Nov 2012 09:45 AM
You should add a TRY - CATCH block to that code so that you can see if there are any errors. Most errors would stop the cursor loop as the code exists and then none of the dbs after that would get backed up. A couple more suggestions though:

1. No need to test for @dbid > 4 inside the cursor loop since you already filtered that out in the cursor definition.
2. Use the keyword STATIC when you define the cursor.
3. Add this to the WHERE clasue for the cursor: AND [state_desc] = 'ONLINE'
4. Add a datetime stamp to the backup file name so that you can keep a history of the backup files. Remember to delete ones you don't want.

However with that said why not just use the maintenance plan to issue the backups and delete the old files? You are not doing anything custom and at least the maintenance plan will provide some error checking.
Kingfish
New Member
New Member

--
06 Nov 2012 05:45 AM
Thank you. That fixed the issue. I know what 'STATIC' forces the cursor to do but why did that fix the issue?
gunneyk
New Member
New Member

--
06 Nov 2012 05:55 AM
I don't really know why it was failing and am a bit suprised that STATIC did the trick. However I recomend STATIC on most cursors since it only has to hit the actual table once and doesn't require locks once it has built the cursor no matter how many loops there are.


Acceptable Use Policy
---