Solved

Restoring SQL 2008 Backups

Posted on 2011-09-23
4
299 Views
Last Modified: 2012-06-27
Lets say I have a full backup every week, two differential backups every day and then transaction log backups every 10 minutes. When I then want to restore up to a given point in time, I would restore the full backup, then the differential backups and then all the transaction log backups? Or is it just the full backup and then all the transaction log backups?
0
Comment
Question by:itnifl
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
AlokJain0412 earned 250 total points
ID: 36592303
Order for restore are correct But u have to consider few more things
Before restoring

Make sure you are using the correct backup set.

RESTORE LABELONLY: summarizes information about the backup set.

RESTORE HEADERONLY: returns information about each item in the backup set including the order in
which the backups were made.
RESTORE FILELISTONLY: returns the associated data files in relation to a specific backup device.


--
1 Restore a full backup.
2.Restoring a differential.
The WITH RECOVERY/NORECOVERY clause.
3.  Files must be restored in order.
Provides the ability to restore to a point in time
Provides the ability to restore to a marked transaction
Allows user to restore to a point before a failure or corrupt data
Allows user to restore without unwanted data

--
STOPATMARK: Transactions can be ‘marked’ so that a restore can take you up to that marked transaction.

STOPBEFOREMARK: Restores can also restore up to but not including that mark.



Cheer
0
 
LVL 5

Assisted Solution

by:VENKAT KOKULLA
VENKAT KOKULLA earned 250 total points
ID: 36890157

The above mentioned order is correct, please find the below step by step order or restoring the backups FYI.....

Restore Full backup
1.Open the Restore Database window in Microsoft SQL Server Management Studio
2.Ensure the To database field is filled in with the name you want.
3.Choose From device as the Source for restore.

4.Choose the full backup file you want to restore. In most cases this is the most recent full backup file.

5.Click the Options page on the left navigation.
6.Choose Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored.(RESTORE WITH NORECOVERY). This is the most important step!!!

Restore Differential backup

1.Open the Restore Database window in Microsoft SQL Server Management Studio
2.Ensure the To database field is filled in with the name you want. The same that you specified in step 2 for the Restore Full backup

3.Choose From device as the Source for restore.

4.Choose the differential backup file you want to restore. In most cases this is the most recent differential backup file.

5.Click the Options page on the left navigation.
6.Choose the default: Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored.(RESTORE WITH RECOVERY) Make sure to choose this if you want to use your database after the restore.

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, and then click Restore.

4.Click Transaction Log, which opens the Restore Transaction Log dialog box.

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

6.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 name of the database whose log backups you want to restore.

From file or tape

Click the browse button to select one or more files or tapes as the source for the transaction log backups.

Selecting a file or tape opens the Specify Backup dialog box. In the Backup media list box, select one of the listed device types. To select one or more devices for the Backup location list box, click Add. After adding the devices you want to the list box, click OK to return to the General page.

7.In the Select the backup sets 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 is 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.

0
 
LVL 2

Author Comment

by:itnifl
ID: 36895727
So, I can both restore:
1. full, then differential, then transactional
2. full, then transactional

in this scenario?
When would I choose one over the other?
0
 
LVL 2

Author Closing Comment

by:itnifl
ID: 36918990
If I have differential backup also, I wpuld restore first fill, then differential, then transaction log backups.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

744 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

13 Experts available now in Live!

Get 1:1 Help Now