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.