SQL 2014 Restore Live Data Over Test DB

(0) Share

Report

Posted on by RussK

1,875

Is anyone else having trouble restoring a backup of a production database over a test database to refresh the data? I have followed the instructions to create a test company (and therefore the DB), backed up the production DB, but I cannot restore over the top of the test DB. The restore fails with a 'database in use' error or that it cannot overwrite the .mdf of the test DB because 'the test DB is using the file'. I have done hundreds of restores in my time and I can't help but think this has something to do with SQL 2014... I know there are some new features that are supposed to bring more flexibility to testing and maintaining connections, but we need to be able to restore DBs. Anyone else having this issue?

*This post is locked for comments

I have the same question (0)

All responses (17)

Answers (1)

Richard Wheeler 75,850 Moderator on at

Like (0)

Report

Have you check the Activity Monitor to make sure no other process has this database locked?

SSRS or another SSMS session? I have not had any issues restoring a database using 2014.

Was this reply helpful? Yes No
Suggested answer

KirkLivermont 5,985 on at

Like (0)

Report

Any chance the Management Reporter services are still running? Stop both of them and try again.

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

Interesting that you post this - I am having the same issue with a client today and I was thinking I must be missing something simple - but I get the same error.

Using SQL 2014 is not any simpler - but I will get it figured - just know you are not alone.

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

Just because I want to be tough - here is the same issue presented in SQL2008

The database in question - was just created by me and no one be SA has assess

Restore from a backup of an active database from Nov 17th

Not sure how this might be related but this just happened right now after reading the article and I had to share.

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

Ok this is nuts. Now I try to restore to SQL 2008 R2 and I have done this 100's of times from one database (production) to two database (test) from a backup of one database 5 days old - so not in use - to the two database (test) also not in use, as SA is only user with access to this specific DB

The management reporter lists it as a company, but the users do not have access to it and if they did there are no reports created for them to access.

I have attempted to restore the database as database as filegroups and northing matters - it tells me the database is in use.

Where exactly can I go in SQL 2008 to see the databases that are in use and by which user? Sorry, but Activity Monitor does not make it work for me - help.

Was this reply helpful? Yes No
Richard Wheeler 75,850 Moderator on at

Like (0)

Report

Have you turned off the services for MR 2012 as they will pull a lock on the company databases as long as they are running.

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

Stopping the MR2012 services is good advice. Once that's done, take a look my blog post on resolving this problem.

redbeardblogging.blogspot.com/.../restore-failed-for-server-exclusive.html

Was this reply helpful? Yes No
Suggested answer

Tom Cruse 1,477 on at

Like (0)

Report

I'm running 2012 and you need to make sure when you choose restore to click on Options and then check the 'Close existing connections to destination database' box.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Just want to second what the others have said.

My steps for a test company restore are

1: Use Dynamics Select * from ACTIVITY

to make sure no users are logged in to the test company

2. Open Management Reporter Configuration Console and Stop both the Application and Process Service

3. Check Activity Monitor in SSMS and filter on the Test DB to make sure no other 3rd party apps are hitting it.

4. Follow the steps here: support.microsoft.com/.../871973 to restore the Data and Log files correctly and run the script to change COMPANYID and INTERID for the test DB.

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

Like the idea, but that is not available on SQL 2008 R2 - but I did like the idea as it makes the most sense.

Was this reply helpful? Yes No