Problem with backups and resotre on AdventureWorks

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

General SQL Server Forums

New to SQL Server Administration

Problem with backups and resotre on AdventureWorks

Author

Topic

petey84
Starting Member

14 Posts

Posted - 2010-11-02 : 15:48:58

Hi,

I have a issue with backups and restores I am doing on AdventureWorks in sql server 2005.

I use the following SQL code to take FUll, differential and Log backups:

BACKUP DATABASE AdventureWorks TO DISK = 'E:\TEST\AW.BAK'
BACKUP log AdventureWorks TO DISK = 'E:\TEST\AW1.TRN'
BACKUP DATABASE AdventureWorks TO DISK = 'E:\TEST\AWDIFF1.BAK' WITH DIFFERENTIAL
BACKUP log AdventureWorks TO DISK = 'E:\TEST\AW2.TRN'

and then I use the following to restore the database:

BACKUP LOG AdventureWorks TO DISK = 'E:\TEST\AWTAIL.TRN'

RESTORE DATABASE AdventureWorks FROM DISK = 'E:\TEST\AW.BAK' WITH NORECOVERY
RESTORE DATABASE AdventureWorks FROM DISK = 'E:\TEST\AWDIFF1.BAK' WITH NORECOVERY
RESTORE LOG AdventureWorks FROM DISK = 'E:\TEST\AW2.TRN' WITH NORECOVERY
RESTORE LOG AdventureWorks FROM DISK = 'E:\TEST\AWTAIL.TRN' WITH RECOVERY

the first restore is teh full backup and that seems to work but when I try the others I get the following error code:

Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

When I run the following SQL command:

SELECT
DATABASE_NAME AS DBNAME,
TYPE,
FIRST_LSN,
DATABASE_BACKUP_LSN,
RECOVERY_MODEL
FROM MSDB.DBO.BACKUPSET
WHERE DATABASE_NAME = 'ADVENTUREWORKS'

I get the following result:

AdventureWorks D 54000000512800238 0 SIMPLE
AdventureWorks D 54000000532000079 54000000512800238 FULL
AdventureWorks L 54000000532000079 54000000532000079 FULL
AdventureWorks L 54000000538400001 54000000532000079 FULL
AdventureWorks I 54000000540000070 54000000532000079 FULL
AdventureWorks L 54000000539200001 54000000532000079 FULL
AdventureWorks F 54000000567200170 54000000532000079 FULL
AdventureWorks F 54000000578400035 54000000532000079 FULL
AdventureWorks L 54000000544800001 54000000532000079 FULL
AdventureWorks F 54000000586400067 54000000532000079 FULL
AdventureWorks F 54000000593600035 54000000532000079 FULL
AdventureWorks L 54000000584000001 54000000532000079 FULL
AdventureWorks G 54000000599200038 54000000532000079 FULL
AdventureWorks L 54000000598400001 54000000532000079 FULL
AdventureWorks L 54000000602400001 54000000532000079 FULL
AdventureWorks L 54000000602400001 54000000532000079 FULL
AdventureWorks D 54000000527600064 54000000512800238 SIMPLE
AdventureWorks D 54000000531800079 54000000527600064 FULL
AdventureWorks L 54000000531800079 54000000531800079 FULL
AdventureWorks I 54000000536100070 54000000531800079 FULL
AdventureWorks L 54000000535900001 54000000531800079 FULL
AdventureWorks F 54000000544500170 54000000531800079 FULL
AdventureWorks F 54000000552300035 54000000531800079 FULL
AdventureWorks L 54000000539300001 54000000531800079 FULL
AdventureWorks G 54000000555400040 54000000531800079 FULL
AdventureWorks L 54000000555100001 54000000531800079 FULL
AdventureWorks L 54000000557100001 54000000531800079 FULL
AdventureWorks L 54000000557100001 54000000531800079 FULL
AdventureWorks L 54000000534900001 54000000531800079 FULL
AdventureWorks D 54000000526600064 54000000512800238 SIMPLE
AdventureWorks D 54000000530800079 54000000526600064 FULL
AdventureWorks L 54000000530800079 54000000530800079 FULL
AdventureWorks I 54000000535100070 54000000530800079 FULL
AdventureWorks L 54000000534900001 54000000530800079 FULL
AdventureWorks F 54000000543500170 54000000530800079 FULL
AdventureWorks F 54000000551300035 54000000530800079 FULL
AdventureWorks L 54000000538300001 54000000530800079 FULL
AdventureWorks G 54000000554400040 54000000530800079 FULL
AdventureWorks L 54000000554100001 54000000530800079 FULL
AdventureWorks L 54000000556100001 54000000530800079 FULL
AdventureWorks D 54000000526900064 54000000512800238 FULL
AdventureWorks L 54000000526900064 54000000526900064 FULL
AdventureWorks I 54000000530800076 54000000526900064 FULL
AdventureWorks L 54000000530600001 54000000526900064 FULL
AdventureWorks L 54000000534100001 54000000526900064 FULL

Can someone please help??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-02 : 15:53:41

To be sure your backups are good, add WITH INIT to your backup commands. This will ensure that no other backups are included in already existing files.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

petey84
Starting Member

14 Posts

Posted - 2010-11-02 : 20:28:16

Thanks Tkizer, by using the WITH INIT for my backups seems to resolve the issue except the last restore highlighted below in BOLD

RESTORE DATABASE AdventureWorks FROM DISK = 'E:\TEST\AW.BAK' WITH NORECOVERY
RESTORE DATABASE AdventureWorks FROM DISK = 'E:\TEST\AWDIFF1.BAK' WITH NORECOVERY
RESTORE LOG AdventureWorks FROM DISK = 'E:\TEST\AW2.TRN' WITH NORECOVERY

RESTORE LOG AdventureWorks FROM DISK = 'E:\TEST\AWTAIL.TRN' WITH RECOVERY

It gives me the following error message:

Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 54000000568500001, which is too recent to apply to the database. An earlier log backup that includes LSN 54000000550400001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

What could be the problem?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-03 : 00:38:48

You are missing a log restore according to the error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources