CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2016
Skadwick
Skadwick

Reputation: 37

Using one backup file for multiple restores in SQL server 2016

We've got one .bak file that we use for testing on Microsoft Server 2016, and SQL Server 2016. Sometimes we want to setup several installations on one server. What we want is several Databases that are identical but separate and with different names, all from the same .bak file.

I can always restore the .bak once, but the second time (to a DB with a different name) I'll get this error: 

Restore of database 'RVT_DB4_2' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.SmoExtended)

In the past I always used SQL 2008 and never had this issue. The .bak file was likely created using SQL 2008. I'm not even sure if we are restoring incorrectly, or if we restored incorrectly. Any tips on what I'm doing incorrectly, or what settings I am missing here?

Thanks!

Upvotes: 0

Views: 804

Answers (2)

Ven
Ven

Reputation: 2014

If the backups were taken with multiple devices that were attached to the server, the same number of devices must be available during an online restore. (microsoft books)

Upvotes: 0

dbajtr
dbajtr

Reputation: 2044

Run sp_who2 and kill off any rouge sessions to the database in question:

EXEC sp_who2

Then you can set the database into single user mode before you do the restore:

ALTER DATABASE 'DATABASE' SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

run the restore then change it back to multi user

ALTER DATABASE 'DATABASE' SET MULTI_USER;

But don't set it to single user and then disconnect or you could have fun trying to get back on it...

Upvotes: 0

Related Questions