restore to a different DB name

Question

Post reply

restore to a different DB name

Kathleen Rider

SSC Rookie

Points: 46
More actions
April 11, 2007 at 9:47 am

#119548

I need to "refresh" a training DB with a backup from a live DB. The restore is from a full backup and I have it set to overwrite the existing DB using MOVE. The failure message is :
"RESTORE cannot process the database because it in use by this session. It is recommended that the master database be used when performing this operation."
I have killed the process for my target DB prior to running the restore scipt, but still get the error. How do I run the restore from the master?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

Could you post the SQL you are using to restore the database?

Kathleen Rider SSC Rookie Points: 46 More actions · Answer 2

I just figured it out after the initial posting. I just had to get out of SQL Server Management Studio and run the scripts independently - which opens it back up again but without attaching to a database. Hence the "run from master" message.

Thanks for trying to help though.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

The message was telling you that you were trying to run the restore operation while you were in the database. If you have enterprise manager (or query analyzer for that matter) connected to the database you want to restore, the restore operation cannot take the database offline to perform the operation.

Rick Townsend Ten Centuries Points: 1228 More actions · Answer 4

For the record: You can't connect to a database server without connecting to a database. When you closed SSMS then opened it again, you connected to database set up as your default prerefence on your user account. That's probably "Master", which is a good one to use because it is guaranteed to exist on any server. (If you changed your default, to say "Northwind", and then Northwind goes offline, you'd have to change your connection details before you could connect to the server!)

So rather than shut down SSMS before running your restore script, just put

USE MASTER;

at the top.

Rick
townsends.ca

Kathleen Rider SSC Rookie Points: 46 More actions · Answer 5

Thanks. Good to know. It's also in a fail-over cluster environment that took a try or two to figure out that I needed to be logged on to the active server and not the cluster.