Restoring Master, Model and MSDB from Netbackup

Restoring System Databases got two parts:


 Taking the instance in single user mode and Restoring the Master Database

 If you need to rebuild the system databases on top of this you can use the below command:
"C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\setup.exe" /ACTION=REBUILDDATABASE /INSTANCENAME=SQL008 /SQLSYSADMINACCOUNTS=domain\ServiceUserName /SAPWD="yourpasswordOfSAuser" /SQLCOLLATION="Latin1_General_CI_AS"
here 100 is for SQL Server 2008R2. It can vary case by case.


How to take instance to single user mode:

Two situation can happen:
1. Standalone Instance 2. Failover Cluster Instance

1. Standalone Instance:

  • Go to SQL Config Manager
  • SQL Service Properties -> Advanced Properties -> Startup Parameters
  • put -m; in the Startup Parameters
  • Apply and Okay - it will ask to restart the SQL Service to take effect.
  • Stop and Start the service from Config Manager or you can stop and service via cmd prompt 
  • net stop "SQL Server (SQL001)"  [or what ever the instance name]
  • net start "SQL Server (SQL001)" /m  [here m stands for single user mode]
  • Once the master db is restored go to the config manager again and then take off  '-m;' parameter from Advanced Properties -> Startup Parameter
  • Stop and start the service like before but this time ..
  • net start "SQL Server (SQL001)"  [this time no /m option]

2. Failover Cluster Instance:

  • Go to SQL Config Manager
  • SQL Service Properties -> Advanced Properties -> Startup Parameters
  • put -m; in the Startup Parameters
  • Apply and Okay - it will ask to restart the SQL Service to take effect.
  • Then go to cluadmin.msc or Failover Cluster GUI and select the Service. In resource list select the SQL Instance Name and take it offline.
  • Stop and Start the service from Config Manager or you can stop and service via cmd prompt 
  • net stop "SQL Server (SQL001)"   [or what ever the instance name]
  • net start "SQL Server (SQL001)" /m   [here m stands for single user mode]
  • Once the master db is restored go to the config manager again and then take off  '-m;' parameter from Advanced Properties -> Startup Parameter
  • Stop the SQL service and then 
  • net start "SQL Server (SQL001)"  [this time no /m option]
  • Then go to cluster manager and turn the SQL Server resource ON.

In both cases SQL Agent Service needs to be offline and stopped.

Now restore the Master Database and Other system DBs

Please use the below link to do it from Netbackup:
https://www.veritas.com/support/en_US/article.000007316
If you need to restore it from Native backup file below would be the command:

SqlCmd -E -S "SQL001" –Q "RESTORE DATABASE [master] FROM DISK=’D\BackupsMyDB.bak'"

For restoring msdb and other databases instance doesn't need to be in single user mode. but the agent needs to be stopped.

Errors:

From Management Studio You can see below error while browsing the DBs:
Database 'msdb' cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927


I fixed it by running:


RESTORE DATABASE MSDB WITH RECOVERY;





helpful links:

https://joeydantoni.com/2012/07/27/starting-a-sql-server-clustered-instance-in-single-user-mode/


































Comments















Post a Comment



















Popular posts from this blog










Oracle Job Operations





















As a DBA, often I need to deal with Client's Jobs. Sometimes the jobs got hault, needs to break etc etc. Here are some useful commands for this sort of operations. ## Job Owner Schema can do this execute dbms_job.remove(444); execute dbms_job.broken(444, true); ## If Sys User needs to remove the job exec SYS.DBMS_IJOB.REMOVE(444); ## to find which jobs are running SELECT a.sid,        c.serial#,        a.job,        a.failures,        to_char(a.this_date, 'mm/dd/yyyy hh:mi pm') startdatetime,        b.what   FROM dba_jobs_running a, dba_jobs b, v$session c  WHERE a.job = b.job    AND a.sid = c.sid  order by a.this_date ##let say we have only sid and serial and we need to kill the process from both OS and database. So first we need to find out the SPID. this query for any RAC database select P.SPID ,S.SID ,S.SERIAL#, S.SCHEMANAME, S.PROGRAM, S.INST_ID, S.OSUSER, S.STATUS from gv$process p, gv$session s where P.ADDR=S.PADDR and S.SID=888 and s.serial#=9293; ## kill the job al...








Read more