Confuse on 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 2000 Forums

SQL Server Administration (2000)

Confuse on Backup and Restore

Author

Topic

JeBoy
Starting Member

7 Posts

Posted - 2007-08-23 : 22:05:24

I am quite confuse my backup schedule runs like this:

Full backup - 12:00 AM
Differential - 12:00 NN
Log - every Hour

Time Backup File
12 AM LOG FULL
1 AM LOG
2 AM LOG
3 AM LOG
4 AM LOG
5 AM LOG
6 AM LOG
7 AM LOG
8 AM LOG
9 AM LOG
10 AM LOG
11 AM LOG
12 NN LOG DIFF
1 PM LOG
2 PM LOG
3 PM LOG
4 PM LOG
5 PM LOG
6 PM LOG
7 PM LOG
8 PM LOG
9 PM LOG
10 PM LOG
11 PM LOG

To restore, can I
a) Restore "FULL" plus all the transaction Log from 1AM to 11PM?
b) Restore "FULL" plus "DIFF" and transaction Log from 1PM to 11PM?

When I restore the logs I got this errors:

Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN <900001>, which is too early to apply to the database. A more recent log backup that includes LSN <500001> can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN <400001>, which is too late to apply to the database. An earlier log backup that includes LSN <100001> can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Any inputs from you guys..

Thank you!
JeBoy

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-23 : 23:33:59

Depends on what time you want to recover the db to, use either procedure a or b. How did you restore db to get that error?

moraes
Starting Member

3 Posts

Posted - 2007-08-28 : 09:44:38

Are you executing any maintenance procedure between Full and Diff backups? Like backups to disk or something else?

Marcelo Moraes
Exchange/SQL/E-Commerce

Das ist nicht mein bier!

Kristen
Test

22859 Posts

Posted - 2007-08-28 : 10:30:50

(a) and (b) are correct.

You could add (c) to restore the FULL (plus a later DIFF made before the next FULL) and then ALL the Logs thereafter. That is useful if the FULL you want to restore is damaged / missing.

See also:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How%20Backup%20works,What%20type%20of%20backup%20should%20I%20use,Automating%20Backups,Backup,RESTORE%20syntax%20/%20example,Restore%20Full%20and%20all%20TLogs%20backups,Restore

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources