SQL 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.
