Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Restoring SQL 2008 Backups

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

670 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