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