Backup and restore - 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 2005 Forums

SQL Server Administration (2005)

Backup and restore

Author

Topic

ilimax
Posting Yak Master

164 Posts

Posted - 2010-01-28 : 23:16:17

I am not clear with backups ...
There is backup each day, 7 days a week into 7 separate backup files.
Each file has full backup made at 12:15am .... then append log backup run every 30 minutes...last log backup run at 11:45pm

1.If I restore Tuesday.bak full backup only to new database, I will have all Monday's records there
2.If I restore Monday.bak (one full backup and all log backups) to new database I will have all Mondays records there till 11:45?

I am not clear with number 2 .... ????

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-29 : 00:40:51

1) Yes
2) Yes

Backups backup the database as it it at the time it is backed up. A restore restores it exactly.
Log backups contain all the transactions that happened between that backup and the one before. Restoring them applies those transactions against the DB.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 03:59:44

Interesting. If you append TLog backups to the same file as Full Backup can you restore them all in a single command?

However, I would be nervous of that in case the Full Backup is corrupted such that the TLog backups can't be "reached".

@ilimax: Note that you can restore a Full backup from ages ago, and ALL Tlog backups since, in order, to get back to any later point in time upto, and including, the last TLog backup that you have. You don't have to start with the most recent full backup.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-29 : 07:48:28

quote:
Originally posted by Kristen

Interesting. If you append TLog backups to the same file as Full Backup can you restore them all in a single command?

Not as far as I'm aware.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 09:37:19

OK, no benefit then. I remain in the camp of "best to put each backup in a separate file" then