We help IT Professionals succeed at work.

Restore a database

320 Views
Last Modified: 2012-10-27
Hello all,

I'm trying to restore an existing database backup to my new database.
However, I'm getting an error message that the restoration cannot be done
because the database is already in use.
Any ideas how to overpass this problem?
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
you have to check the "overwrite" option when restoring, in case you use SQL, include the WITH REPLACE syntax part...
the_b1ackfoxTechnophile

Commented:
Restore it as another name if you are looking to restore only certain tables... other wise, drop the current DB, and then restore from the backup

Commented:
it can't be restored over the top of a database if that database has any connections to it. You'll need to determine if these processes are legitimate. They can be SQL Query windows that are open, services that are logged into the the database, etc.

Open Management Studio, Drill down into the server where you are restoring to. go into the Management section and open the Activity Monitor. Look in there for anything with the name of the database you're trying to restore to in the "Database" column. You can right click on that row and kill the process. First make sure that it's ok to kill.

Once there are no rows for that database, try your restore again.

Commented:
Oh, forgot 2005 has a check box in the restore window to Drop existing connections to the db. That will clear connections of people using the db.

Author

Commented:
AngelIII,
I have already checked the overwrite option.
b1ackfox, I am using a new database with a different name. I can't drop the current db because
I'm using it.

Author

Commented:
Where are these options i2Mental (to drop the connections?) I'm in the restore panel but can't see them

Commented:
ehh... actually. I think I was thinking of the drop database window for that check box. Restore just restores without a confirmation window.

Author

Commented:
i2Mental,

for my database there are only 2 processes which are running under the Management
section but they are sleeping. I'm trying to kill them but I get nothing as a response from Mgmt Studio.
I have stopped the server but nothing helped!

Any ideas?
If the database is online you cannot restore on top of it. It will throw an error that you are getting. Put that database in the SINGLE USER MODE.
You can do so by going to the Properties-->Option of the target database and select SINGLE USER MODE. Once thats done Make sure that on the Backup Exec you have selected the correct option to redirect the restore of the Database on this new target database.

Hope this helps.

Author

Commented:
"Make sure that on the Backup Exec you have selected the correct option to redirect the restore of the Database on this new target database."

How would I do that?
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.