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

SQL 2008, scheduled database restore fails

I have a database which is backed up and restored to another machine over night.
The problem is, whenever SQL management studio is left open, it fails. I know I have to close it when I 'm done working with it, but sometimes I forget and it affects everyone next day.


I don't know how setting single user will bring down the database, even the process which had opened management studio. I think I tried this before, it didn't work either.

Next I

Error message on restoring database:

Executed as user: NT AUTHORITY\SYSTEM. Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101)  RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

The scheduled database restore does following;


--Do Actual Restore
RESTORE DATABASE db1
FROM DISK = '\\db-srv\ScheduledBackups\db1.bak'
WITH MOVE 'db1_dat' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db1.mdf',
MOVE 'db1_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db1.ldf',
REPLACE

use db1
GO
SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', 'username', 'username'
GO


I tried to use SQL statements to  kill all process, but it didn't work. I thought SQL management studio doesn't show as active process in process list.

Options what I found online was;

Solution 1:

USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

ref. http://blog.sqlauthority.com/2009/02/06/sql-server-quickest-way-to-kill-all-threads-kill-all-user-session-kill-all-processes/

Solution 2
The other solution I found online is, it specifically set the database offline and it seems to be more promising to close all connections, but still does it close process management studio uses?

ALTER DATABASE <Put Your Database Here> SET OFFLINE WITH ROLLBACK IMMEDIATE

-- And to bring the database online again
ALTER DATABASE <Put Your Database Here> SET ONLINE

http://www.dbatodba.com/sql-server/how-tos/how-to-kill-all-users-in-specific-database-on-sql-server/


I need to test each of solution, but I want to get any additional information before I do it because I won't have much time to do the experiment when I actually do this.
0
crcsupport
Asked:
crcsupport
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would go with a SET OFFLINE, and then directly do the restore, which should bring it back online implicitly. you can try that on a test db restored to even another name, connecting to it, and trying to run the restore there ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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