restore with copy

SQL Backup creates a file which you can direct to a location and to which you can provide a name.

code from http://www.mssqltips.com/sqlservertip/3113/sql-server-database-restore-with-move-or-not-with-move/

USE [master]
GO
BACKUP DATABASE [AdventureWorks] TO DISK = 'c:\mssql\backup\yukon\AW2K5_Full.bak'
WITH CHECKSUM, COPY_ONLY, FORMAT, INIT, STATS = 10;

In this example, the database is changed to single-user mode and dropped. The mdf and ldf files remain in place. A backup is done AFTER the file is dropped. The contents of the .bak file can be displayed with

RESTORE FILELISTONLY FROM DISK = 'c:\mssql\backup\yukon\AW2K5_Full.bak' -- WITH FILE = 1
GO

which will return the logicalName, physicalName, type (D vs L), filegroup name, size, FileID.

Another approach is 

RESTORE VERIFYONLY FROM DISK = 'c:\mssql\backup\yukon\AW2K5_Full.bak'
GO

also review the current physical location of the mdf and ldf files with t-sql tosys.master_files

USE [master]
GO
SELECT
DB_NAME([database_id]) [database_name]
, [file_id]
, [type_desc] [file_type]
, [name] [logical_name]
, [physical_name]
FROM sys.[master_files]
WHERE [database_id] IN (DB_ID('AdventureWorks'))
ORDER BY [type], DB_NAME([database_id]);

If we restore now even to a new name, the data and log files in the .bak file are in the default locations and we will get rrros including the suggestion to use move.

The restore plan below changes the name from AdventureWorks to AdventureWorksCopy from the AdventureWorks full backup .bak file, and moved data and log from the default location to a new location.

USE [master]
GO
RESTORE DATABASE [AdventureWorksCopy] FROM DISK = 'c:\mssql\backup\yukon\AW2K5_Full.bak'
WITH CHECKSUM,
MOVE 'AdventureWorks_Data' TO 'c:\mssql\data\yukon\AdventureWorksCopy_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'c:\mssql\log\yukon\AdventureWorksCopy_Log.ldf',
RECOVERY, REPLACE, STATS = 10;
Advertisements