Differential Restore

Win2K3 SQL2K Enterprise Edition
This is my current backup schedule
Sunday FULL
M-Sat Differential
TranLogs Backups every hour
I am trying to restore to a point in time, specifically to the time
right after my last Differentail backup. I am using Ent Manager for
this. I have restored the Full with no recovery and then I select just
the last Differential Backup for restore and I get the message
Cannot apply the backup on device 'd:\abcd\diff' to database 'TEST'
restore database is terminated abnormally
Is there something wrong with my restore process. This is a qa
environment that I am testing this on. I have added a lot of data to
the database and then manual run the diffential job. Now I am trying
to restore to this point but I keep getting this error. Any help would
be appreciated.Please check the backup file you are using.
Thanks
Ajay Rengunthwar|||Ajay, Not sure what you mean exactly. I was able to restore my last
full backup. Both with and without recovery. I am just having trouble
restoring the differentials backup after that.

Differential backups way too large

I don't know why this is happening...I have a database with the following
backup schedule..
Friday 2:30am Full backup
Daily except friday 2:30am Differential backup
Daily between 6:15am and 11:45pm half hourly tlog backups
So I'm looking at last weeks full backup it is 2GB as of 2:30 am...
The first tlog backup at 6:15am is 1.4 GB ?
The diff starting the next day was already 1.9GB yet the next full backup
will only be 2GB?
If a differential is only supposed to show the changes since last full
backup then how can it be the size of the full database by the next day?
There is absolutely no way all that data has changed.
Is there some magical setting I'm missing that resets the state of something
on a full backup?
any/all help is most graciously appreciated....I'm really tired of moving
unnecessary data around our wires....
Tim,
Do you also rebuild indexes, do a DBCC INDEXDEFRAG, et cetera? These things
also cause pages to change and logs to grow.
RLF
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23E92rvLdIHA.3400@.TK2MSFTNGP03.phx.gbl...
>I don't know why this is happening...I have a database with the following
>backup schedule..
> Friday 2:30am Full backup
> Daily except friday 2:30am Differential backup
> Daily between 6:15am and 11:45pm half hourly tlog backups
>
> So I'm looking at last weeks full backup it is 2GB as of 2:30 am...
> The first tlog backup at 6:15am is 1.4 GB ?
> The diff starting the next day was already 1.9GB yet the next full backup
> will only be 2GB?
> If a differential is only supposed to show the changes since last full
> backup then how can it be the size of the full database by the next day?
> There is absolutely no way all that data has changed.
> Is there some magical setting I'm missing that resets the state of
> something on a full backup?
> any/all help is most graciously appreciated....I'm really tired of moving
> unnecessary data around our wires....
>
|||Yes I do and thought about that immediately after posting this... BUT ... I
can see that making my first tlog large, but not every days DIFF file.
Oh crap...just realized. If I'm rebuilding clustered indexes, then I'm
indirectly updating EVERYTHING correct?
Guess I need to be sure that happens immediately before my full/diff
backups.
Thanks for such a quick response.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23C3S6xLdIHA.4588@.TK2MSFTNGP06.phx.gbl...
> Tim,
> Do you also rebuild indexes, do a DBCC INDEXDEFRAG, et cetera? These
> things also cause pages to change and logs to grow.
> RLF
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:%23E92rvLdIHA.3400@.TK2MSFTNGP03.phx.gbl...
>
|||Hi Tim,
What you saw is not uncommon.
Think of diffential backup the same as tran log backup in that they all
contain db changes. They only differ in that the diffential backup is
accumulative, whereas the tran log backup is incremental. If database
activities do fancy things, such as insert 10 milion records in a temp
table, update them, then insert into a second temp table, then delete from
the frist table, then ..All these transactions contribute to the size of
tran log or diff backups. although as a net result, that may not affect the
size of dbs too much.
I suggest you run a profile trace to see what exactly run during the log
growth. You may be surprised to see the apps doing dumb things...
rick
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23E92rvLdIHA.3400@.TK2MSFTNGP03.phx.gbl...
>I don't know why this is happening...I have a database with the following
>backup schedule..
> Friday 2:30am Full backup
> Daily except friday 2:30am Differential backup
> Daily between 6:15am and 11:45pm half hourly tlog backups
>
> So I'm looking at last weeks full backup it is 2GB as of 2:30 am...
> The first tlog backup at 6:15am is 1.4 GB ?
> The diff starting the next day was already 1.9GB yet the next full backup
> will only be 2GB?
> If a differential is only supposed to show the changes since last full
> backup then how can it be the size of the full database by the next day?
> There is absolutely no way all that data has changed.
> Is there some magical setting I'm missing that resets the state of
> something on a full backup?
> any/all help is most graciously appreciated....I'm really tired of moving
> unnecessary data around our wires....
>
|||Hmmm that is NOT what BOL says about differentials. TLOGS grow based on
activity level...diffs show the actual database pages that have been altered
only regardless of activity level.
"Rick D." <y.ding@.neu.edu> wrote in message
news:1D24A79F-AD81-4B7D-A279-80ED18C72E9E@.microsoft.com...
> Hi Tim,
> What you saw is not uncommon.
> Think of diffential backup the same as tran log backup in that they all
> contain db changes. They only differ in that the diffential backup is
> accumulative, whereas the tran log backup is incremental. If database
> activities do fancy things, such as insert 10 milion records in a temp
> table, update them, then insert into a second temp table, then delete from
> the frist table, then ..All these transactions contribute to the size of
> tran log or diff backups. although as a net result, that may not affect
> the size of dbs too much.
> I suggest you run a profile trace to see what exactly run during the log
> growth. You may be surprised to see the apps doing dumb things...
>
> rick
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:%23E92rvLdIHA.3400@.TK2MSFTNGP03.phx.gbl...
>
|||Yes, you got it. - RLF
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23odFZ8LdIHA.4744@.TK2MSFTNGP06.phx.gbl...
> Yes I do and thought about that immediately after posting this... BUT ...
> I can see that making my first tlog large, but not every days DIFF file.
> Oh crap...just realized. If I'm rebuilding clustered indexes, then I'm
> indirectly updating EVERYTHING correct?
> Guess I need to be sure that happens immediately before my full/diff
> backups.
> Thanks for such a quick response.
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23C3S6xLdIHA.4588@.TK2MSFTNGP06.phx.gbl...
>

