I routinely take a backup copy from a customer database, and restore it to my development machine. Since the customer server uses the D: drive, and my server uses C:, restoring from EM takes extra steps to relocate the data files to the new location. The following scripts were written to make this process faster & easier. Actually, two databases are involved so a "solution" was required. . .


CREATE PROC dbo.procBackupTwoDBs
AS

-- Create a logical backup device for the full backup.
EXEC sp_dropdevice 'par_pos_1'
EXEC sp_dropdevice 'LampsPAR_1'

EXEC sp_addumpdevice 'disk', 'par_pos_1', 'd:\sqldata\MSSQL\data\par_pos.bak'
EXEC sp_addumpdevice 'disk', 'LampsPAR_1', 'd:\sqldata\MSSQL\data\LampsPAR.bak'

-- Back up the full database.
BACKUP DATABASE par_pos TO par_pos_1 WITH INIT
BACKUP DATABASE LampsPAR TO LampsPAR_1 WITH INIT

. . . which goes into the master on the customer server;

CREATE PROC dbo.procRestoreTwoDBs
AS

-- Create a logical backup device for the full backup.
EXEC sp_dropdevice 'par_pos_1'
EXEC sp_dropdevice 'LampsPAR_1'

EXEC sp_addumpdevice 'disk', 'par_pos_1', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\par_pos.bak'
EXEC sp_addumpdevice 'disk', 'LampsPAR_1', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\LampsPAR.bak'

-- Restore the databases
RESTORE DATABASE
par_pos
FROM
par_pos_1
WITH RECOVERY,
MOVE
'QSDB_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\PAR_POS_Data.MDF'

RESTORE DATABASE
LampsPAR
FROM
LampsPAR_1
WITH RECOVERY,
MOVE
'11-10-2004_Par_Data_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\LampsPAR_Data.MDF'

. . . which goes into the master on my development machine. To use these, I just open a Query Analyzer window and run the script out of the master.