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 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply