Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CanadaDBA
583 Posts |
 Posted - 2009-05-05 : 13:04:02
|
During the night I have BAK file transfer to a Test machine and restore. The restore program fails when executes “RESTORE FILELISTONLY FROM DISK“ and provides the following error message:BackupDiskFile::OpenMedia: Backup device 'F:\BAKFILES\MyDatabaseBackup_db_200905042000.BAK' failed to open. Operating system error = 32(The process cannot access the file because it is being used by another process.).Which is correct because at that time the file transfer might not be completed and the file still being copied from prod into the Test machine. Using the following SELECT @Error = @@Error, @RowCount = @@RowCount and a loop I do the restore finally but I see the above errors in event viewer log.How can I validate a BAK file is correct before I use RESTORE command against the file? I want to make sure the file is not in use by any other program and then apply the RESTORE. Please advise. Canada DBA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-05-05 : 13:38:38
|
| Why don't you move the file transfer task to this test server by putting the file transfer task and restore task in a SQL job in separate steps? You wouldn't have to check anything as the restore would run only after the previous step, the file transfer, is complete.This is what I do in my environments.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2009-05-05 : 13:46:27
|
| I didn't think of that. I have a scheduled batch job that does this file copy using ROBOCOPY. Are you saying I should call the batch job from within the SQL job?Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
CanadaDBA
583 Posts |
Posted - 2009-05-05 : 15:20:55
|
| That was great help. Thanks Tara!I also read your weblog on "How to refresh a SQL Server database automatically". It was useful.. as usual :)Canada DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Validate a BAK file