Backup of database X on SQL Server 2008 on server A - restore to SQL Server 2012 database Y on server B 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
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';