Recovering a SQL Server Database from Suspect Mode

A couple of days back at I got a call from my support team informing me that one of our database located on the Production Server went into Suspect Mode. The version used was SQL Server 2005 Service Pack 3. Being a Production Database server, it was a Priority 1 incident and the expected time of resolution was 4 hours..

Solution:

The first step was to identify why this incident occured and after investigation it was found that it was due to the corruption of the transactional log file of the database.

I connected to SSMS using the sa login credentials and located the SUSPECT database:

I then reset the status of the SUSPECT Database by executing the below T-SQL query against the master database.

EXEC sp_resetstatus 'test_dr';

sp_resetstatus turns off the suspect flag on a database. This procedure updates the mode and status columns of the named database in sys.databases. Also note that only logins having sysadmin priveleges can perform this :

As you can see in the above screen capture, the T-SQL query gave the warning message upon execution:

You must recover this database prior to access

The next step was to set the SUSPECT database into an EMERGENCY mode. This was done by executing the below SQL query against the master database.

ALTER DATABASE test_dr SET EMERGENCY

Once the database is set to EMERGENCY mode it becomes a READ_ONLY copy and only members ofsysadmin fixed server roles have privileges to access it. The basic purpose for this is to facilitate troubleshooting. I did not want other users updating the database while it was being worked on.

As you can see from the above screen capture, once the T-SQL query got executed successfully the state of the database changed from SUSPECT to EMERGENCY.

Once the database state was changed to EMERGENCY. I then performrf a consistency check by executing the below T-SQL query against the master database.

DBCC checkdb('test_dr')

Which resulted in the below output:

As seen from the above screen capture there is no issue with respect to consistency of the test_dr database. Also, this confirmed that the logical and physical integrity of the database was intact.

The next step was to set the database to SINGLE USER mode with ROLLBACK IMMEDIATE. To do this the below SQL query was executed against the master database.

ALTER DATABASE
test_dr SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

The above query will rollback any transactions if any are present in the test_dr database and will bring the database named test_dr into Single User mode.

Please refer to the screen capture below:

The next step was to perform a DBCC Checkdb along with Repair with Data Loss by executing the below T-SQL query against the master database.

DBCC CheckDB ('test_dr', REPAIR_ALLOW_DATA_LOSS)

This query will attempt to repair all reported errors. These repairs can cause some data loss.

Once the DBCC CheckDB with the Repair with Data Loss option were executed, the Database went into Single User mode as shown below:

After performing the above step the database was brought ONLINE and Multiple Users access was enabled by executing the below T-SQL query against the master database.

ALTER DATABASE test_dr SET MULTI_USER

Please refer the screen capture below.

As you can see from the above screen capture the database named test_dr is back ONLINE. I am even able to view its objects as shown below:

As final step for safety, I again checked the consistency of the database which was just repaired and brought ONLINE (i.e. the test_dr database) by executing the below T-SQL query against the master database.

 DBCC CheckDB ('test_dr')

After performing the above steps I ensured that all the required logins had access to the database with proper privileges. The application started working fine and the business was back on track. It took just 38 minutes to bring the SUSPECT database back ONLINE.

Please let me know if you have any comments on this approach or alternative approaches you have used in the past.

Find out who modified an object in SQL Server

SELECT I.*

FROM

sys.traces T

CROSS Apply ::fn_trace_gettable(T.path, T.max_files) I

Join sys.trace_events E On I.eventclass = E.trace_event_id

Where T.id = 1 And

E.name = 'Object:Altered' and ObjectName like '%UAC_AdminAccess%'

SQL Server Log Shipping -- Complete Overview.. Part - VII

Next Chapter »

Scheduling the Log Backup Job

Once this multi-step job is created, the next step is to schedule it. Scheduling this job is more complicated that scheduling the database backup job. There are two key decisions you will have to make. First, you must decide how often you want to do a log backup. Generally, the more often, the better, as less data is subject to loss. I generally perform a log backup every 15 minutes. Another way of saying this is that I am doing log shipping every 15 minutes.

The second aspect of scheduling is to prevent your scheduled log backups from interfering with your database backups. If they get out of sync with each other, the transaction log restores will fail, and you won’t have a current backup of your database on your standby server. To prevent any potential conflict, what I do is schedule the log backup job to stop 15 minutes before the database backup begins, and then to restart 15 minutes after the database backup is run. This way, there should be no scheduling conflicts.

