Automatic backup and restore using SQL Server 2014

Hi Everyone,

I have 2 servers running SQL Server 2014 which are in different location. Server 1 does not see Server 2 and vice versa. I have a shared drive where both servers can access. I have created a Backup job to create the backup on the shared drive which is working fine. The issue I'm having is with the database restore.

I'm seeing the following in the SQL log:

Message
Error: 18204, Severity: 16, State: 1.
Message
BackupDiskFile::OpenMedia: Backup device 'Z:\BackUp\SalesDB.bak' failed to open. Operating system error 32(The process cannot access the file because it is being used by another process.).

Any help and solution is greatly appreciated.

Salim

At its simplest, it sounds like you're trying to do the restore before the backup is complete.

If that is the case then what we do is to have a different RESTORE folder on the shared drive. We make the backup to a BACKUP folder and then have a MOVE command that moved the file(s) from BACKUP to RESTORE folders. The files won't MOVE whilst they are still being written to ... even better if you can make the MOVE an additional step in your Backup job.

Might be easier than all of that. I believe that things like checking the file header will fail if the file is currently open. If it fails, don't try to restore. If it doesn't, then start the restore.

Heh... for that matter, if you try to restore and it returns the file-is-being-used message, then deal with it and tell the system to try the restore later.

I agree, if the errors are easy to Catch then do it that way. being an old fart 99.999% of my knowledge predates the time when such things were catachable at all, hence the prehistoric methods I suggest!

That works. Thank you so much.

Being an old fart myself, there's a lot of great things to be said for many of the old methods. Depending on what else may be required, your good method of moving the file can have some pretty extreme advantages.

Another common method is to use a different extension...during the backup you could use .tmp and once completed a simple rename of the file to .bak.

I know the OP stated the systems are in different locations but if you can get a connection to the other system another option is to use SQLCMD on the source system to kick off the restore job on the destination system.

For these types of operations I normally create a process on the destination system that pulls the backup files across using data in MSDB on the source system to identify the latest available backup and all tlog backups up to a point in time - that same process creates a script to restore the backup and every tlog backup and then executes that script on the destination system.

Be careful now... renaming the file doesn't do any renaming in the header nor in the contained file list. You might be rendering your backup useless for restores because the restore process checks all of that.

Nope, SQL Server does not care about the name of the file or if that file even has an extension.

The restore process reads the file header without regard to the file name and uses that information to determine what the data files for that database are...

I just tried it out and you're correct. I'm not sure why I thought it would make a difference. It doesn't care at all about the backup file itself. Just what the file list is and that's not touched during the rename. Thanks, Jeff.