Differential restore fails after full.

ab_pai

I'm trying to set up an automated restore of a weekly full followed by a nightly differential. The full restore WITH NORECOVER restores correctly, however the differential (immediately following) fails with the following error:

SQL Backup log file 6.4.0.56

-SQL "RESTORE DATABASE [ATMReportsLive] FROM DISK = 'I:\SQLBackupStore\Thursday\DIFF_(local)_ATMReportsLive_20110428_*.sqb' WITH DISCONNECT_EXISTING, STANDBY = 'I:\LogShippingUndo\ATMReportsLive_Undo.DAT' "

---

ERRORS AND WARNINGS

---

4/29/2011 4:31:17 PM: Restoring ATMReportsLive (database) from:
4/29/2011 4:31:17 PM: I:\SQLBackupStore\Thursday\DIFF_(local)_ATMReportsLive_20110428_233000.sqb

4/29/2011 4:31:17 PM: ALTER DATABASE [ATMReportsLive] SET ONLINE RESTORE DATABASE [ATMReportsLive] FROM VIRTUAL_DEVICE = 'SQLBACKUP_7BFC1D4A-79A1-42C0-9A9B-67FC0BFE418C', VIRTUAL_DEVICE = 'SQLBACKUP_7BFC1D4A-79A1-42C0-9A9B-67FC0BFE418C01' WITH
BUFFERCOUNT = 12, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , STANDBY = N'I:\LogShippingUndo\ATMReportsLive_Undo.DAT'

4/29/2011 5:14:32 PM: SQL Server error

4/29/2011 5:14:32 PM: SQL error 5069: SQL error 5069: ALTER DATABASE statement failed.
4/29/2011 5:14:32 PM: SQL error 5052: SQL error 5052: ALTER DATABASE is not permitted while a database is in the Restoring state.

The restore of the full backup is scripted as follows:

SQL Backup log file 6.4.0.56

-SQL "RESTORE DATABASE [ATMReportsLive] FROM DISK = 'I:\SQLBackupStore\Full\FULL_(local)_ATMReportsLive_20110423_*.sqb' WITH NORECOVERY, DISCONNECT_EXISTING, REPLACE, MOVE 'ATMReportsDev' TO 'P:\SQL Server\ATMReportsLive_data1.mdf', MOVE 'Indexes' TO
'I:\SQL Server\ATMReportsLive_index1.ndf', MOVE 'ATMReportsLive_BankcardBusinesses' TO 'S:\SQL Server\ATMReportsLive_BankcardBusinesses.ndf', MOVE 'ATMReportsLive_Busniesses' TO 'S:\SQL Server\ATMReportsLive_Busniesses.ndf', MOVE 'ATMReportsLive_Contacts'
TO 'S:\SQL Server\ATMReportsLive_Contacts.ndf', MOVE 'ATMReportsLive_HierarchyRelationships' TO 'S:\SQL Server\ATMReportsLive_HierarchyRelationships.ndf', MOVE 'ATMReportsLive_Terminals' TO 'S:\SQL Server\ATMReportsLive_Terminals.ndf', MOVE
'ATMReportsLive_UserAccess' TO 'S:\SQL Server\ATMReportsLive_UserAccess.ndf', MOVE 'ATMReportsLive_UserBankcardRoots' TO 'S:\SQL Server\ATMReportsLive_UserBankcardRoots.ndf', MOVE 'ATMReportsLive_UserHierarchyCalculation' TO 'S:\SQL
Server\ATMReportsLive_UserHierarchyCalculation.ndf', MOVE 'ATMReportsLive_Users' TO 'S:\SQL Server\ATMReportsLive_Users.ndf', MOVE 'Realtime_data1' TO 'S:\SQL Server\ATMReportsLive_Realtime_data1.ndf', MOVE 'Realtime_data2' TO 'S:\SQL
Server\ATMReportsLive_Realtime_data2.ndf', MOVE 'Realtime_data3' TO 'S:\SQL Server\ATMReportsLive_Realtime_data3.ndf', MOVE 'Realtime_data4' TO 'S:\SQL Server\ATMReportsLive_Realtime_data4.ndf', MOVE 'ATMReportsDev_log' TO 'L:\SQL Server\ATMReportsLive_lo
g.ldf' "

---

PROCESSES COMPLETED SUCCESSFULLY

---

4/29/2011 1:22:33 PM: Restoring ATMReportsLive (database) from:
4/29/2011 1:22:33 PM: I:\SQLBackupStore\Full\FULL_(local)_ATMReportsLive_20110423_203000.sqb

