CODEWITHSUNDEEP
sql-serverdatabasebackupdatabase-backupssql-server-2017
Erica Stockwell-Alpert
Erica Stockwell-Alpert

Reputation: 4863

Cannot create a copy of a database with a new name by restoring from backup: "the database is in use"

I have two sets of databases for my different testing environments (internal qa and uat). I'm trying to bring qa up to date by restoring it from the latest uat backups. I encountered an issue with the qa DBs getting stuck in "restoring" mode and ended up deleting them, so I'm trying to create a brand new database now by restoring from the UAT backup and changing the name, but it keep failing.

Restore database

Source: device > latestUATbackup.bak

Destination: database > change name from UAT.Web to Dev.Web

Files > check off Relocate all files to folder

Options: Close existing connections to destination database

"Restore of database Dev.Web failed. Access could not be obtained because the database is in use"

I tried taking UAT.Web offline but then it fails with the error "UAT.Web cannot be opened because it is offline"

Why would it matter if the database is in use when I'm using a backup? What do I need to do?

Upvotes: 0

Views: 247

Answers (2)

Cookie Monster
Cookie Monster

Reputation: 475

Go to Options and tick the 'Close existing connections to destination database' option before restoring.

Upvotes: 0

squillman
squillman

Reputation: 13641

You can't restore a database that is in use because the restore would put it in an inconsistent state. You need to disconnect all active connections (including all SSMS query windows and other applications) from the database in order to restore it. If it already exists make sure to check "Overwrite existing database" on the Options tab of the restore window.

On a side note, up to you but I would recommend not using a '.' in the database name. It can get confusing when using fully qualified object names that include the database.

Upvotes: 0

Related Questions