Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Restoring SQL 2008 Backups

Posted on 2011-09-23
4
Medium Priority
?
348 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 1000 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 1000 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

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

782 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