April 7, 2008 at 8:00 am
SQL2005 Ent. Ed. sp1.
I have a proc that runs nightly which opens a cusor then backs up all the dbases on the server. However I have discovered it does not always backup all the dbases. Some days it will and some days it will not. The proc does not error it acts as if the list of dbases are not complete per the cursor.
I tested by putting the select statement from the cursor pre and post the proc. They both returned all dbases however the proc still only backed up some of them ????
I think I need to add the insensitive statement to the declare cursor.
Any input would be helpful.
Here is the cursor stataement
DECLARE dbbackup_cursor CURSOR FOR
SELECT mst.name, mst.database_id FROM SYS.DATABASES mst WITH (NOLOCK)
WHERE MST.name not in ('tempdb','northwind','pubs')
ORDER BY database_id
April 7, 2008 at 9:08 am
Shouldn't need to. I'd add some logging code to this proc. Make a table, insert into it at various places from the proc the value of variables and what's happening. Include a timestamp so that you can order by it.
April 7, 2008 at 9:20 pm
I'd also add [font="Courier New"]AND mst.state_desc = 'ONLINE'[/font] to the WHERE clause (or [font="Courier New"] AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'[/font]. The first way is SQL 2005 only).
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
April 8, 2008 at 12:22 am
As you said the select statement to declare the cursor works, the issue may not be with the cursor, rather with the back up statement. ie. it could be failing to backup ...not failing to try to backup
output the back up statements in the log from the procedure.
eg include in your proc:
print @backup_statement
exec (@backup_statement)
April 8, 2008 at 12:22 pm
I have a stored procedure that you can use if you like.
EXECUTE dbo.DatabaseBackup @databases = 'USER_DATABASES', @Directory = 'C:\Backup', @BackupType = 'FULL', @verify = 'Y', @CleanupTime = 24
Here it does a full backup of all user databases. The root backup directory is C:\Backup. The backups are verified. Backup files that are older than 24 hours are deleted on success.
http://ola.hallengren.com/sql-server-backup.html
Ola Hallengren
April 14, 2008 at 6:51 am
I have resovled the issue by declaring the cursor insensitive. Thanks every one for their input.
-- resolution
DECLARE dbbackup_cursor INSENSITIVE CURSOR FOR
SELECT mst.name, mst.database_id FROM SYS.DATABASES mst WITH (NOLOCK)
WHERE MST.name not in ('tempdb','northwind','pubs',)
and MST.name not like ('%snapshot%')
ORDER BY database_id
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply