Restoring Backup with T -log - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Restoring Backup with T -log

Author

Topic

poratips
Posting Yak Master

105 Posts

Posted - 2013-05-22 : 00:00:00

Hi,
I will be doing first time restore from Full backup, Differential and Transaction Log back up, what will be the ideal procedure I have to follow?
We are taking weekly Full backup, Daily Differential and every hour T-Log back up into Sql server 2008.

Thanks,

chadmat
The Chadinator

1974 Posts

Posted - 2013-05-22 : 03:28:21

Restore latest Full, Latest Diff (Prior to the point in time you want to restore to), then every TLog backup after that Diff, up to the point in time you want to restore to.

-Chad

poratips
Posting Yak Master

105 Posts

Posted - 2013-05-27 : 16:49:51

Thanks Chad.
So If I want to Restore up to Wednesday 11 AM then Full back up from Weekend, Tuesday night's Differential back up and after differential back up time, all the TLog back up to 11 AM, Right?
We normally taking back up - Full Back up weekend, Every night Diff Back up and Every hour T -Log back up

ksr39
Posting Yak Master

193 Posts

Posted - 2013-05-27 : 23:15:18

Yeah thats correct but you need to restore the full, diff backup's with no recovery and all the t-logs last but one with no-recovery and the last with recovery then your database is back as you were expecting.

Thank You All In Advance
KRS39

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-05-28 : 01:31:57

Use the backupset and backupmediafamily tables in msdb to view the backup history of the database. That way you can request the relevant files fro restore
http://www.sqlserver-dba.com/2012/06/display-sql-backup-history-for-a-single-database.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

poratips
Posting Yak Master

105 Posts

Posted - 2013-06-17 : 00:23:18

Thanks KRS.
You mean:

1)Restore the full and diff backup's with NO RECOVERY
2) All the T-Logs with no-recovery except the last T-Log which will be with recovery , Right?

Thanks

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-06-18 : 15:21:18

That's the way to do it - only with recovery on the last log file

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources