Solved

Restoring SQL 2008 Backups

Posted on 2011-09-23
4
337 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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…

617 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