Restoring SQL 2008 Backups

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?
LVL 2
itniflAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
AlokJain0412Connect With a Mentor Commented:
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
 
VENKAT KOKULLAConnect With a Mentor SQL Server DBACommented:

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
 
itniflAuthor Commented:
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
 
itniflAuthor Commented:
If I have differential backup also, I wpuld restore first fill, then differential, then transaction log backups.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.