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 220.127.116.11.
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.
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.
Solved! Go to Solution.
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).
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.
Ideally I was hoping for two policies only.
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.