How to Rebuild SQL Server Database After a Failure

Overview

SQL Server plays an integral part of its users who creates their own database where they can store and retrieve data whenever required. Sometimes, the user may face issue while trying to access their database due to crash of SQL Server. To overcome such issue and to make the database accessible, we need to be able to make the database run on another physical server. For this, we can recreate the entire SQL Server environment on an existing server instead of building a new server and installing SQL again.

Things to keep in mind before Rebuild SQL Server Database

• In order to recreate the SQL environment, we will use an existing SQL Server that is already setup.
• Since the process involves restoring the master, msdb and user databases, we need to make sure that both the source and target SQL Servers must be on the exact service pack.
• If the Source and Target environment are not similar, the restore of master database on the target server will not be possible.
• To check the version of the target environment, use command ‘Select @@version’ in SSMS.
• For version of Source environment, we can use a master backup file to check the SoftwareVersionMajor, SoftwareVersionMinor and SoftwareVersionBuild using the command ‘RESTORE HEADERONLY FROM DISK ='F:\Backup\master_backup_2015_10_9_000001_7422453.BAK'
• Ensure that both the environments have the same version before beginning the rebuild process on the target server.
• As an example for this page, we will use SQL 2008 SP2 (10.0.5000) for both source and destination.
• In Source- master, model and msdb databases are located on drive D while tempdb is on a dedicated drive E with initial size of 10GB.
• In Target- server has only one drive C without enough space to create 10GB file for tempdb.

Rebuilding SQL Server Database after a Failure

Here are the steps that need to be followed for rebuilding SQL Server database after a Failure:

1. Shutdown the SQL Services like SQL Server, Agent, SSIS, etc. on the target SQL Server using SQL Server Configuration Manager.

2. Go to command window by typing cmd in the search box. Use the command
   ‘sqlservr -m’
3. Open a new command prompt to start SQLCMD and use the following command to restore the master database:

4. The instance of SQL Server, which was started in single user mode stops after the completion of master database restore. While trying to run ‘sqlservr -m’ again, SQL Server will not start as master database has the reference that model and msdb are on drive D and tempdb is on E drive. Since the target server does not contain Drives D and E, we will use the following command:
   ‘sqlservr –c –m –f –T3608’
   Where -c >> Starting SQL not as a service
   -f >> starting with minimum configuration
   -T3608 >>trace flag 3608 will skip automatic recovery of all databases except master database
5. We can change the location of the model and tempdb by using below commands after starting SQLCMD. We can also resize tempdb as well.

6. We will stop the SQL Server from the CMD window by either closing it directly or using CTRL+BREAK. Restart the SQL Server with the help of SQL Server Configuration Manager.

7. We will run the following command either on SSMS or SQLCMD to restore msdb database

8. In the end, we will restore the user databases and start the SQL Server Agent.

Conclusion

In the blog, we have been discussing ways on how to rebuild SQL Server database after a failure using existing server instead of creating a new one. After restoring the master and msdb databases, we were able to recover all the system information such as logins, schedules, jobs etc. without the need to recreate them. This method saves time since we do not need to build the entire server and install SQL Server all over again. However, if this methods fails to rebuild SQL Server Database then you can also take the help of third party utility like SysTools SQL Recovery

MS Build 2016 Live Streaming

Know How to Manage SQL Server Error Logs

The SQL Server error log file contains an account of the entire information generated in SQL Server. It contains warnings, informational messages, and information about critical events that take place in SQL Server. Furthermore, important information, like success and failure of logon events user-generated messages also get stored in the SQL Server error log. Since it is a valuable data point for the SQL Server administrators, managing the size of the error logs is the most important thing that needs to be done.

Every time, the SQL Server instance is started, the error log is initialized. In case the SQL Server instance has not been stated from a very long time, the error log can grow to a very large size. However, management of SQL Server error logs helps in maintaining the size of error log under control.

In this write-up, we will discuss some of these methods by which an administrator can manage SQL Server error logs effectively.

Methods for Managing SQL Server Error Logs

There are many methods, which can help the administrators to manage the SQL server error logs effectively. Some of them are:

1. Reinitialize SQL Server Error Logs

In order to reinitialize the server, the stored procedure sp_cycle_errorlog can be used. This stored procedure closes current error log and like a server restart, cycles error log extension. The new error log file will contain the version and copyright information and a line that will indicate that a new log is created.

Each time, SQL Server is started, the error log file is named to errorlog.1, and then errorlog.1 becomes errorlog.2 and so on. The syntax sp_cycle_errorlog cycles the error log files and that too without stopping and starting the server.

2. Increase Number Of Error Logs

The second method by which an administrator can effectually manage SQL Server error log files is by increasing the number of error log files for a specific instance. There are seven error logs by default in SQL Server, which are stored in the log folder. For better management of error logs, the user might want to create more than seven log files. This can be done by adding a registry key in the registry editor. Before making any changes in the registry, it is advised to create a backup of the entire data.

Browse to the following location in registry editor.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer

Create a key with the name NumErrorLogs of value REG_DWORD type to it. Double click on it and change its value data to the number of extra error logs you require in SQL Server.

3. Limiting Error Log Size

Limiting the size of error log file is another method with the help of which the SQL Server error logs can be maintained. In SQL Server 2012 and subsequent versions, following mentioned script can be run to manage the size of an individual error log file.

Conclusion

With the help of the above-mentioned parameters, a database administrator can easily manage SQL Server the error log files.