What are the challenges you face when working across database platforms? Take the survey
Options

BackUp - Job Failed to disconnect existing connections

SandySandy Posts: 2
edited September 30, 2015 9:34PM in SQL Backup Previous Versions
Hi,

I faced some problem with backup using Redgate.

Redgate version is 6.5.1.9. And Our SQL Server is 2008 R2 Standard Edition 64 bit.

Every Morning, we did the restoration to the databases. The restoration job has two steps - Restore Full Backup then Restore Differential Backup.

However, the restoration is usually failed for one DB on certain day (Not everyday). This DB is biggest in the server. 36 GB.

As some log shows it is failed to disconnect existing connection, we add two more steps before restoration for this DB.

Step 1. Change Single User Mode
Step 2. Change Multi User Mode
Step 3. Restore Full BK
Step 4. Restore Differential BK

The job seems to fail at step 3 but it jumps to step 4 and then the DB cannot be accessible. If we manually re-run this job, it is working fine. Btw. the job is scheduled at 5:30 am and we normally re-run it on 8 am if the job fails.

We monitor the connection for this DB around this hour, it didn't show anything much. Once the Step 1 of the Job is executed, the connection are no longer there.

Could you suggest me which other areas do I need to check? Thanks a lot.


The job script is as below.
--FULL RESTORE (Step 3)
EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [MYDB] FROM DISK = ''E:Full_MYDB_Backup.sqb'' WITH NORECOVERY, DISCONNECT_EXISTING, MOVE ''MYDB_dat'' TO ''D:MYDB.mdf'', MOVE ''MYDB_dat2'' TO ''D:MYDB_dat2.ndf'', MOVE ''MYDB_Log'' TO ''E:MYDB_1.ldf'', REPLACE"'


--DIFF RESTORE (Step 4)
EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [MYDB] FROM DISK = ''E:DIFF_(local)_MYDB.sqb'' WITH RECOVERY, ORPHAN_CHECK"'

When I checked in the RedGate log, here is the message
SQL Backup log file 6.5.1.9

-----------------------------  ERRORS AND WARNINGS -----------------------------
28/5/2015 5:30:46 AM: Failed to disconnect existing connections to database.
28/5/2015 5:30:46 AM: 
28/5/2015 5:30:47 AM: Memory profile
28/5/2015 5:30:47 AM: Type             Maximum     Minimum     Average     Blk count   Total      
28/5/2015 5:30:47 AM: ---------------- ----------- ----------- ----------- ----------- -----------
28/5/2015 5:30:47 AM: Commit           1683513344  8192        627428      5066        3178553344 
28/5/2015 5:30:47 AM: Reserve          3707699200  8192        1050526     3667        3852279808 
28/5/2015 5:30:47 AM: Free             7666252775424 8192        33503801862 229         7672370626560
28/5/2015 5:30:47 AM: Private          3707699200  8192        841027      8081        6796345344 
28/5/2015 5:30:47 AM: Mapped           2949120     8192        197017      60          11821056   
28/5/2015 5:30:47 AM: Image            108216320   8192        376126      592         222666752  
28/5/2015 5:30:47 AM: 

SQL Backup log file 6.5.1.9



-----------------------------  ERRORS AND WARNINGS -----------------------------

28/5/2015 5:30:50 AM: Error 890: RESTORE DATABASE permission denied for database:  (MYDB)
28/5/2015 5:30:50 AM: SQL error 913: Could not find database ID 17. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur, contact your primary support provider. Please have available for review the Microsoft SQL Server error log and any additional information relevant to the circumstances when the error occurred.

These are the log from SQL server.
Date,Source,Severity,Message
05/28/2015 05:30:49,spid57,Unknown,Could not find database ID 17. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur<c/> contact your primary support provider. Please have available for review the Microsoft SQL Server error log and any additional information relevant to the circumstances when the error occurred.
05/28/2015 05:30:49,spid57,Unknown,Error: 913<c/> Severity: 16<c/> State: 4.
05/28/2015 05:30:08,spid59,Unknown,Setting database option OFFLINE to ON for database MYDB.
05/28/2015 05:30:01,spid55,Unknown,Setting database option MULTI_USER to ON for database MYDB.
05/28/2015 05:30:00,spid55,Unknown,Setting database option SINGLE_USER to ON for database MYDB.
05/28/2015 00:00:23,spid21s,Unknown,This instance of SQL Server has been using a process ID of 6116 since 12/5/2015 7:15:41 AM (local) 11/5/2015 11:15:41 PM (UTC). This is an informational message only; no user action is required.

Comments

  • Options
    peteypetey Posts: 2,358 New member
    Try removing the DISCONNECT_EXISTING option from the SQL Backup restore command. Then, instead of setting the database to single-user and back to multi-user, set it to offline, then back online, before running the restore command.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.