Differential backups way too large

I don't know why this is happening...I have a database with the following
backup schedule..
Friday 2:30am Full backup
Daily except friday 2:30am Differential backup
Daily between 6:15am and 11:45pm half hourly tlog backups
So I'm looking at last weeks full backup it is 2GB as of 2:30 am...
The first tlog backup at 6:15am is 1.4 GB '
The diff starting the next day was already 1.9GB yet the next full backup
will only be 2GB?
If a differential is only supposed to show the changes since last full
backup then how can it be the size of the full database by the next day?
There is absolutely no way all that data has changed.
Is there some magical setting I'm missing that resets the state of something
on a full backup?
any/all help is most graciously appreciated....I'm really tired of moving
unnecessary data around our wires....Tim,
Do you also rebuild indexes, do a DBCC INDEXDEFRAG, et cetera? These things
also cause pages to change and logs to grow.
RLF
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23E92rvLdIHA.3400@.TK2MSFTNGP03.phx.gbl...
>I don't know why this is happening...I have a database with the following
>backup schedule..
> Friday 2:30am Full backup
> Daily except friday 2:30am Differential backup
> Daily between 6:15am and 11:45pm half hourly tlog backups
>
> So I'm looking at last weeks full backup it is 2GB as of 2:30 am...
> The first tlog backup at 6:15am is 1.4 GB '
> The diff starting the next day was already 1.9GB yet the next full backup
> will only be 2GB?
> If a differential is only supposed to show the changes since last full
> backup then how can it be the size of the full database by the next day?
> There is absolutely no way all that data has changed.
> Is there some magical setting I'm missing that resets the state of
> something on a full backup?
> any/all help is most graciously appreciated....I'm really tired of moving
> unnecessary data around our wires....
>|||Yes I do and thought about that immediately after posting this... BUT ... I
can see that making my first tlog large, but not every days DIFF file.
Oh crap...just realized. If I'm rebuilding clustered indexes, then I'm
indirectly updating EVERYTHING correct?
Guess I need to be sure that happens immediately before my full/diff
backups.
Thanks for such a quick response.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23C3S6xLdIHA.4588@.TK2MSFTNGP06.phx.gbl...
> Tim,
> Do you also rebuild indexes, do a DBCC INDEXDEFRAG, et cetera? These
> things also cause pages to change and logs to grow.
> RLF
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:%23E92rvLdIHA.3400@.TK2MSFTNGP03.phx.gbl...
>>I don't know why this is happening...I have a database with the following
>>backup schedule..
>> Friday 2:30am Full backup
>> Daily except friday 2:30am Differential backup
>> Daily between 6:15am and 11:45pm half hourly tlog backups
>>
>> So I'm looking at last weeks full backup it is 2GB as of 2:30 am...
>> The first tlog backup at 6:15am is 1.4 GB '
>> The diff starting the next day was already 1.9GB yet the next full backup
>> will only be 2GB?
>> If a differential is only supposed to show the changes since last full
>> backup then how can it be the size of the full database by the next day?
>> There is absolutely no way all that data has changed.
>> Is there some magical setting I'm missing that resets the state of
>> something on a full backup?
>> any/all help is most graciously appreciated....I'm really tired of
>> moving unnecessary data around our wires....
>|||Hi Tim,
What you saw is not uncommon.
Think of diffential backup the same as tran log backup in that they all
contain db changes. They only differ in that the diffential backup is
accumulative, whereas the tran log backup is incremental. If database
activities do fancy things, such as insert 10 milion records in a temp
table, update them, then insert into a second temp table, then delete from
the frist table, then ..All these transactions contribute to the size of
tran log or diff backups. although as a net result, that may not affect the
size of dbs too much.
I suggest you run a profile trace to see what exactly run during the log
growth. You may be surprised to see the apps doing dumb things...
rick
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23E92rvLdIHA.3400@.TK2MSFTNGP03.phx.gbl...
>I don't know why this is happening...I have a database with the following
>backup schedule..
> Friday 2:30am Full backup
> Daily except friday 2:30am Differential backup
> Daily between 6:15am and 11:45pm half hourly tlog backups
>
> So I'm looking at last weeks full backup it is 2GB as of 2:30 am...
> The first tlog backup at 6:15am is 1.4 GB '
> The diff starting the next day was already 1.9GB yet the next full backup
> will only be 2GB?
> If a differential is only supposed to show the changes since last full
> backup then how can it be the size of the full database by the next day?
> There is absolutely no way all that data has changed.
> Is there some magical setting I'm missing that resets the state of
> something on a full backup?
> any/all help is most graciously appreciated....I'm really tired of moving
> unnecessary data around our wires....
>|||Hmmm that is NOT what BOL says about differentials. TLOGS grow based on
activity level...diffs show the actual database pages that have been altered
only regardless of activity level.
"Rick D." <y.ding@.neu.edu> wrote in message
news:1D24A79F-AD81-4B7D-A279-80ED18C72E9E@.microsoft.com...
> Hi Tim,
> What you saw is not uncommon.
> Think of diffential backup the same as tran log backup in that they all
> contain db changes. They only differ in that the diffential backup is
> accumulative, whereas the tran log backup is incremental. If database
> activities do fancy things, such as insert 10 milion records in a temp
> table, update them, then insert into a second temp table, then delete from
> the frist table, then ..All these transactions contribute to the size of
> tran log or diff backups. although as a net result, that may not affect
> the size of dbs too much.
> I suggest you run a profile trace to see what exactly run during the log
> growth. You may be surprised to see the apps doing dumb things...
>
> rick
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:%23E92rvLdIHA.3400@.TK2MSFTNGP03.phx.gbl...
>>I don't know why this is happening...I have a database with the following
>>backup schedule..
>> Friday 2:30am Full backup
>> Daily except friday 2:30am Differential backup
>> Daily between 6:15am and 11:45pm half hourly tlog backups
>>
>> So I'm looking at last weeks full backup it is 2GB as of 2:30 am...
>> The first tlog backup at 6:15am is 1.4 GB '
>> The diff starting the next day was already 1.9GB yet the next full backup
>> will only be 2GB?
>> If a differential is only supposed to show the changes since last full
>> backup then how can it be the size of the full database by the next day?
>> There is absolutely no way all that data has changed.
>> Is there some magical setting I'm missing that resets the state of
>> something on a full backup?
>> any/all help is most graciously appreciated....I'm really tired of
>> moving unnecessary data around our wires....
>|||Yes, you got it. - RLF
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23odFZ8LdIHA.4744@.TK2MSFTNGP06.phx.gbl...
> Yes I do and thought about that immediately after posting this... BUT ...
> I can see that making my first tlog large, but not every days DIFF file.
> Oh crap...just realized. If I'm rebuilding clustered indexes, then I'm
> indirectly updating EVERYTHING correct?
> Guess I need to be sure that happens immediately before my full/diff
> backups.
> Thanks for such a quick response.
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23C3S6xLdIHA.4588@.TK2MSFTNGP06.phx.gbl...
>> Tim,
>> Do you also rebuild indexes, do a DBCC INDEXDEFRAG, et cetera? These
>> things also cause pages to change and logs to grow.
>> RLF
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:%23E92rvLdIHA.3400@.TK2MSFTNGP03.phx.gbl...
>>I don't know why this is happening...I have a database with the following
>>backup schedule..
>> Friday 2:30am Full backup
>> Daily except friday 2:30am Differential backup
>> Daily between 6:15am and 11:45pm half hourly tlog backups
>>
>> So I'm looking at last weeks full backup it is 2GB as of 2:30 am...
>> The first tlog backup at 6:15am is 1.4 GB '
>> The diff starting the next day was already 1.9GB yet the next full
>> backup will only be 2GB?
>> If a differential is only supposed to show the changes since last full
>> backup then how can it be the size of the full database by the next day?
>> There is absolutely no way all that data has changed.
>> Is there some magical setting I'm missing that resets the state of
>> something on a full backup?
>> any/all help is most graciously appreciated....I'm really tired of
>> moving unnecessary data around our wires....
>>
>

