cancel
Showing results forΒ 
Search instead forΒ 
Did you mean:Β 

Unable to restore SQL2008 master db only from a $ALL db backup

redmantra
Level 3
Partner Accredited

Hi,

 

I have a Windows 2008 R2 client running SQL 2008.  I am currently doing a DR test whereby the master sql db is recovered first before recoverin all the other databases.  The SQL server is not a cluster and so moving sql into single user mode and back out again is automated by NetBackup 7.5.0.7.

After losts of tests, I have found a pattern in the issue I am having.  If I backup all the databases ($ALL) using a SQL policy or running from the client, I cannot recover the master only,  but if I configure a SQL policy to backup the master only and/or run a master only backup from the client, I can successfully restore the master db.

The master db shows as restoring successfully, but fails when trying to restart in mult-user mode.  I am assuming my permissions are OK as I can restore the master successfully from a master only backup.

I have found lots of references to not being able to restore the master db, but nothing with this pattern.

So my question is whether this is the way it works and I will need to create a seperate policy for master backups only or is there a config issue I am missing.

I have logged a support call with Symantec, but as yet not helpful.  Extract from activity monitor below.

Thanks,

Geoff.

begin Restore
INF - RESTORE STARTED USING       
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   
Jun 28 2012 08:36:30        
Copyright (c) Microsoft Corporation        
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Batch = D:\Program Files\Veritas\NetBackup\dbext\mssql\temp\__11_14_21_233_00.bch, Op# = 1.     
INF - Using backup image SQL_CLIENT.MSSQL7.SQL_CLIENT.db.master.~.7.001of001.20150115105201..C      
INF - restore database "master" from VIRTUAL_DEVICE='VNBU0-1904-3264-1421320463' with blocksize = 65536, maxtransfersize = 4194304, buffercount = 1, replace , recovery
INF - Number of stripes: 1, Number of buffers per stripe 1.
INF - Created VDI object for SQL Server instance <SQL_CLIENT>. Connection timeout is <300> seconds.
INF - Thread has been closed for stripe #0   
ERR - Received error 1068 while trying to start service status SQLServerAgent
CONTINUATION: - The dependency service or group failed to start.
ERR - Could not restart SQL Server service in multi-user mode. 
ERR - Error in VDS->Close: 0x8077000d.      
CONTINUATION: - All devices are open  
INF - OPERATION #1 of batch D:\Program Files\Veritas\NetBackup\dbext\mssql\temp\__11_14_21_233_00.bch FAILED with STATUS 1 (0 is normal). Elapsed time = 227(227) seconds.
INF - Results of executing <D:\Program Files\Veritas\NetBackup\dbext\mssql\temp\__11_14_21_233_00.bch>:     
<0> operations succeeded. <1> operations failed.

1 ACCEPTED SOLUTION

Accepted Solutions

redmantra
Level 3
Partner Accredited

Looks like the issue was down to the SQL client being in the wrong OU.

Thanks for pointers anyway.

Geoff.

View solution in original post

4 REPLIES 4

Yasuhisa_Ishika
Level 6
Partner Accredited Certified

As per NetBackup for MSSQL Administrator's Guide, single user mode is needed in clustered DB. in single node DB, just run restore in multi user mode after rebuilding master db by rebuildm.exe or so(depends on SQL Server version).

redmantra
Level 3
Partner Accredited

Thanks for info Yasuhisa.  As you can see from my description this is non clustered and so NBU will do the single to multi user mode itself as you say.  However, there is no requirement for me to rebuild the master db as I can recover it only if backed up with system db's.  If I backup both system and user db's in one policy (i.e. $ALL), then I cannot recover the master which is what you need to do in a DR scenario.

So now, I will need three policies.

  1. Full backup of system db's on a weekly basis.
  2. Full backup of user db's on a weekly basis
  3. Differential backup of user db's daily.

Ideally I was hoping for two policies only.

Yasuhisa_Ishika
Level 6
Partner Accredited Certified

I'm not sure the difference of restore regarding backup policy configuration, but in my experience of POC test in previous version, I could restore master db from $ALL backup. Try again without restarting SQL with /m switch.

redmantra
Level 3
Partner Accredited

Looks like the issue was down to the SQL client being in the wrong OU.

Thanks for pointers anyway.

Geoff.