4/29/2011 1:22:33 PM: ALTER DATABASE [ATMReportsLive] SET ONLINE RESTORE DATABASE [ATMReportsLive] FROM VIRTUAL_DEVICE = 'SQLBACKUP_3283D12B-221A-4710-95A0-E7C8D94CFACF', VIRTUAL_DEVICE = 'SQLBACKUP_3283D12B-221A-4710-95A0-E7C8D94CFACF01' WITH
BUFFERCOUNT = 12, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , NORECOVERY, MOVE 'ATMReportsDev' TO 'P:\SQL Server\ATMReportsLive_data1.mdf', MOVE 'Indexes' TO 'I:\SQL Server\ATMReportsLive_index1.ndf', MOVE 'ATMReportsLive_BankcardBusinesses' TO
'S:\SQL Server\ATMReportsLive_BankcardBusinesses.ndf', MOVE 'ATMReportsLive_Busniesses' TO 'S:\SQL Server\ATMReportsLive_Busniesses.ndf', MOVE 'ATMReportsLive_Contacts' TO 'S:\SQL Server\ATMReportsLive_Contacts.ndf', MOVE 'ATMReportsLive_HierarchyRelation
ships' TO 'S:\SQL Server\ATMReportsLive_HierarchyRelationships.ndf', MOVE 'ATMReportsLive_Terminals' TO 'S:\SQL Server\ATMReportsLive_Terminals.ndf', MOVE 'ATMReportsLive_UserAccess' TO 'S:\SQL Server\ATMReportsLive_UserAccess.ndf', MOVE
'ATMReportsLive_UserBankcardRoots' TO 'S:\SQL Server\ATMReportsLive_UserBankcardRoots.ndf', MOVE 'ATMReportsLive_UserHierarchyCalculation' TO 'S:\SQL Server\ATMReportsLive_UserHierarchyCalculation.ndf', MOVE 'ATMReportsLive_Users' TO 'S:\SQL
Server\ATMReportsLive_Users.ndf', MOVE 'Realtime_data1' TO 'S:\SQL Server\ATMReportsLive_Realtime_data1.ndf', MOVE 'Realtime_data2' TO 'S:\SQL Server\ATMReportsLive_Realtime_data2.ndf', MOVE 'Realtime_data3' TO 'S:\SQL Server\ATMReportsLive_Realtime_data3
.ndf', MOVE 'Realtime_data4' TO 'S:\SQL Server\ATMReportsLive_Realtime_data4.ndf', MOVE 'ATMReportsDev_log' TO 'L:\SQL Server\ATMReportsLive_log.ldf', REPLACE

4/29/2011 4:31:17 PM: Processed 60255304 pages for database 'ATMReportsLive', file 'ATMReportsDev' on file 1.
4/29/2011 4:31:17 PM: Processed 20713208 pages for database 'ATMReportsLive', file 'Indexes' on file 1.
4/29/2011 4:31:17 PM: Processed 10216 pages for database 'ATMReportsLive', file 'ATMReportsLive_BankcardBusinesses' on file 1.
4/29/2011 4:31:17 PM: Processed 6824 pages for database 'ATMReportsLive', file 'ATMReportsLive_Busniesses' on file 1.
4/29/2011 4:31:17 PM: Processed 12880 pages for database 'ATMReportsLive', file 'ATMReportsLive_Contacts' on file 1.
4/29/2011 4:31:17 PM: Processed 13288 pages for database 'ATMReportsLive', file 'ATMReportsLive_HierarchyRelationships' on file 1.
4/29/2011 4:31:17 PM: Processed 8784 pages for database 'ATMReportsLive', file 'ATMReportsLive_Terminals' on file 1.
4/29/2011 4:31:17 PM: Processed 2880 pages for database 'ATMReportsLive', file 'ATMReportsLive_UserAccess' on file 1.
4/29/2011 4:31:17 PM: Processed 224 pages for database 'ATMReportsLive', file 'ATMReportsLive_UserBankcardRoots' on file 1.
4/29/2011 4:31:17 PM: Processed 423592 pages for database 'ATMReportsLive', file 'ATMReportsLive_UserHierarchyCalculation' on file 1.
4/29/2011 4:31:17 PM: Processed 624 pages for database 'ATMReportsLive', file 'ATMReportsLive_Users' on file 1.
4/29/2011 4:31:17 PM: Processed 582272 pages for database 'ATMReportsLive', file 'Realtime_data1' on file 1.
4/29/2011 4:31:17 PM: Processed 583160 pages for database 'ATMReportsLive', file 'Realtime_data2' on file 1.
4/29/2011 4:31:17 PM: Processed 582728 pages for database 'ATMReportsLive', file 'Realtime_data3' on file 1.
4/29/2011 4:31:17 PM: Processed 583336 pages for database 'ATMReportsLive', file 'Realtime_data4' on file 1.
4/29/2011 4:31:17 PM: Processed 73491 pages for database 'ATMReportsLive', file 'ATMReportsDev_log' on file 1.
4/29/2011 4:31:17 PM: RESTORE DATABASE successfully processed 83852811 pages in 11119.022 seconds (61.779 MB/sec).
4/29/2011 4:31:17 PM: SQL Backup process ended.

Any thoughts, ideas, suggestions???

-A

Eddie D

Thank you for your post and sorry that you have encountered a problem.

If using the Restore Wizard, can you please retry the restore process and ensure that the option "Kill any existing connections to the database" is not selected or unchecked.

If scripting please retry the restore process and remove the DISCONNECT_EXISTING keyword from the script.

Please reply to let us know if the restore is successful or not?

Many Thanks
Eddie

ab_pai

Removing the DISCONNECT_EXISTING from the WITH clause of the SQL argument to the RESTORE command fixed the issue. I didn't realize that the disconnect_existing performed ALTER statements as part of it's function.

Thanks!