June 5, 2009 at 3:48 am
Hello everyone,
I am having trouble with the following my backup maintenance plans for the transaction files, it does not happen on all the DB's, just some. I have configured the jobs are owned by the SA so I am unsure as to what might be causing this, I've had a look around on the web but cant seem to find anything that actually matches my situation.
Executing the query "declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'DATABASE_NAME' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DATABASE_NAME' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DATABASE_NAME'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'D:\\MSSQL2K\\MSSQL1\\MSSQL\\BACKUP\\DATABASE_NAME\\DATABASE_NAME_backup_200906050600.trn' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
" failed with the following error: "Cannot open backup device 'D:\\MSSQL2K\\MSSQL1\\MSSQL\\BACKUP\\DATABASE_NAME\\DATABASE_NAME_backup_200906050600.trn'. Operating system error 2(error not found).
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.
I am using SQL 2005 SP2 and the jobs are running under the same account as the SQL services, the databases are in Full Rec mode. I am trying to find some sort of pattern but so far I've not come across anything.
Any help or suggestions are most appreciated.
Kind Regards,
D.
June 5, 2009 at 4:28 am
May be permission issue. Check if SQL Server startup account have all the permission to write on to the backup folder. OPerating system error 2 :- The system cannot find the file specified.
Because backup did not take place hence RESTORE VERIFY fails with the error.
Check this link this might help:
"HowTo: Backup to UNC name using Database Maintenance Wizard" -
http://support.microsoft.com/kb/555128
http://support.microsoft.com/kb/890352
"More Green More Oxygen !! Plant a tree today"
June 5, 2009 at 4:53 am
Hello Minaz,
Firstly, thank you for getting back, I should have pointed out that I am backing up to a local disk and not a network location. I had a look at the permissions and the account has all the standard permissions and write permission on the folders, but some of the databases are in fact backing up successfully which is what makes this so confusing.
Regards,
D.
June 5, 2009 at 5:06 am
First thing I would check is if the databases with no t-log backup are in SIMPLE RECOVERY.
[font="Verdana"]Markus Bohse[/font]
June 5, 2009 at 5:21 am
Hello,
Thanks for joining in, I have configured all of the databases to be in Full Recovery mode already so I know its not that.
Regards,
D
June 5, 2009 at 5:43 am
The errors that you've posted are because the RESTORE VERIFY ONLY can't find the file to verify. (Operating system error 2 is file not found)
What's the error that the backup gives?
Are you sure that nothing's truncating the transaction log between the full backup running and the log backups failing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply