System database issues: rebuild, relocation and restore - Are we ready?

"What is the probability that the sun will NOT rise tomorrow?"

“What is the probability that the master database will NOT corrupt tomorrow?”

To answer the first question, we have to live billions of lights years, even more, I don’t know.

But I believe that the master database may corrupt tomorrow; it could be happening right now while you are reading this article. Are we ready to recover it?

In this article, we will be recovering the master database after a disaster (later on msdb, model and tempdb). When the master database has an issue, SQL Server will not start and we may see startup errors.

Good practices and good to know about master database:

Consider following recommendations:

1.        Always have a current backup of the master database available and perform system databases backup, daily basis.

2.        Perform file copy (mdf and ldf) when you stop SQL Server service during maintenance window.

3.        When there is a configuration change, do a backup before and after.

4.        Back up the master database as soon as possible after the following operations:

(a)    Creating, modifying, or dropping any database

(b)    Changing server or database configuration values

(c)     Modifying or adding logon accounts.

5.        Do not create user objects in master.

6.        Do not set the TRUSTWORTHY option to ON for the master database.

7.        Repairing system databases is not an option.

8.        Run database consistency and make sure it’s healthy.

9.        Create alerts to get earliest possible notifications for any issues that could occur.

10.    Never change configuration of any system database except the model database

11.    Location can be changed in SQL Server Configuration Manager.

Part One: Master Database:

We will review all system database’s recovery option. In our first part, we will work on the master database only.

