3

I want to copy a SQL Server 2008 database to SQL Server 2012 with a different database name.

  • Took a backup of 'production' and copied it to the SQL Server 2012 machine.
  • Created a new SQL Server 2012 database with a 'test' name.
  • Tried to restore the .bak file but SQL Server says 'test' is busy

What is the correct way top do this?

Basically getting a snapshot of a production 2008 database into a test 2012 environment.

Thanks

Bob

5

Don't create the database on server B before restoring. Just restore it. It doesn't have to exist first in order to do that. This should work whether the database already exists or not (so perhaps you should consider using a better name than 'test'), just open a new query window:

USE master;
GO
RESTORE DATABASE test
  FROM DISK = 'E:\backup path\backup.bak'
  WITH REPLACE, RECOVERY,
    MOVE 'data_file_name' TO 'D:\data file path\test.mdf',
    MOVE 'log_file_name'  TO 'L:\log file path\test.ldf';

But I have no idea where you are getting a message about "busy" - are you restoring using the UI? Stop doing that - the UI is notoriously bad for assisting with tasks like backup and restore. And make sure you don't have any query windows etc. with their context set to the existing test database. To kick people out so you can restore over it, you can use:

USE master;
GO
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

To get the file names to place in the MOVE arguments, run this:

RESTORE FILELISTONLY FROM DISK = 'E:\backup path\backup.bak';
  • Getting Error Msg 3234, Level 16, State 2, Line 1 Logical file 'D:\Blah.mdf' is not part of database 'WgtnTest1'. Use RESTORE FILELISTONLY to list the logical file names. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. – Bob Clegg Jun 12 '14 at 19:27
  • 1
    @Bob data_file_name in my code above is supposed to be replaced with the logical file name, not the physical file name. Show the results of RESTORE FILELISTONLY. – Aaron Bertrand Jun 12 '14 at 19:32
  • Hi Aaron, that appears to be working (Big database, it will be a while.) How they can take a working process in the 2008 UI and reduce it to tedious scripting in 2012 is beyond me. I guess I should be grateful for anything at all. RE. Paths. All are good. I just shortened here for brevity. Thanks for your help. – Bob Clegg Jun 12 '14 at 19:43
1

To do that in SQL Server management Studio:

  1. Connect to the SQL Server 2012 instance
  2. Right-click the Databases node and select Restore Database

enter image description here

3.Select Device and navigate to the .bak file

SQL Server will in the Database field automatically insert the name of the original database, read from the restored backup

enter image description here

4.You have to manually type the new name here. There's a drop-down menu, but it shows only existing databases

enter image description here

5.Click OK

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.