Link to home
Create AccountLog in
Avatar of 105881
105881Flag for United States of America

asked on

SQL 2008 R2 Restore can not start

I have a SQL job that runs a BACKUP of datebase MAIN everyday. After the backup completes a job runs to RESTORE to the REPORTS database. The REPORT database is replaced everyday with yesterday's data so that reports can be run from REPORT and not load down production.
I also have SSRS running against REPORT.

Here is the SQL:
ALTER DATABASE REPORT SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Use Master
Go

Declare @dbname sysname

Set @dbname = 'REPORT'

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End

RESTORE DATABASE REPORT
    FROM DISK = 'D:\DBBackup\REPORT_data.BAK'

Open in new window



The problem is that RESTORE keeps failing with error:

Changes to the state or options of database 'REPORT' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

I've used Activity monitor to disconnect all users and the process still fails.
The only way I've found to have a successful restore is to restart the REPORT database service.

I also ran EXEC SP_who2 and the only connection to REPORT is a backgroud process with a command of CHECKPOINT
Avatar of Kent Dyer
Kent Dyer
Flag of United States of America image

Here is one post that I saw out there on how to address this..

http://remidian.com/2008/01/remove-sql-server-database-from-single-user-mode/

HTH,

Kent
Avatar of 105881

ASKER

The problem is non-user sessions (spid < 50)  they were blocking me.
I'm going to restore over the existing database, so I don't care about the existing connections to the database. I want all connections terminated.
I want all connections terminated.
Then you will have to identify them using something like sp_who and then KILL one by one, until there are no more connections.
hi

1. delete the database . (check the disconnect all connections checkbox)
2. restore the database.
Avatar of 105881

ASKER

I can delete the database if I manually run the restore but I'm trying to use an automated job to run the restore.
Check services running on box, shut down FTS, SSIS and RptSvcs, leaving only SQL Srvr and SQL Srvr Browser services running and lo and behold, ADMIN:MTRDEV06\SQL_DEV works and opens session!!

Refer:
http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/17c2744c-2e18-45c8-8b88-0e0b5db6461c/
ASKER CERTIFIED SOLUTION
Avatar of 105881
105881
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of 105881

ASKER

I was an update issue