Test, Test, and Test Some More

We are almost done. While all the steps are now in place, the last step is to test everything over a period of several days to be sure the jobs and stored procedures work properly. Since each job keeps its own success and failure history, it is not to hard to debug any job-related problems you run across.

Before you add log shipping to your production server, I suggest you set it up and test it first on two non-production SQL Servers if you can. This will prevent any of your learning mistakes to bring down your production server.

Don’t think that a successful log shipping solution eliminates the need for storing backups to tape. Besides everything I have described in this article, I still back up all of my database and log disk backups to tape on a daily basis. There is no such thing as having too many backups.

Monitor Everything

Once you have your log shipping process in place, be sure to monitor it for success. Be sure to set up job notifications so that you are notified by e-mail or pager if either of your two log shipping jobs fail.

How to Failover to the Standby Server

Once log shipping is successfully implemented, you will want to create your own scripts to implement an actual failover to the standby server, and then you will want to test your process to see that it works successfully.

Since I am not familiar with your specific environment, I cannot provide you an exact set of steps to follow when failing over your production server to your standby server, but what I can do is list some of the most common things you will need to consider. They include:

• Are you able to recover the transaction log on the production server? If so, then you may want to run a script similar to this one in order to back it up:

             BACKUP LOG database_name TO database_name_log_backup_device WITH INIT,
             NO_TRUNCATE

• If you have recovered the transaction log on the production server, you may want to restore in on the standby server using a script similar to this one:

             RESTORE LOG database_name
             FROM DISK = ‘d:sql7logdatabase_name_log_backup_device.bak’
             WITH
             DBO_ONLY,
             STANDBY = ‘d:sql7undo_database_name_log.ldf’

• Whether or not you were able to recover the transaction log or not from the production server, you will need to run a script similar to the one below in order to “recover” the standby server and turn it into your production server.

             RESTORE DATABASE database_name WITH RECOVERY
        EXEC SP_DBOPTION ‘database_name’, ‘read only’, ‘false’
        EXEC SP_DBOPTION ‘database_name’, ‘dbo use only’, ‘false’

• Once your standby server has been failed over, the next step is to re-point your clients to it. How you do this will depend on your clients. As a last resort, you can always rename your standby server to the name of your production server, and change the IP address, but this solution often creates problems of its own. You will just have to test to find the best way to re-point your clients to your SQL Server. 
• Another aspect of the failover process you will want to plan for is how you will make the move back from the standby server to the production server once the production server is working again.

As you can see, setting up log shipping involves a lot of detailed steps. If you want to give it a try, try to allocate the largest part of a couple of days to implement and test it. But once it is going, I think you will find it a great time saver when your production server goes down.

SQL Server Log Shipping -- Complete Overview.. Part- VI

Next Chapter »

Step 4 Script

EXEC standby_server_name.master.dbo.restore_database_backups

This single line is used to fire the restore_database_backups stored procedure on the standby server. Note that I have used a fully qualified object name for the stored procedure. You will want to do the same.

Run this script from the Master database.

Scheduling the Database Backup Job

Once this multi-step job is created, the next step is to schedule it. It is important that this job only be scheduled to run once a day. If it does not, it will get out of synch with the transaction log backups, discussed in the next section. Select a time to run it when it will produce the least affect on your users.

The Transaction Log Backup Job

Now its time to look at the second job, the job used to backup the transaction log, move the log backup to the standby server, and then fire the stored procedure used to restore the transaction log. It consists of the three steps outlined in this table:

Step ID	Step Name	Step Type	On Success	On Failure
1	Backup Log	T-SQL Script	Goto Next Step	Quit With Failure
2	Copy Log	Operating System Command	Goto Next Step	Quit With Failure
3	Restore Log	T-SQL Script	Quit With Success	Quit With Failure

Let’s first talk about each step in general, then we will discuss each one in depth.

Step 1 is to back up the transaction log onto its backup device. Note that if this step fails that we “Quit With Failure”. No point in copying a failed backup.

Step 2 is copy the backup device from the production server to the standby server using the XCOPY command.

Step 3 fires the stored procedure on the standby server that is used to restore the transaction log on the standby server.

Now that we have a look at the big picture, lets let at the script behind each of these steps.

Step 1 Script

BACKUP LOG database_name TO log_backup_device WITH INIT, NO_TRUNCATE

