Sql-server – Automatic database restore fails as cannot find file

maintenance-planssql serverssis

A while back I asked for help automating a database restore using a backup file provided from another server: Automatically restore SQL Server database from file from another server. I'm pleased to say that I now have this working as a SQL Server Agent job.

We are now trying to trigger this job as part of a maintenance plan that runs on the other (dev) server. At the moment we have a maintenance plan that runs on the live server and performs the following steps, in sequence:

Back up the target databases
Call an external process to copy the created backup files
Clean up any backup older than 2 days (runs at the same time as 2)
Start the restore job on the other server

When I run the maintenance plan on live it reports success, however when we check the job history on the dev server it shows that it has failed saying that it could not find the file. Running the job again returns success, and a manual check indicates that the files have been copied correctly.

It would seem that the restore job is being started too soon but I can't figure out why. Is there any reason why the restore job is being triggered before the copy process has completed?

EDIT: I've checked the maintenance plan logs and the restore job step starts 1 second after the transfer step. Obviously the transfer takes longer than this, so I can at least see evidence of the problem.

Best Answer

Copy step of your job will just inform operating system to start copy process and will mark this step completed and this will initiate next step "Restore Database", but meanwhile operating system is still copying your file to destination.

Before restore, you must verify file existence first.

Declare @file_exists int
EXEC xp_fileexist 'c:\MyDatabase.bak', @file_exists out
IF @file_exists = 1
BEGIN
  RESTORE DATABASE ....................
END
ELSE
  RAISERROR ('Restore Job Step Failure!', 16, 1)

Related Solutions

Sql-server – Restore Database SQL Server 2012

Lets take your questions one by one :

Is it possible to do the following:Have a copy of the database on the same SQL Server Instance Yes it is possible. You just have to restore the database main-db.bak with move.

e.g. Provided you dont have secondary data files, below will work

-- find logical names

restore filelistonly from disk = 'path\main-db.bak'
go
restore database main_db_copy
from disk = 'path\main-db.bak'
with recovery, stats = 10,
-- below logical data and log file names will be found from the filelistonly mentioned above.
move 'logicalDataFileName' to 'PhysicalPath\main-db_data.mdf',
move 'logicalLogFileName' to 'PhysicalPath\main-db_log.ldf'

Create a new empty database

No need to first create an empty database, as the restore with move will create it.

Restore the database from main-db.bak file and not overrite anything else that may be using the same data MDF and LDF files?

Same as above. As restore with move will not over write unless you use restore with REPLACE explicitly to overwrite.

Or would it be easier to reproduce this database from a live running database copy so that it is not needed to restore if from backup?

Cannot understand what you mean by this ! If you want to create a new database or copy of the current database , you have to take a backup and then use restore database to restore it.

Is it mandatory to rename the LDF and MDF files when the database is restored, can I script to automate the majority of the work?

If you want to overwrite the existing one, then no. If not and you want to create a copy of existing one, then Yes - you have to rename the mdf and ldf files.

You can fully automate the restore process. Some examples can be found here and here.

Sql-server – How to specify a SQL Server maintenance plan subtask to run unconditionally on success or failure of preceding tasks

You need to change the appropriate upstream precedence constraint(s) in the flow to On Completion instead of On Success.

This is done one of two ways:

Right-clicking the constraint on the design surface (shown below) and selecting the appropriate type from the popup menu.
In the Precedence Constraint Editor dialog (which you had open), select the appropriate type from the Value dropdown list.

In earlier versions than 2012, the constraint arrow will turn blue; in 2012, the arrow gets a more explicit "Completion" caption.

enter image description here

Now having said that, if you want to keep all of this in one flow, I would actually recommend using SQL Server Data Tools to create the package, because what you really need is a Sequence Container to put all the maintenance stuff in (keeping the existing On Success constraint types), and then connect the container to the backups task with an On Completion precedence constraint.

Alternatively -- and this is what I would usually do -- separate the backup portion of the process into a different subplan, and schedule it after the maintenance portion is expected to complete.

Best Answer

Related Solutions

Sql-server – Restore Database SQL Server 2012

Sql-server – How to specify a SQL Server maintenance plan subtask to run unconditionally on success or failure of preceding tasks

Related Question