Solved

SQL Database Recovery

Posted on 2014-04-03
13
426 Views
Last Modified: 2014-04-07
Restart the SQL Server and a database is marked as "recovering". Any idea ?

Tks
0
Comment
Question by:AXISHK
  • 6
  • 5
  • 2
13 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 39977043
0
 

Author Comment

by:AXISHK
ID: 39977069
What should I do on SQL Server next time before rebooting the server, I mean a proper way to shut down / reboot the server.

How to start server in recovery log ? Tks

Tks
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 39977169
It should have shut down gracefully.  Not sure why it happens.  (unless someone forced the shutdown?)

To restore the log:
http://technet.microsoft.com/en-us/library/ms177446.aspx

To restore a transaction log backup


1.After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.


2.Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.


3.Right-click the database, point to Tasks, point to Restore, and then click Transaction Log, which opens the Restore Transaction Log dialog box.



Note Note


If Transaction Log is grayed out, you may need to restore a full or differential backup first. Use the Database backup dialog box.
 


4.On the General page, in the Database list box, select the name of a database. Only databases in the restoring state are listed.


5.To specify the source and location of the backup sets to restore, click one of the following options:

¿ From previous backups of database

Select the database to restore from the drop-down list. The list contains only databases that have been backed up according to the msdb backup history.


¿ From file or tape

Click the browse (...) button to open the Select backup devices dialog box. In the Backup media type box, select one of the listed device types. To select one or more devices for the Backup media box, click Add.

After you add the devices you want to the Backup media list box, click OK to return to the General page.



6.In the Select the transaction log backups to restore grid, select the backups to restore. This grid lists the transaction log backups available for the selected database. A log backup is available only if its First LSN greater than the Last LSN of the database. Log backups are listed in the order of the log sequence numbers (LSN) they contain, and they must be restored in this order.

The following table lists the column headers of the grid and describes their values.






Header
 

Value
 

Restore  
 
Selected check boxes indicate the backup sets to be restored.
 

Name
 
Name of the backup set.
 

Component
 
Backed-up component: Database, File, or <blank> (for transaction logs).
 

Database
 
Name of the database involved in the backup operation.
 

Start Date
 
Date and time when the backup operation began, presented in the regional setting of the client.
 

Finish Date
 
Date and time when the backup operation finished, presented in the regional setting of the client.
 

First LSN
 
Log sequence number of the first transaction in the backup set. Blank for file backups.
 

Last LSN
 
Log sequence number of the last transaction in the backup set. Blank for file backups.
 

Checkpoint LSN
 
Log sequence number of the most recent checkpoint at the time the backup was created.
 

Full LSN  
 
Log sequence number of the most recent full database backup.
 

Server
 
Name of the Database Engine instance that performed the backup operation.
 

User Name
 
Name of the user who performed the backup operation.
 

Size
 
Size of the backup set in bytes.
 

Position
 
Position of the backup set in the volume.
 

Expiration
 
Date and time the backup set expires.
 


7.Select one of the following:

¿ Point in time

Either retain the default (Most recent possible) or select a specific date and time by clicking the browse button, which opens the Point in Time Restore dialog box.


¿ Marked transaction

Restore the database to a previously marked transaction. Selecting this option launches the Select Marked Transaction dialog box, which displays a grid listing the marked transactions available in the selected transaction log backups.

By default, the restore is up to, but excluding, the marked transaction. To restore the marked transaction also, select Include marked transaction.

The following table lists the column headers of the grid and describes their values.






Header
 

Value
 

<blank>
 
Displays a checkbox for selecting the mark.
 

Transaction Mark
 
Name of the marked transaction specified by the user when the transaction was committed.
 

Date
 
Date and time of the transaction when it was committed. Transaction date and time are displayed as recorded in the msdb gmarkhistory table, not in the client computer's date and time.
 

Description
 
Description of marked transaction specified by the user when the transaction was committed (if any).
 

LSN
 
Log sequence number of the marked transaction.
 

Database
 
Name of the database where the marked transaction was committed.
 

User Name
 
Name of the database user who committed the marked transaction.
 



8.To view or select the advanced options, click Options in the Select a page pane.


