• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 594
  • Last Modified:

SQL 2008 Restore issue

I have a backup file .bak from my live database.

I have run a script that kills all connections to the database.

I have set the data base to single user

I then execute the script generated by SQL SERVER MANAGEMENT STUDIOand get the attached error?

Can someone help me out here
restore-error.jpg
0
Jeff_Kingston
Asked:
Jeff_Kingston
1 Solution
 
microheadCommented:
Where do you start the SQL Server Management Studio?
I've run into this problem when i set the database to single user mode on my workstation en try to execute a script, because a background process beat me to it.

If i run the management studio on the server itself, I've never had this problem..

Grt Dimitry
0
 
Scott PletcherSenior DBACommented:
Forget the "script" to KILL connections/users; that's an obsolete method.



USE [iMIS_MCLE_15TEST]

-- cancel all other users
ALTER DATABASE [iMIS_MCLE_15TEST]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

USE master

RESTORE DATABASE [iMIS_MCLE_15TEST]
...

-- just to be safe/sure db is now multi-user
ALTER DATABASE [iMIS_MCLE_15TEST]
SET MULTI_USER
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Ramesh Babu VavillaCommented:
You cant restore a database when it is in recovery mode
0
 
Jeff_KingstonAuthor Commented:
Strangely enough I ran this script multiple times an got the same problem.

Came in today and magically the script ran to completion.

I replaced your restore with the script option output from the script option in the restore task.  Now all I'll ever have to do is modify the name of the .bak file

Thanks
0
 
Scott PletcherSenior DBACommented:
The connection script's KILLs likely started transaction rollback(s).  Once a rollback starts, it has to finish before you can restore the db.

The ALTER DATABASE avoids all that.  This makes sense -- no point in wasting time waiting for SQL to get the db to a consistent point if you're just going to immediately restore over it anyway!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now