Showing posts with label state. Show all posts
Showing posts with label state. Show all posts

Monday, March 19, 2012

Differential DB Restore Script Help

I am trying to restore a differential backup on top of the
restored full backup and getting the following error:
Server: Msg 3136, Level 16, State 1, Line 1
Cannot apply the backup on device 'F:\HISTORY backup.BAK'
to database 'HISTORY'.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Here is my script:
RESTORE DATABASE HISTORY
FROM DISK = 'F:\HISTORY backup.BAK'
WITH
DBO_ONLY,
REPLACE,
STANDBY = 'F:\UNDO_WPHISTORY.ldf',
MOVE 'HISTORY_dat1' TO 'E:\Program Files\Microsoft SQL
Server\MSSQL\Data\WPHISTORY_dat1.mdf',
MOVE 'HISTORY_log1' TO 'E:\Program Files\Microsoft SQL
Server\MSSQL\Data\HISTORY_log1.ldf'
---
I am thinking that it has something to do with backup
date/time.
Any help will be appreciated.Use RESTORE HEADERONLY to investigate what is on the backup files. Also,
check the sysbackuphistory tables in msdb to determine if you had any db
backup in between the db and diff backup you try to restore.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"calvin" <anonymous@.discussions.microsoft.com> wrote in message
news:018101c3a7c9$442a16e0$a501280a@.phx.gbl...
> I am trying to restore a differential backup on top of the
> restored full backup and getting the following error:
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'F:\HISTORY backup.BAK'
> to database 'HISTORY'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Here is my script:
> RESTORE DATABASE HISTORY
> FROM DISK = 'F:\HISTORY backup.BAK'
> WITH
> DBO_ONLY,
> REPLACE,
> STANDBY = 'F:\UNDO_WPHISTORY.ldf',
> MOVE 'HISTORY_dat1' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL\Data\WPHISTORY_dat1.mdf',
> MOVE 'HISTORY_log1' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL\Data\HISTORY_log1.ldf'
> ---
> I am thinking that it has something to do with backup
> date/time.
> Any help will be appreciated.
>|||Yes. There are Transaction log backups between the Full
backup anf the Differential backup (Differential backup is
the latest).
>--Original Message--
>Use RESTORE HEADERONLY to investigate what is on the
backup files. Also,
>check the sysbackuphistory tables in msdb to determine if
you had any db
>backup in between the db and diff backup you try to
restore.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"calvin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:018101c3a7c9$442a16e0$a501280a@.phx.gbl...
>> I am trying to restore a differential backup on top of
the
>> restored full backup and getting the following error:
>> Server: Msg 3136, Level 16, State 1, Line 1
>> Cannot apply the backup on device 'F:\HISTORY
backup.BAK'
>> to database 'HISTORY'.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> Here is my script:
>> RESTORE DATABASE HISTORY
>> FROM DISK = 'F:\HISTORY backup.BAK'
>> WITH
>> DBO_ONLY,
>> REPLACE,
>> STANDBY = 'F:\UNDO_WPHISTORY.ldf',
>> MOVE 'HISTORY_dat1' TO 'E:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\WPHISTORY_dat1.mdf',
>> MOVE 'HISTORY_log1' TO 'E:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\HISTORY_log1.ldf'
>> ---
>> I am thinking that it has something to do with backup
>> date/time.
>> Any help will be appreciated.
>>
>
>.
>|||Silly ? but did you use the right full backup? You can
only apply a differential to its proper full.
>--Original Message--
>Yes. There are Transaction log backups between the Full
>backup anf the Differential backup (Differential backup
is
>the latest).
>
>>--Original Message--
>>Use RESTORE HEADERONLY to investigate what is on the
>backup files. Also,
>>check the sysbackuphistory tables in msdb to determine
if
>you had any db
>>backup in between the db and diff backup you try to
>restore.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at:
>>http://groups.google.com/groups?
>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"calvin" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:018101c3a7c9$442a16e0$a501280a@.phx.gbl...
>> I am trying to restore a differential backup on top of
>the
>> restored full backup and getting the following error:
>> Server: Msg 3136, Level 16, State 1, Line 1
>> Cannot apply the backup on device 'F:\HISTORY
>backup.BAK'
>> to database 'HISTORY'.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> Here is my script:
>> RESTORE DATABASE HISTORY
>> FROM DISK = 'F:\HISTORY backup.BAK'
>> WITH
>> DBO_ONLY,
>> REPLACE,
>> STANDBY = 'F:\UNDO_WPHISTORY.ldf',
>> MOVE 'HISTORY_dat1' TO 'E:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\WPHISTORY_dat1.mdf',
>> MOVE 'HISTORY_log1' TO 'E:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\HISTORY_log1.ldf'
>> ---
>> I am thinking that it has something to do with backup
>> date/time.
>> Any help will be appreciated.
>>
>>
>>.
>.
>|||I wasn't referring to t-log backups between the db and the diff backup. I
was referring to *db backups* between the db backup you try to restore and
the diff backup you try to restore.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Calvin" <anonymous@.discussions.microsoft.com> wrote in message
news:022b01c3a7d1$b509aee0$a501280a@.phx.gbl...
> Yes. There are Transaction log backups between the Full
> backup anf the Differential backup (Differential backup is
> the latest).
>
> >--Original Message--
> >Use RESTORE HEADERONLY to investigate what is on the
> backup files. Also,
> >check the sysbackuphistory tables in msdb to determine if
> you had any db
> >backup in between the db and diff backup you try to
> restore.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"calvin" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:018101c3a7c9$442a16e0$a501280a@.phx.gbl...
> >> I am trying to restore a differential backup on top of
> the
> >> restored full backup and getting the following error:
> >>
> >> Server: Msg 3136, Level 16, State 1, Line 1
> >> Cannot apply the backup on device 'F:\HISTORY
> backup.BAK'
> >> to database 'HISTORY'.
> >> Server: Msg 3013, Level 16, State 1, Line 1
> >> RESTORE DATABASE is terminating abnormally.
> >>
> >> Here is my script:
> >> RESTORE DATABASE HISTORY
> >> FROM DISK = 'F:\HISTORY backup.BAK'
> >> WITH
> >> DBO_ONLY,
> >> REPLACE,
> >> STANDBY = 'F:\UNDO_WPHISTORY.ldf',
> >> MOVE 'HISTORY_dat1' TO 'E:\Program Files\Microsoft SQL
> >> Server\MSSQL\Data\WPHISTORY_dat1.mdf',
> >> MOVE 'HISTORY_log1' TO 'E:\Program Files\Microsoft SQL
> >> Server\MSSQL\Data\HISTORY_log1.ldf'
> >> ---
> >> I am thinking that it has something to do with backup
> >> date/time.
> >>
> >> Any help will be appreciated.
> >>
> >>
> >
> >
> >.
> >|||No. There is no database backups between the last full
backup which I restored and the differential backup.
and for Allen, yes it is the correct full backup.
Thanks.
>--Original Message--
>I wasn't referring to t-log backups between the db and
the diff backup. I
>was referring to *db backups* between the db backup you
try to restore and
>the diff backup you try to restore.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Calvin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:022b01c3a7d1$b509aee0$a501280a@.phx.gbl...
>> Yes. There are Transaction log backups between the Full
>> backup anf the Differential backup (Differential backup
is
>> the latest).
>>
>> >--Original Message--
>> >Use RESTORE HEADERONLY to investigate what is on the
>> backup files. Also,
>> >check the sysbackuphistory tables in msdb to determine
if
>> you had any db
>> >backup in between the db and diff backup you try to
>> restore.
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at:
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"calvin" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:018101c3a7c9$442a16e0$a501280a@.phx.gbl...
>> >> I am trying to restore a differential backup on top
of
>> the
>> >> restored full backup and getting the following error:
>> >>
>> >> Server: Msg 3136, Level 16, State 1, Line 1
>> >> Cannot apply the backup on device 'F:\HISTORY
>> backup.BAK'
>> >> to database 'HISTORY'.
>> >> Server: Msg 3013, Level 16, State 1, Line 1
>> >> RESTORE DATABASE is terminating abnormally.
>> >>
>> >> Here is my script:
>> >> RESTORE DATABASE HISTORY
>> >> FROM DISK = 'F:\HISTORY backup.BAK'
>> >> WITH
>> >> DBO_ONLY,
>> >> REPLACE,
>> >> STANDBY = 'F:\UNDO_WPHISTORY.ldf',
>> >> MOVE 'HISTORY_dat1' TO 'E:\Program Files\Microsoft
SQL
>> >> Server\MSSQL\Data\WPHISTORY_dat1.mdf',
>> >> MOVE 'HISTORY_log1' TO 'E:\Program Files\Microsoft
SQL
>> >> Server\MSSQL\Data\HISTORY_log1.ldf'
>> >> ----
-
>> >> I am thinking that it has something to do with backup
>> >> date/time.
>> >>
>> >> Any help will be appreciated.
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||Are you absolutely 100% certain? Did you check against the sysbackuphistory
tables in msdb?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Calvin" <anonymous@.discussions.microsoft.com> wrote in message
news:02c601c3a7da$1371a570$a501280a@.phx.gbl...
> No. There is no database backups between the last full
> backup which I restored and the differential backup.
> and for Allen, yes it is the correct full backup.
> Thanks.
>
> >--Original Message--
> >I wasn't referring to t-log backups between the db and
> the diff backup. I
> >was referring to *db backups* between the db backup you
> try to restore and
> >the diff backup you try to restore.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Calvin" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:022b01c3a7d1$b509aee0$a501280a@.phx.gbl...
> >> Yes. There are Transaction log backups between the Full
> >> backup anf the Differential backup (Differential backup
> is
> >> the latest).
> >>
> >>
> >>
> >> >--Original Message--
> >> >Use RESTORE HEADERONLY to investigate what is on the
> >> backup files. Also,
> >> >check the sysbackuphistory tables in msdb to determine
> if
> >> you had any db
> >> >backup in between the db and diff backup you try to
> >> restore.
> >> >
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at:
> >> >http://groups.google.com/groups?
> >> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"calvin" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:018101c3a7c9$442a16e0$a501280a@.phx.gbl...
> >> >> I am trying to restore a differential backup on top
> of
> >> the
> >> >> restored full backup and getting the following error:
> >> >>
> >> >> Server: Msg 3136, Level 16, State 1, Line 1
> >> >> Cannot apply the backup on device 'F:\HISTORY
> >> backup.BAK'
> >> >> to database 'HISTORY'.
> >> >> Server: Msg 3013, Level 16, State 1, Line 1
> >> >> RESTORE DATABASE is terminating abnormally.
> >> >>
> >> >> Here is my script:
> >> >> RESTORE DATABASE HISTORY
> >> >> FROM DISK = 'F:\HISTORY backup.BAK'
> >> >> WITH
> >> >> DBO_ONLY,
> >> >> REPLACE,
> >> >> STANDBY = 'F:\UNDO_WPHISTORY.ldf',
> >> >> MOVE 'HISTORY_dat1' TO 'E:\Program Files\Microsoft
> SQL
> >> >> Server\MSSQL\Data\WPHISTORY_dat1.mdf',
> >> >> MOVE 'HISTORY_log1' TO 'E:\Program Files\Microsoft
> SQL
> >> >> Server\MSSQL\Data\HISTORY_log1.ldf'
> >> >> ----
> -
> >> >> I am thinking that it has something to do with backup
> >> >> date/time.
> >> >>
> >> >> Any help will be appreciated.
> >> >>
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Sunday, March 11, 2012

