September 21, 2009 at 10:10 am
Hello, I hope someone can help me.
My normal full backups are working but my Transaction Log backups are failing, at first I thought it was because there were other backup jobs running along side my own, as that is normally the cause, so I stopped these other backup jobs.
I am still getting the problem though, I would like to know that if there are other jobs such as reindexing, database shrinking etc still running, will these (or others, if so what other jobs can cause this) still cause the same issue? My error is as follows...
select @backupSetId = position from msdb..backupset where database_name=N''DBNAME'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''DBNAME'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''DBNAME'''' not found.'', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N''Path to backup file.trn'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
Any help or suggestions are, as always, most appreciated. I'm running SQL 2005 9.0, thanks.
Kind Regards,
D.
September 21, 2009 at 12:36 pm
I'm sorry, I'm reading through the code there and I'm not sure what your error message is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 22, 2009 at 1:22 am
Yep agree with that, can you post the actual error you are receiving. already looked at the code you posted.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 22, 2009 at 5:03 am
Please check your DB recovery model. In Simple recovery model is not allowed.
September 22, 2009 at 7:12 am
Hello
Thanks for getting back, all of my DB's are in full recovery mode. Here is the log that will hopefully help you to help me. Hope its right.
Failed:(-1073548784) Executing the query "declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'DBNAME' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DBNAME' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DBNAME'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'F:\\PATH TO FILE\FILE.trn' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
" failed with the following error: "Cannot open backup device 'F:\\PATH TO FILE\FILENAME.trn'. Operating system error 2(The system cannot find the file specified.).
VERIFY DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Thank you for help,
Regards,
D.
September 22, 2009 at 7:16 am
Duran (9/22/2009)
HelloThanks for getting back, all of my DB's are in full recovery mode. Here is the log that will hopefully help you to help me. Hope its right.
Failed:(-1073548784) Executing the query "declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'DBNAME' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DBNAME' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DBNAME'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'F:\\PATH TO FILE\FILE.trn' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
" failed with the following error: "Cannot open backup device 'F:\\PATH TO FILE\FILENAME.trn'. Operating system error 2(The system cannot find the file specified.).
VERIFY DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Thank you for help,
Regards,
D.
Ok not sure if that is down to your formatting or not. change
'F:\\PATH TO FILE\FILENAME.trn'
to
'F:\PATH TO FILE\FILENAME.trn'
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 22, 2009 at 8:13 am
Either the path where backup is placed doesn't exist or context under which SQL Server is running doesn't have access to it.
Or, it's the issue that SilverFox spotted.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 22, 2009 at 8:33 am
If you see "operating system error" in your log, it means that a Windows error is bubbling up. In this case, it's the same error you might get in Word if you didn't have rights to the path, or had given an incorrect path.
September 24, 2009 at 6:29 am
Silverfox,
I can see that there is indeed to a double \\ in the file path for some reason, I cant see how this can happen as I am using the wizard to create the jobs, but this does seem to be a problem. The file path in the properties has been checked, so how can I remove this extra \ that only seems to appear in the logs? Where could I change it?
The folder permissions seem ok, and the actual full backups always complete without problem.
Thank you for your help everyone.
Regards,
D.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply