backup and restore with move

There is a database on a SQL server.  Duh.

They need a copy of it to do some testing.  Initially we were going to backup and restore it.

Segue: we don’t backup.  We use DPM.  But all my DBA-Heros say “use the CLI, Luke!”  In fact, even using the GUI is thought declasse.

So i need to backup the file and restore it.  But of course i can’t restore it since it is already there.  OK, so i restore it with a different name.  I have to supply the name on restore anyway.  So

use master
go
backup database housingDirector_live
to disk = 'E:\housingDirector_live.bak'

Put the backup in the root of data (E:\) so i don’t have to go searching for it and it is shorter to type.  It is a file and not a device, so i have to supply the path as well as the name.

restore database housingDirector_test from disk = 'E:\housingDirector_live.bak'
Msg 1834, Level 16, State 1, Line 1
The file 'R:\MSSQL11.MSSQLSERVER\MSSQL\DATA\housingDirector_live.mdf' cannot be overwritten. It is being used by database 'housingDirector_live.'.
Msg 3156, Level 16, State 4, Line 1
File 'AdventureWorks2012_Data' cannot be restored to 'R:\MSSQL11.MSSQLSERVER\MSSQL\DATA\housingDirector..mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 1
The file 'R:\MSSQL11.MSSQLSERVER\MSSQL\DATA\housingDirector_live._log.ldf' cannot be overwritten. It is being used by database 'housingDirector_live..
Msg 3156, Level 16, State 4, Line 1
File 'AdventureWorks2012_Log' cannot be restored to 'R:\MSSQL11.MSSQLSERVER\MSSQL\DATA\housingDirector_live._log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Huh?  I changed the name…..  I hate it when something “terminates abnormally.”  That’s just not … RIGHT.

Using restore filelistonly to the backup file says that the backup knows about the correct location to restore the files even if the database file name is different.

restore filelistonly from disk = 'E:\housingDirector_live.bak'
LogicalName              PhysicalName                               Type FileGroupName Size
HousingDirector_Live     F:\MSSQL\Data\HousingDirector_Live.mdf     D    PRIMARY       2283470848
HousingDirector_Live_log E:\MSSQL\Data\HousingDirector_Live_log.ldf L    NULL          722468864

So use move to either move the files to another location or rename the files after they are extracted.

(we can get the same file paths from the original locations more simply with t-SQL and this query;)

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('HousingDirector_Live'),DB_ID('HousingDirector_Live_Log'))ORDER BY [type], DB_NAME([database_id]);

That should give us something like

database_name file_id file_type logical_name physical_name
HousingDirector_live 1 ROWS HousingDirector_Live C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\HousingDirector_Live.mdf
HousingDirector_live_log 2 LOG HousingDirector_Live_Log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\HousingDirector_Live_Log.ldf

So, next restore again with Move and the new name.

restore database housingDirector_Test 
from disk = 'E:\housingDirector_live.bak'
with move 'HousingDirector_Live' 
to 'F:\MSSQL\Data\HousingDirector_Test.mdf',
move 'HousingDirector_Live_log' 
to 'E:\MSSQL\Data\HousingDirector_Test_log.ldf'
Processed 278704 pages for database 'housingDirector_Test', file 'HousingDirector_Live' on file 1.
Processed 5 pages for database 'housingDirector_Test', file 'HousingDirector_Live_log' on file 1.
RESTORE DATABASE successfully processed 278709 pages in 35.840 seconds (60.753 MB/sec).

More on MOVE.

 

Advertisements