Recovering the master database (figure # 1):

So, SQL Server 2012 (also 2005, 2008) will not start and threw an error. Following are the steps to restore the master database from the latest backup.

(a)    Open the Command prompt with administrative privileges.

(b)   Start the SQL Server Service in a single-user mode as follows:

(1)    Default instance:

C:\>NET START MSSQLSERVER /m

(2)    Named Instance:

C:\>NET START MSSQL$InstanaceName /m

e.g. C:\>NET START MSSQL$HealthCanada01 /m

Note: Replace server name and instance name to match your case

(c)    Now start the sqlcmd from command prompt

(1)    Default instance:

C:\>sqlcmd

(2)    Named instance:

C:\>sqlcmd  -SYourServer\InstanceName

e.g. C:\>sqlcmd –SmyWinServer\HealthCanada01

Note: Replace server name and instance name to match your case

(d)   In the sqlcmd command window, run the following command:

>RESTORE DATABASE master FROM DISK=’H:\DBBackup\master_backup.BAK’ WITH REPLACE

>GO

Note: Replace path and backup file name to match your case

(e)   As soon as the restore is done, SQL Server service will be terminated. Now Restart the SQL Server service by using SQL Server Configuration Manager or by using the NET START command:

Figure # 1: Restoring the master database from the latest backup.

Rebuild the master database (figure # 2):

We may need to rebuild the master database if:

(a)    After installation, the collation setting needs to change.

(b)   The master database is corrupt and there is no backup.

Following are the step by step processes to rebuild the master database. In SQL 2008R2 and SQL 2012 there is no need for setup DVD/ISO. Please note that rebuilding the master database will create all three databases - master, model and msdb.

(a)    Open the Command prompt with administrative privileges.

(b)   Go to the following folder:

SQL Server 2008R2:

C:\> CD  C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2

SQL Server 2012:

C:\> CD  C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012

SQL Server 2005: See this article:

http://msdn.microsoft.com/en-us/library/ms144259(v=sql.90).aspx

SQL Server 2008: See this article

http://msdn.microsoft.com/en-us/library/dd207003(v=sql.100).aspx

(c)    Use the following command and fit your requirement as well:

Syntax:

setup.exe

/QUIET

/ACTION=REBUILDDATABASE

/INSTANCENAME=instance_name

/SQLSYSADMINACCOUNTS=domain\ accounts

[/SAPWD=new_password]        

[/SQLCOLLATION=new_collation_name]

To rebuild the default instance:

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=ARIBU\sqladmin /SAPWD=myPassw0rd

To rebuild the default instance to change collation:

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=ARIBU\sqladmin /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

To rebuild the named instance (HealthCanada01):

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME= HealthCanada01 /SQLSYSADMINACCOUNTS=ARIBU\sqladmin /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

To rebuild the named instance (HealthCanada01) with sa password and to change collation:

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME= HealthCanada01 /SQLSYSADMINACCOUNTS=ARIBU\sqladmin /SAPWD=myPassw0rd /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

(d)   Once the master database is built, we need to reconfigure various settings again, such as login, Linked server, Server wide settings, and so on. It is also a good idea to apply SQL Server patches after rebuilding the master database.  

Figure # 2: Rebuilding the master database.

In the above screenshot I ran the following command:

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=ARIBU\sqladmin /SAPWD=1amThe$a /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

Changing the location of master database:

In some situations, we need to start SQL Server Service by changing the location of the master database. This can be done in command prompts or from SQL Server Service Manager. Below is a screenshot (figure # 3) for starting a SQL Server 2012 Service when master database sits in a different location other than the default location.

Figure # 3: New location of master database.

Part Two: msdb and model:

Changing location of msdb or model database:

To move the model and msdb system database data or log file because of a hardware failure or in a planned location, follow these steps to relocate the file to a new location.

1.       Stop the instance of Microsoft SQL Server 2005/2008/208R2/2012 if it is started.

2.       Start the instance of Microsoft SQL Server Service in master-only recovery mode by entering one of the following commands at the command prompt.

For the default (MSSQLSERVER) instance, run the following command.

NET START MSSQLSERVER /f /T3608 /T4022

For a named instance, run the following command.

NET START MSSQL$instancename /f /T3608 /T4022

3.       For each file to be moved, use sqlcmd commands or SQL Server Management Studio to run the following statement.

ALTER DATABASE database_name  

MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

4.       Exit the sqlcmd utility or SQL Server Management Studio.

5.       Stop the instance of Microsoft SQL Server.

6.       Move the file or files to the new location.

7.       Restart the instance of Microsoft SQL Server. For example, run

NET START MSSQLSERVER or

NET START MSSQL$instancename

8.       Verify the file change by running the sp_helpfile.

After Moving All System Databases:

If we have moved all of the system databases to a new drive or volume or with a different drive letter/folder, we need to change the SQL Server Agent log path. If we do not update this path, SQL Server Agent will fail to start.

To change the SQL Server Agent Log Path:

1.     From SQL Server Management Studio, in Object Explorer, expand SQL Server Agent.

2.     Right-click Error Logs and click Configure.

3.     In the Configure SQL Server Agent Error Logs dialog box, specify the new location of the SQLAGENT.OUT file.

 

Example of relocating msdb and model database:

In this example, we will move the msdb and model database to a new location, which is “H:\SQLSystemDB”.  Prior to stop SQL Server Service collect the logical and OS file name (use sp_helpfile)

To execute the ALTER DATABASE command we can use sqlcmd.

For msdb:

ALTER DATABASE msdb

MODIFY FILE( NAME = MSDBData , FILENAME = 'H:\SQLSystemDB\MSDBData.mdf' )

ALTER DATABASE msdb

MODIFY FILE( NAME = MSDBLog , FILENAME = 'H:\SQLSystemDB\MSDBLog.ldf' )

GO

Figure # 4: msdb database relocation:

For model:

ALTER DATABASE model

MODIFY FILE( NAME = modeldev , FILENAME = 'H:\SQLSystemDB\model.mdf' )

ALTER DATABASE model

MODIFY FILE( NAME = modellog , FILENAME = 'H:\SQLSystemDB\modellog.ldf' )

GO

Figure # 5: model database relocation:

Restoring msdb and model database:

So far restoring the msdb and model database from backup is somehow misleading (??). Thus, a common trick is to restore the model and msdb database in a different instance of SQL Server which has same Edition/Build and then copy back the mdf and ldf to the folder.

Part three: tempdb startup issue:

Relocating tempdb is one of the simplest task and it can be done when SQL Server service is up and running. However, reallocating tempdb data files will not be in effect until the SQL Server Service restarts. But if we add data files to tempdb this will take effect as soon as the creation has been done.

SQL Server startup issue may occur due to following reasons:

1.       Drive failure of tempdb,

2.       Unavailability or change of folder/drive

Resolving tempdb issue:

To rectify the startup issue with SQL Server due to unavailability of tempdb, following are the steps

1.       Open the command prompt with administrative privilege

2.       Start the SQL Server service with following command

Default instance: NET START MSSQL SERVER /f /T3608

Named Instance: NET START MSSQL$InstanceName /f /T3608

3.       Open the sqlcmd command window

Default instance: C:\> sqlcmd

Named Instance: C:\> sqlcmd  -SComputername\InstanceName

4.       We can query the sys.master_files to get the logical name of each data file and corresponding physical name with location of each data file. Execute following command:

SELECT  name ,

        physical_name

FROM    sys.master_files

WHERE   database_id = 2

5.       In the sqlcmd window run the ALTER DATABASE command

Syntax:

ALTER DATABASE database_name

MODIFY FILE( NAME = logical_name , FILENAME = 'new_path\os_file_name' )

Example for tempdb (one data and one log):

ALTER DATABASE tempdb

MODIFY FILE( NAME = tempdev , FILENAME = 'H:\tempdb\tempdb.mdf' )

ALTER DATABASE tempdb

MODIFY FILE( NAME = templog , FILENAME = 'H:\tempdb\templog.ldf' )

GO

6.       Restart SQL Server as usual without any switch and trace flag.

Relocating tempdb data files:

Here are two examples for “how to move tempdb” to a different location.

Following is the example (figure # 6) to move tempdb from its default location “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA” to a new location “H:\tempdb” in SQL Server 2012.

Figure # 6: tempdb move to a new location

Following is the example (figure # 7) to move tempdb from the location “H:\tempdb” to a new location “H:\tempdbNew” in SQL Server 2012, where we have 4 data files and 1 log file.

Figure # 7: tempdb move to a new location (muiltiple data files)

Figure # 7A: tempdb move to a new location (muiltiple data files)

Figure # 7B: tempdb move to a new location (muiltiple data files)

Reference and Read More:
Move System Databases

http://msdn.microsoft.com/en-ca/library/ms345408.aspx

The SQL Server Instance That Will not Start (written by Gail Shaw)

https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/

Rebuild System Databases
http://msdn.microsoft.com/en-us/library/dd207003.aspx

Rebuilding master database:

SQL 2005: http://msdn.microsoft.com/en-us/library/ms144259(v=sql.90).aspx

SQL 2008: http://msdn.microsoft.com/en-us/library/dd207003(v=sql.100).aspx