Restore a database with a different name on the same server

Question

I need a copy of a live database called Carers on the same server for testing purposes. The test database name will be Carers_Test.

I took a backup of Carers using SSMS. And tried to restore it using the GUI. When I followed the steps, the only thing I changed was the name of the database.

But the restore fails with the error:

Exclusive access could not be obtained because the database is in use.

I do not want to do anything with the current live database, why is that matter that the database is in use? I have tried to restore the same backup on my local host and it restored successfully. When I try to restore it on the same server with the live database then I get this error.

Please someone help me...I'm going crazy!

Answer 1

I think it also depends on which version of Management Studio you're using. In 2016+ when you change the name of the Destination Database, SSMS changes the name of the Restore As files (see the screenshots). I seem to remember that in older versions of SSMS you had to manually change the names of the files, or else they would conflict with the existing files.

Also, be very careful of the Options "Overwrite the existing database" and "Take a tail-log backup before restore". Both of those should be Unchecked!

Lastly, if I were you, I would take a backup of the production database immediately before trying a restore of that production database.

Here I've changed the name of the new database:

Here the names of the new files are changed by SSMS. You might need to manually change these. In any case, check what SSMS is trying to do:

Here the two important Options are Unchecked:

Answer 2

It's really simple to solve. You are trying to override the original files you need to you use the move option like this:

restore database <Your-Database> from disk ='<backup-file-path>' 
with move 'mdf_file_name' to '<new-path>',
move 'log_file_name' to '<new-path>'

By the way to discover the file name from backup use this command:

restore filelistonly from disk ='<backup-file-path>' 

Answer 3

It has been suggested that the reason for your error is because of the database files (use the same files as your current/existing database). That is not the reason for your error. If that were the reson, you would have seen a different error message. Below is from trying to restore a database named TSQL into the name TSQLTest, using the same file names as the existing TSQL database:

The file 'C:\DemoDatabases\DbFiles\a\TSQL.mdf' cannot be overwritten. It is being used by database 'TSQL'.

SSMS is pretty good at changing the database files names for you when you type something for the name of the database to be restored. Old SSMS did this in a pretty stupid way, and then came one version which didn't change the names for you, and then in the next version came the "intelligent" file changing that we see today. I don't recall the version history, as for when those changes were made, but it was a while ago.

The reason for your error is that the restore GUI will by default try to do a tail log backup using the NORECOVERY option. That will fail if there are users in that database.

Answer 4

how about something like that? I'm restoring database named [Northwind] into a new database named [NORTHWND_new]

be aware that I'm changing the name of data and log files.

USE [master]

RESTORE DATABASE [NORTHWND_new]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\Northwind.bak'
WITH FILE = 1, MOVE N'Northwind' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\NORTHWND_new.MDF', 
    MOVE N'Northwind_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\NORTHWND_new_log.ldf', NOUNLOAD, STATS = 5
GO

Answer 5

The steps that worked for me I found here. Create a backup of the database you want to clone first, then restore it as a new database after renaming the current one:

-- Run this script in SQL Server Management Studio:

USE [MyDatabase];
-- Changing Physical names and paths
ALTER DATABASE MyDatabase MODIFY FILE (NAME = 'MyDatabase', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\MyNewDatabase.mdf');
ALTER DATABASE MyDatabase MODIFY FILE (NAME = 'MyDatabase_log', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\MyNewDatabase_log.ldf');
-- Changing logical names
ALTER DATABASE MyDatabase MODIFY FILE (NAME = MyDatabase, NEWNAME = MyNewDatabase);
ALTER DATABASE MyDatabase MODIFY FILE (NAME = MyDatabase_log, NEWNAME = MyNewDatabase_log);

-- Right click on the DB and select Tasks --> Take Offline
-- Go to the location that MDF and LDF files are located and rename them exactly as you specified in first two alter commands. If you changed the folder path, then you need to move them there.
-- Go back to Microsoft SQL Server Management Studio and right click on the DB and select Tasks --> Bring Online
-- Right click database and rename it to the new name