9.In the Restore options section, the choices are:

¿ Preserve the replication settings (WITH KEEP_REPLICATION)

Preserves the replication settings when restoring a published database to a server other than the server where the database was created.

This option is available only with the Leave the database ready for use by rolling back the uncommitted transactions... option (described later), which is equivalent to restoring a backup with the RECOVERY option.

Checking this option is equivalent to using the KEEP_REPLICATION option in a Transact-SQL RESTORE statement.


¿ Prompt before restoring each backup

Before restoring each backup set (after the first), this option brings up the Continue with Restore dialog box, which asks you to indicate whether you want to continue the restore sequence. This dialog displays the name of the next media set (if available), the backup set name, and backup set description.

This option is particularly useful when you must swap tapes for different media sets. For example, you can use it when the server has only one tape device. Wait until you are ready to proceed before clicking OK.

Clicking No leaves the database in the restoring state. At your convenience, you can continue the restore sequence after the last restore that completed. If the next backup is a data or differential backup, use the Restore Database task again. If the next backup is a log backup, use the Restore Transaction Log task.


¿ Restrict access to the restored database (WITH RESTRICTED_USER)

Makes the restored database available only to the members of db_owner, dbcreator, or sysadmin.

Checking this option is synonymous to using the RESTRICTED_USER option in a Transact-SQL RESTORE statement.



10.For the Recovery state options, specify the state of the database after the restore operation.

¿ Leave the database ready for use by rolling back uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)

Recovers the database. This option is equivalent to the RECOVERY option in a Transact-SQL RESTORE statement.

Choose this option only if you have no log files you want to restore.


¿ Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)

Leaves the database unrecovered, in the RESTORING state. This option is equivalent to using the NORECOVERY option in a Transact-SQL RESTORE statement.

When you choose this option, the Preserve replication settings option is unavailable.



Important note Important


For a mirror or secondary database, always select this option.
 


¿ Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a file so that recovery effects can be reversed. (RESTORE WITH STANDBY)

Leaves the database in a standby state. This option is equivalent to using the STANDBY option in a Transact-SQL RESTORE statement.

Choosing this option requires that you specify a standby file.



11.Optionally, specify a standby file name in the Standby file text box. This option is required if you leave the database in read-only mode. You can browse for the standby file or type its pathname in the text box.


0
 

Author Comment

by:AXISHK
ID: 39977324
We just reboot the SQL server and when it comes up, the database states in "in Recovery".
Is there a way to ensure all transactions have been either commit to database or rollback before shuting down.

Tks
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 39978421
you can run the shutdown command on the sql server itself:
http://technet.microsoft.com/en-us/library/ms188767(v=sql.90).aspx
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 39978474
If you did a normal Windows shutdown, SQL Server should have done a normal shutdown as well, including running CHECKPOINT on all databases.

If you have only one database that is an issue, just make sure you run a CHECKPOINT command on that database before you reboot the server.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:AXISHK
ID: 39979508
So, in normal case, I don't need to issue "CHECKPOINT" on the database before shuting down the Window  ? tks
0
 

Author Comment

by:AXISHK
ID: 39979573
One more check, how check whether log file has been committed before I reboot the server ?

Tks
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 39979575
You don't need to run the checkpoint command, that's only when you're in recovery.  If you run the shutdown command first, when the sql server stops all transactions will have been committed.
0
 

Author Comment

by:AXISHK
ID: 39979581
So, issue shutdown for SQL query and wait SQL server to shutdown. Afterwards, shutdown the WIndow server, correct ?
0
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 250 total points
ID: 39979746
According to what Scott said if you issue the windows shutdown SQL should shutdown gracefully on its own.  Maybe someone forced it because of a long running transaction.

To be absolutely safe you can run the shutdown command on SQL first, wait for it to ensure it's shutdown, and then shutdown the machine.
0
 

Author Closing Comment

by:AXISHK
ID: 39979958
tks.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39983124
Normally you would not need to issue a CHECKPOINT yourself.  However, if you know you're going to shut down the server abruptly, it won't hurt for you to issue your own CHECKPOINT on the main database to make sure that that db gets fully committed to disk at that point.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now