Restore Database Backup From Another Server

On one of my recent projects, I needed to restore a backup from production to another development server, on a daily basis.  I used the logic below in a SQL Server Agent job on the dev server, to identify the remote backup, and restore it locally.  

Of course, the two servers have to be linked, but first I connect to the production server, and query the msdb database in order to retrieve the remote backup filename as @backupfile.  In this case, the format of the filename is like this:

     C:\MSSQL\Backup\DatabaseName_DB_20110122.bak

I wrote this into a BakFiles working table, so that I could manipulate it for the actual restore.  Because I was restoring from the remote server, I needed to update the filename to include the UNC path, like this:

    \\ProductionServerName\C$\MSSQL\Backup\DatabaseName_DB_20110122.bak

At that point, the local database is then set to SINGLE_USER in order to perform the restore, and set back to MULTI_USER afterward.  Pretty much like this:

  SET NOCOUNT ON;
  
  DECLARE @backupfile VARCHAR(100)
  SET @backupfile = (
   SELECT TOP (1) BUMF.physical_device_name
   FROM ProductionServerName.msdb.dbo.backupmediafamily AS BUMF 
   INNER JOIN ProductionServerName.msdb.dbo.backupmediaset AS BUMS   
    ON BUMF.media_set_id = BUMS.media_set_id 
   INNER JOIN ProductionServerName.msdb.dbo.backupfile AS BUF 
   INNER JOIN ProductionServerName.msdb.dbo.backupset AS BUS 
    ON BUF.backup_set_id = BUS.backup_set_id 
    ON BUMS.media_set_id = BUS.media_set_id
   WHERE (BUS.database_name = 'DatabaseName')
   AND (BUMF.physical_device_name LIKE  
    'C:\MSSQL\Backup\DatabaseName\DatabaseName_db_%') 
   ORDER BY BUS.backup_start_date DESC 
   )


  /* BAKFILES WORKING TABLE */
  TRUNCATE TABLE dbo.BakFiles
  INSERT dbo.BakFiles (bakfilename)
  SELECT (@backupfile)


    UPDATE dbo.BakFiles
  SET bakfilename =   
  REPLACE(bakfilename,'C:\','\\ProductionServerName\C$\')


   DECLARE @newfile VARCHAR(100)
  SELECT @newfile = [bakfilename] FROM dbo.BakFiles 


  /* SET DATABASE TO SINGLE_USER */
  ALTER DATABASE DatabaseName 
  SET SINGLE_USER WITH ROLLBACK IMMEDIATE


  /* RESTORE DATABASE */
  RESTORE DATABASE DatabaseName
  FROM DISK = @newfile
  WITH REPLACE, STATS = 5,
  MOVE 'DatabaseName_Data.mdf' TO   
   'D:\MSSQL10.MSSQLSERVER\MSSQL\Data\DatabaseName_Data.mdf',
  MOVE 'DatabaseName_Log.ldf' TO 
   'D:\MSSQL10.MSSQLSERVER\MSSQL\Log\DatabaseName_Log.ldf'
  GO


  /* SET BACK TO MULTI_USER */
  ALTER DATABASE DatabaseName
  SET MULTI_USER


  SET NOCOUNT OFF;
  

I also ran another step after this, to drop and recreate the database users, and then add them back to the appropriate role.  You'll need to replace 'ProductionServerName' and 'DatabaseName' to suit your needs, but that's pretty much it.  Providing your remote server is reachable, and the backup files are available, it should work just fine.