Differential Backup via Maintenance wizard

(If this is not the correct forum please point me in the right direction)

I am trying to schedule a Daliy differential backup using the Maintenance Wizard. This works fine for me, however I do not see where I can actualy speficy the file name I want to use for the differential backup

I notice that SQL 2005 will add the date and time for example MyDatabase_backup_200703191330. I would like to use a different name, for example MyDatabase_DiffBackup_DateTime. How can I specify this, I am not able to modify the T-SQL that is generated. is there a way to do this? Modify the T-SQL

Do I need to specify "backup databases across one or more files option" if so where do i specify the file name.

Should I create the job manually and execute to a T-SQL statement where I can specify the file name?

Thanks

-javahar

Create a Job and write T-SQL Code for Differential Backup will be great and less problem

Thanks

Faiz Farazi

www.databasetimes.net

Best learning center for Microsoft

http://www.lascomp.com

Differential Backup in Maintenance Plan?

Hello. Can you schedule a differential backup in a
Database Maint Plan? I didn't see any way to do that, so
my guess is this is a sqlwish type of thing? Unless
someone knows a trick? Thanks, BruceHello, Bruce!
Nope. Diff backups are not covered by the maint plan
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
: Hello. Can you schedule a differential backup in a
: Database Maint Plan? I didn't see any way to do that, so
: my guess is this is a sqlwish type of thing? Unless
: someone knows a trick? Thanks, Bruce
-- Microsoft CDO for Windows 2000|||Hello, Bruce!
Nope. Diff backups are not covered by the maint plan
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
: Hello. Can you schedule a differential backup in a
: Database Maint Plan? I didn't see any way to do that, so
: my guess is this is a sqlwish type of thing? Unless
: someone knows a trick? Thanks, Bruce
-- Microsoft CDO for Windows 2000|||ok Andy... Yep, a scheduled job would be fine. Just not
ALLLLLLL in the Maint plan then, oh well... Thanks, Bruce
>--Original Message--
>I thought I just saw Tibor answer this question a few
minutes ago. Oh well,
>no there is not a way to do it in the MP. Create your
own scheduled job and
>do it there.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:006401c34641$7daeeba0$a501280a@.phx.gbl...
>> Hello. Can you schedule a differential backup in a
>> Database Maint Plan? I didn't see any way to do that,
so
>> my guess is this is a sqlwish type of thing? Unless
>> someone knows a trick? Thanks, Bruce
>
>.
>|||Why not put ALLLLL the backups in your own scheduled jobs then to be
consistent. You have much better control over things if you don't use the
wizard and there really isn't anything the wizard can do that you can't with
a few lines of code.
--
Andrew J. Kelly
SQL Server MVP
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:0b4001c34647$70000e20$a001280a@.phx.gbl...
> ok Andy... Yep, a scheduled job would be fine. Just not
> ALLLLLLL in the Maint plan then, oh well... Thanks, Bruce
>
> >--Original Message--
> >I thought I just saw Tibor answer this question a few
> minutes ago. Oh well,
> >no there is not a way to do it in the MP. Create your
> own scheduled job and
> >do it there.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> >news:006401c34641$7daeeba0$a501280a@.phx.gbl...
> >> Hello. Can you schedule a differential backup in a
> >> Database Maint Plan? I didn't see any way to do that,
> so
> >> my guess is this is a sqlwish type of thing? Unless
> >> someone knows a trick? Thanks, Bruce
> >
> >
> >.
> >|||Bruce
You can use the backup wizard to create a differential
backup, if you do not want to code it yourself, and keep
it consistent if you need to do more than one.
Regards
John