WAITFOR DELAY ’00:00:05′

Where database_name is the name of your database, and log_backup_device is the name of the backup device used for the log backup.

The “BACKUP LOG” command does a transaction log backup of the database to the named backup device. The “WITH INIT” option is used to delete any previous database backups from the backup device before the backup is made. The “NO_TRUNCATE” option is used to tell the “BACKUP LOG” command not to truncate the log, which is the default behavior of this command. We don’t want to truncate the log each time we do a transaction log backup because we might need it for some other reason later.

The “WAITFOR” line is used to give the previous time to complete before the next step in the job executes.

Run this script from the Master database.

Step 2 Script

xcopy g:mssql7backupbackup_device_name.bak   \standby_server_nameg$mssql7backup /c

For this operating system command to work, the account used to run this job must have local administrative rights on both the production and standby servers. You will of course have to substitute your paths and file names. The “g$” refers to the local administrative share on the standby server where the backup device is to be copied. The “/c” option tells the command to continue copying even if an error of any kind occurs.

Step 3 Script

EXEC standby_server_name.master.dbo.restore_log_backups

This single line is used to fire the restore_log_backups stored procedure on the standby server. Note that I have used a fully qualified object name for the stored procedure. You will want to do the same.

Run this script from the Master database.

SQL Server Log Shipping -- Complete Overview.. Paert - V

Next Chapter »

Create the Backup Jobs

Now that we have learned how to restore our databases and transaction logs, its about time we learn how to back them up, move them from the production server to the standby server, and then fire a remote stored procedure on the standby server to perform the necessary restore.

This is accomplished using two multi-step SQL Server jobs. One job, as you might expect, if for backing up the database, and the second job is for backing up the transaction logs.

The Database Backup Job

Let’s begin our look at these jobs with the job used to backup the database on the production server, move it to the standby server, and then fire the stored procedure on the standby server to perform the database restore. It consists of the four steps outlined in this table:

Step ID	Step Name	Step Type	On Success	On Failure
1	Truncate Log	T-SQL Script	Goto Next Step	Goto Next Step
2	Backup Database	T-SQL Script	Goto Next Step	Quit With Failure
3	Copy Backup	Operating System Command	Goto Next Step	Quit With Failure
4	Restore Database	T-SQL Script	Quit With Success	Quit With Failure

Let’s first talk about each step in general, then we will discuss each one in depth.

Step 1 is to truncate the transaction log. Why do we want to do this? The reason for this is that when we perform the transaction log backups (to be discussed in detail later), we won’t at that time truncate the log. Instead, we will do it here. If we don’t do it here, and we don’t truncate the log when we back up the log, then it will grow forever. Even if this step of the job should fail, we still want to backup the database. That’s why I specify “Goto Next Step” if this step should fail.

Step 2 is to backup the database onto its backup device. Note that if this step in the job fails that we “Quit With Failure”. No point in copying a failed backup.

Step 3 is copy the backup device from the production server to the standby server using the XCOPY command.

Step 4 fires the stored procedure on the standby server that is used to restore the database on the standby server.

Now that we have a look at the big picture, lets let at the script behind each of these steps.

Step 1 Script

BACKUP LOG database_name WITH TRUNCATE_ONLY

WAITFOR DELAY ’00:00:05′

Where database_name is the name of your database.

The “BACKUP LOG” line is used to truncate the transaction log of the database.

The “WAITFOR” line is used to give the previous step time to complete before the next step in the job executes.

Run this script from the Master database.

Step 2 Script

BACKUP DATABASE database_name TO database_name_backup_device WITH INIT

WAITFOR DELAY ’00:00:05′

Where database_name is the name of your database, and database_name_backup_device is the name of the backup device used for the database backup.

The “BACKUP DATABASE” does a full backup of the database to the named backup device. The “WITH INIT” option is used to delete any previous database backups from the backup device before the backup is made.

The “WAITFOR” line is used to give the previous step time to complete before the next step in the job executes.

Run this script from the Master database.

Step 3 Script

xcopy g:mssql7backupbackup_device_name.bak   \standby_server_nameg$mssql7backup /c

For this operating system command to work, the account used to run this job must have local administrative rights on both the production and standby servers. You will of course have to substitute your paths and file names. The “g$” refers to the local administrative share on the standby server where the backup device is to be copied. The “/c” option tells the command to continue copying even if an error of any kind occurs.