Automatically restore SQL Server database from file from another server

Question

We have a daily task to overwrite a number of development databases using backups of the associated production databases. The backups are produced by maintenance plans on the production server then transferred to the dev server by FTP. Each day we run a SQL statement similar to this to overwrite each database:

RESTORE DATABASE [Database1] 
FROM DISK = N'D:\path\to\Database1_backup_2015_02_05_190004_7401803.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO

Each time we run this we have to replace the file name with the correct most recent file. I would like to automate this somehow to minimise the chance of operator error. The problem is that we can't control the name of the .bak file (although the format is consistent - database name, date, time and whatever that seven digit number is), and the folder will usually contain several days worth of backups.

• LowlyDBA - John M: I think the simplest way would be to manually script your backup job to only use YYYY-MM-DD (maybe HH) in the naming convention so you'd know the backup names for each day. Unless you're sold on Ola's scripts, in which case this won't be viable.
• toryan: @JohnM that would be ideal, unfortunately we don't manage that system and in any case the maintenance plan that does the backup also does a whole bunch of other important stuff, so separating the backup job from it is not desirable :(
• swasheck: unless the the "whole bunch of other important stuff" is directly backup-related, i'd say that it's extremely desirable to separate it.
• swasheck: also, you mention ftp ... how much access do you have to the source? can you get a linked server?
• swasheck: @JohnM though i'd certainly advocate against maintenance plans altogether

Answer 1

Since all the details on backups are maintained in the msdb database, you should just extract the backup file name from the source server.

You could create a linked server from your Dev Server to access the Production Server's msdb database. Or you can use OPENQUERY to query the same data. (OPENQUERY may be faster since the query is actually being run on the Production Server.)

For example:

SELECT * from OPENQUERY([LinkToPRD], 
 'EXEC database.dbo.ExecDailyRestore');

This shows running a stored procedure with no parameters, which might suit your daily restore plan.

If you search for "auto generate sql server database restore scripts" you will find many scripts. An example from Paul Brewer is sp_RestoreGene that you might use as is or as a basis to create your own ExecDailyRestore stored procedure.

https://paulbrewer.wordpress.com/sp_restoregene/

Here are the parameters supported by sp_restoregene:

 @Database SYSNAME = NULL,
 @TargetDatabase SYSNAME = NULL,
 @WithMoveDataFiles VARCHAR(2000) = NULL,
 @WithMoveLogFile VARCHAR(2000) = NULL,
 @FromFileFullUNC VARCHAR(2000) = NULL,
 @FromFileDiffUNC VARCHAR(2000) = NULL,
 @FromFileLogUNC VARCHAR(2000) = NULL,
 @StopAt DATETIME = NULL,
 @StandbyMode BIT = 0,
 @IncludeSystemDBs BIT = 0,
 @WithRecovery BIT = 0,
 @WithCHECKDB BIT = 0,
 @WithReplace BIT = 0,
 @UseDefaultDatabaseBackupPath BIT = 0,
 @Log_Reference VARCHAR (250) = NULL,
 @LogShippingVariableDeclare BIT = 1,
 @LogShippingStartTime DATETIME = NULL,
 @LogShippingLastLSN VARCHAR(25) = NULL

And here is a sample script:

RESTORE DATABASE db_workspace 
FROM DISK = 'X:\Backups\Temp\db_workspace.bak' WITH REPLACE, 
FILE = 1,CHECKSUM,NORECOVERY, STATS=10
, MOVE 'db_workspace' TO 'x:\data\db_workspace.mdf'
, MOVE 'db_workspace_log' TO 'x:\data\db_workspace_log.ldf'
, MOVE 'db_workspace_FG2' TO 'x:\data\db_workspace_FG2.ndf'
, MOVE 'db_workspace_FG1' TO 'x:\data\db_workspace_FG1.ndf'

• JonBrave: @RLF (I have just joined as an existing "stackoverflow" user, but my reputation here does not yet allow me to place a comment against your answer above, so I hope it's alright if I have to do this as a separate post.) I agree with the principle of your answer above: SELECT * from OPENQUERY([LinkToPRD], 'SELECT top 1 * FROM msdb.dbo.backupmediafamily'); However, as an answer for people to use, I am surprised you use SELECT TOP but with no ORDER BY clause. SQL BOL states [SELECT] If the query has no ORDER BY clause, the order of the rows is arbitrary. I guess you assume it will actually al
• RLF: @ypercube -Thanks, to JonBrave (welcome!) I changed the OPENQUERY sample to show executing a remote stored procedure.
• RLF: @JonBrave - Thanks for the comment and welcome to StackExchange. I have updated the answer to show executing a stored procedure. Which is what I went on to describe in the answer. (No I did not assume anything about the result set, it was just to show OPENQUERY running a remote query. But the answer is better from your comment.)
• ypercubeᵀᴹ: I suggest you add an example from the linked article. Sqlservercentral requires registration to view and some readers may not be able (or don't want) to do that. It certainly hits my nerves.
• toryan: Thanks for the advice. I've managed to get the filename out of the msdb database on the live server and use that to create a simple SQL job for each database. I'm going to attempt to create a stored procedure on the dev server next in order to streamline the process, but that's a bit above my skill level at the moment.

Answer 2

The DBA's here are probably going to throw eggs and tomatoes at me but I'm going to throw this out there anyway.

You could use and integration services job to run this. First create a for Each file loop and set it to run for each file in that location. BackUp

Map the result to a variable. Variable

Then create and Execute SQL Task within the container. EST

Map Query

Once that's done you should be able to create a SQL job and schedule it appropriately. I'm not saying this is the best solution but it should work.

FreeHand

Answer 3

This should solve the problem for you exactly. Obviously if you are restoring to a database where the mdf/ldf are differently named you may need to adjust the final restore command slightly. This just works by listing out the backups in a directory and picking based on a pattern. I guess i could have made the pattern a variable too but you get the point.

 DECLARE @FileName varchar(255), @PathToBackup varchar(255), @RestoreFilePath varchar(1000)
 DECLARE @Files TABLE (subdirectory varchar(255), depth int, [file] int)
 SET NOCOUNT ON
 SET @PathToBackup = 'D:\path\to'
 -- insert into our memory table using dirtree and a single file level
 INSERT INTO @Files
 EXEC master.dbo.xp_DirTree @PathToBackup,1,1
 SELECT TOP 1 
 @FileName = [subdirectory]
 FROM 
 @Files
 WHERE
 -- get where it is a file
 [file] = 1
 AND 
 subdirectory LIKE 'Database1_backup%.bak'
 ORDER BY
 -- order descending so newest file will be first by naming convention
 subdirectory DESC
 IF LEFT(REVERSE(@PathToBackup), 1) != '\'
 BEGIN
 SET @PathToBackup = @PathToBackup + '\'
 END
 SET @RestoreFilePath = @PathToBackup + @FileName
 SELECT @RestoreFilePath
 RESTORE DATABASE [Database1] 
 FROM DISK = @RestoreFilePath
 WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

• toryan: This wasn't how I chose to solve this particular problem, but your answer did help me with another thing I was working on, so thanks for that :)