Differential backups - under the covers

I have a question about how differential backups actually work in SQL Server 2000. Before I go any further, let me state that I do understand very well that a Differential backup records all the changes to the database since the last full backup. However, I would be interested to know how this is actually done by SQL server? The issue that puzzles me is that the Transaction Log is the normal place where you would expect to find the history of transactions necessary to do the differential backup. However, I think it would also be true to say that the history of transactions can be cleared down as a result of checkpoints/truncations. This would lead to the situation where the Transaction Log file would be truncated - losing some of the history that would otherwise provide the transaction history since the last full backup.

Therefore, when doing a differential backup that may be only one in a long sequence of differential and transaction log backups, how does SQL Server find out the transaction history since the last full backup when the transaction log does not necessarily contain all this information?

CliveI believe that a full backup writes to each page header in the database which LSN (or similar counter) was last picked up by the full backup. A differential backup reads this counter, and figures out if the page has been written to since the last (full of diff) backup. If the page has been written to, then the page is backed up. If the page has not had the header updated, then page is assumed to be the same as it was before, and not backed up. You are correct that in order ot get a real point in time recovery you need the transaction logs. Differential backups are just a way of not backing up historical/static data that does not change day to day. Clear as mud?|||thanks for the reply.

Yes, I can understand what you're saying. So, you're suggestion is that it scans every page in the database. Maybe another answer (I picked it up from another forum and a note can be found in BOL) that SQL Server scans all the previous backup files and finds out what has changed in that way.

note from BOL:-

"If you have created any file backups since the last full database backup, those files will be scanned by Microsoft SQL Server 2000 at the beginning of a differential database backup. This may cause some degradation of performance in the differential database backup. For more information, see Using File Backups."