1

I have confirmed via testing that clicking on the refresh icon (one or more times) for the database node of a SQL Server instance being observed in SQL Server Management Studio (SSMS 2012) causes my 3rd-party database restore software to fail. If the refresh icon is not clicked, then the restore succeeds.

My question: what occurs in the SQL Server engine (or the msdb database) when the refresh icon is clicked?

Update (2:36pm) When the failure occurs, one database is left in a restoring state. The specific error is "The database cannot be recovered because the log was not restored."

Update (2:48pm) The 3rd-party database restore software is GUI-driven. A restore script is not available to post. I will capture the commands via Profiler.

Update (3:30pm) - restore script captured from Profiler (with no SSMS refresh)

select name, physical_name from model.sys.database_files
select name from master..sysdatabases where name = 'DC_EARTH001'
select DatabaseProperty( 'DC_EARTH001', 'IsInLoad' )
select database_id from master.sys.databases where name ='DC_EARTH001'
select DatabaseProperty( 'DC_EARTH001', 'IsSuspect' )
select DatabaseProperty( 'DC_EARTH001', 'IsShutdown' )
select name, physical_name from model.sys.database_files
RESTORE DATABASE [DC_EARTH001] FROM VIRTUAL_DEVICE='MVD_SQLAgent-DC_EARTH001__a6d9fb41_c3fc_4b00_a78e_596dd32fc149_' WITH SNAPSHOT, NORECOVERY, MOVE 'DC_EARTH001' TO 'X:\DC_EARTH001.mdf', MOVE 'DC_EARTH001_log' TO 'Y:\DC_EARTH001_log.ldf'

select name, physical_name from model.sys.database_files
select name from master..sysdatabases where name = 'DC_EARTH002'
select DatabaseProperty( 'DC_EARTH002', 'IsInLoad' )
select database_id from master.sys.databases where name ='DC_EARTH002'
select DatabaseProperty( 'DC_EARTH002', 'IsSuspect' )
select DatabaseProperty( 'DC_EARTH002', 'IsShutdown' )
RESTORE DATABASE [DC_EARTH002] FROM VIRTUAL_DEVICE='MVD_SQLAgent-DC_EARTH002__584bf879_215d_4c15_b7ad_230ecab60488_' WITH SNAPSHOT, NORECOVERY, MOVE 'DC_EARTH002' TO 'X:\DC_EARTH002.mdf', MOVE 'DC_EARTH002_log' TO 'Y:\DC_EARTH002_log.LDF'

select name from master..sysdatabases where name = 'DC_EARTH003'
select name, physical_name from model.sys.database_files
select DatabaseProperty( 'DC_EARTH003', 'IsInLoad' )
select database_id from master.sys.databases where name ='DC_EARTH003'
select DatabaseProperty( 'DC_EARTH003', 'IsSuspect' )
select DatabaseProperty( 'DC_EARTH003', 'IsShutdown' )
RESTORE DATABASE [DC_EARTH003] FROM virtual_device = 'DC_EARTH003_00__416c5ed9_54e9_4ad1_9adc_3c7401c3fd57_' WITH NORECOVERY , MOVE 'DC_EARTH003_data' TO 'X:\DC_EARTH003.mdf', MOVE 'DC_EARTH003_log' TO 'Y:\DC_EARTH003.ldf'

exec xp_msver
select convert( char(100), ServerProperty('Edition') )
select is_srvrolemember( 'sysadmin', 'NT AUTHORITY\SYSTEM' )
exec sp_helpsort
select name, source_database_id from master.sys.databases order by name

select DatabaseProperty( 'DC_EARTH003', 'IsTruncLog' )
select status from sysdatabases where name='DC_EARTH003'
RESTORE DATABASE [DC_EARTH003] WITH RECOVERY

select DatabaseProperty( 'DC_EARTH001', 'IsTruncLog' )
select status from sysdatabases where name='DC_EARTH001'
RESTORE DATABASE [DC_EARTH001] WITH RECOVERY

select DatabaseProperty( 'DC_EARTH002', 'IsTruncLog' )
select status from sysdatabases where name='DC_EARTH002'
RESTORE DATABASE [DC_EARTH002] WITH RECOVERY

Update (2015/04/07 @ 9:25am) Solution proposed by Microsoft: SQL Server 2012 SP1 CU1

Update (2015/04/07 @ 9:25am) Solution implemented: SQL Server 2012 SP1 CU15 http://support.microsoft.com/en-us/kb/3038001

  • The issue is resolved
  • 2
    Running a profiler reveals that it run this sql in the background when you refresh the databases node in SSMS. I would doubt if that affects your restore. You should use alter database db_name set single_user with rollback immediate; waitfor delay '00:00:05'; alter database db_name set multi_user with rollback immediate;. This will kill all the connections to that particular database and then run your restore database script. – Kin Shah Apr 6 '15 at 18:34
  • The database cannot be recovered because the log was not restored. This is a different error than what you meant. You have to use WITH REPLACE option when doing restore of a current database. Can you post your restore script ? – Kin Shah Apr 6 '15 at 18:44
  • Are you trying to restore on existing databases ? In other words, do the DC_EARTH003, 001, 002 etc exist on the server that you are trying to restore ? – Kin Shah Apr 6 '15 at 19:40
  • @Kin: the databases being restored do not exist on the target. – EngineeringSQL Apr 6 '15 at 19:44
  • You have to re-restore the database. Drop the database that is left in restoring state. You may want to consult the 3rd party tool provider why the database is screwed up. – Kin Shah Apr 6 '15 at 19:54
2

SQL Server 2012 SP1 CU15 (http://support.microsoft.com/en-us/kb/3038001) resolves the issue (no deadlock occurs as the result of issuing a command during a database restore).

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.