Monday, March 19, 2012
differentials question - can i skip a full backup?
I have a SqlExpress app and would like to start using differential
backups (we currently always use full backups) for our auto-backup
utility. The problem is that the users can create their own backups
(always full). My auto-backup utility will not use these files. I
control the auto-backups and hide them from the user.
Can I somehow tell SqlExpress to ignore any user-created backups and
reference my differentials off MY full backups?
thanks very much!
Deanna
That's GREAT news. The user backs up via the application, so assuming
there's a SQLDMO option for COPY_ONLY I am home free. I'm off to
research. If there's NOT and you have another way to programmatically
cause the COPY_ONLY option please let me know!
thanks so much!
Deanna
|||Tibor,
Thanks so much. I was just reading about SMO this morning (we JUST
moved to 2005 and I haven't caught up). I do NOT see the COPY_ONLY
exposed in SMO though. Nor do I see an .ExecuteImmediate on SMO. I
guess I should stick with DMO and use the .ExecuteImmediate. If anyone
knows a way to accomplish this with SMO please let me know. I'd prefer
to dump DMO.
Thanks for all the help!
Deanna
differentials question - can i skip a full backup?
I have a SqlExpress app and would like to start using differential
backups (we currently always use full backups) for our auto-backup
utility. The problem is that the users can create their own backups
(always full). My auto-backup utility will not use these files. I
control the auto-backups and hide them from the user.
Can I somehow tell SqlExpress to ignore any user-created backups and
reference my differentials off MY full backups?
thanks very much!
DeannaI see what you want to do, but unfortunately, there's no such setting.
If you could force your users to do the full backups using the COPY_ONLY option, then those full
backups would not affect your full backup/.diff backup sequence. But there's no such setting to
force a user to only do such backups...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message news:1165871674.022127.48890@.l12g2000cwl.googlegroups.com...
> Hi,
> I have a SqlExpress app and would like to start using differential
> backups (we currently always use full backups) for our auto-backup
> utility. The problem is that the users can create their own backups
> (always full). My auto-backup utility will not use these files. I
> control the auto-backups and hide them from the user.
> Can I somehow tell SqlExpress to ignore any user-created backups and
> reference my differentials off MY full backups?
> thanks very much!
> Deanna
>|||That's GREAT news. The user backs up via the application, so assuming
there's a SQLDMO option for COPY_ONLY I am home free. I'm off to
research. If there's NOT and you have another way to programmatically
cause the COPY_ONLY option please let me know!
thanks so much!
Deanna|||> That's GREAT news. The user backs up via the application, so assuming
> there's a SQLDMO option for COPY_ONLY I am home free.
DMO is deprecated and does not expose new functionality in 2005.
If you really want to use DMO, use .ExecuteImmediate or similar method to execute the whole BACKUP
command.
Or, use the replacement SMO, which is managed API, and (should) expose the COPY_ONLY option of the
backup command through the object model.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message news:1165928275.312674.220290@.f1g2000cwa.googlegroups.com...
> That's GREAT news. The user backs up via the application, so assuming
> there's a SQLDMO option for COPY_ONLY I am home free. I'm off to
> research. If there's NOT and you have another way to programmatically
> cause the COPY_ONLY option please let me know!
> thanks so much!
> Deanna
>|||Tibor,
Thanks so much. I was just reading about SMO this morning (we JUST
moved to 2005 and I haven't caught up). I do NOT see the COPY_ONLY
exposed in SMO though. Nor do I see an .ExecuteImmediate on SMO. I
guess I should stick with DMO and use the .ExecuteImmediate. If anyone
knows a way to accomplish this with SMO please let me know. I'd prefer
to dump DMO.
Thanks for all the help!
Deanna|||> I do NOT see the COPY_ONLY
> exposed in SMO though.
Interesting. I had a look and couldn't find it. Perhaps it is considered too unusual so it isn't
exposed through the SQL object model.
> Nor do I see an .ExecuteImmediate on SMO.
I didn't say there exist such a method in SMO. I was referring to DMO. In SMO, you can use
ExecuteNonQuery on either a ServerConnection object (which is what I would use) or a Database
object.
Or, you check out the BackupRestoreBase class, which has members such as ExecuteSql (etc). I don't
think you'll find COPY_ONLY here, but it seems to be another way of sending TSQL commands to the
engine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message
news:1165933594.476139.251730@.l12g2000cwl.googlegroups.com...
> Tibor,
> Thanks so much. I was just reading about SMO this morning (we JUST
> moved to 2005 and I haven't caught up). I do NOT see the COPY_ONLY
> exposed in SMO though. Nor do I see an .ExecuteImmediate on SMO. I
> guess I should stick with DMO and use the .ExecuteImmediate. If anyone
> knows a way to accomplish this with SMO please let me know. I'd prefer
> to dump DMO.
> Thanks for all the help!
> Deanna
>
differentials question - can i skip a full backup?
I have a SqlExpress app and would like to start using differential
backups (we currently always use full backups) for our auto-backup
utility. The problem is that the users can create their own backups
(always full). My auto-backup utility will not use these files. I
control the auto-backups and hide them from the user.
Can I somehow tell SqlExpress to ignore any user-created backups and
reference my differentials off MY full backups?
thanks very much!
DeannaI see what you want to do, but unfortunately, there's no such setting.
If you could force your users to do the full backups using the COPY_ONLY opt
ion, then those full
backups would not affect your full backup/.diff backup sequence. But there's
no such setting to
force a user to only do such backups...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message news:1165871674.022127.48890@.l12g2000cwl.googlegroup
s.com...
> Hi,
> I have a SqlExpress app and would like to start using differential
> backups (we currently always use full backups) for our auto-backup
> utility. The problem is that the users can create their own backups
> (always full). My auto-backup utility will not use these files. I
> control the auto-backups and hide them from the user.
> Can I somehow tell SqlExpress to ignore any user-created backups and
> reference my differentials off MY full backups?
> thanks very much!
> Deanna
>|||That's GREAT news. The user backs up via the application, so assuming
there's a SQLDMO option for COPY_ONLY I am home free. I'm off to
research. If there's NOT and you have another way to programmatically
cause the COPY_ONLY option please let me know!
thanks so much!
Deanna|||> That's GREAT news. The user backs up via the application, so assuming
> there's a SQLDMO option for COPY_ONLY I am home free.
DMO is deprecated and does not expose new functionality in 2005.
If you really want to use DMO, use .ExecuteImmediate or similar method to ex
ecute the whole BACKUP
command.
Or, use the replacement SMO, which is managed API, and (should) expose the C
OPY_ONLY option of the
backup command through the object model.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message news:1165928275.312674.220290@.f1g2000cwa.googlegroup
s.com...
> That's GREAT news. The user backs up via the application, so assuming
> there's a SQLDMO option for COPY_ONLY I am home free. I'm off to
> research. If there's NOT and you have another way to programmatically
> cause the COPY_ONLY option please let me know!
> thanks so much!
> Deanna
>|||Tibor,
Thanks so much. I was just reading about SMO this morning (we JUST
moved to 2005 and I haven't caught up). I do NOT see the COPY_ONLY
exposed in SMO though. Nor do I see an .ExecuteImmediate on SMO. I
guess I should stick with DMO and use the .ExecuteImmediate. If anyone
knows a way to accomplish this with SMO please let me know. I'd prefer
to dump DMO.
Thanks for all the help!
Deanna|||> I do NOT see the COPY_ONLY
> exposed in SMO though.
Interesting. I had a look and couldn't find it. Perhaps it is considered to
o unusual so it isn't
exposed through the SQL object model.
> Nor do I see an .ExecuteImmediate on SMO.
I didn't say there exist such a method in SMO. I was referring to DMO. In SM
O, you can use
ExecuteNonQuery on either a ServerConnection object (which is what I would u
se) or a Database
object.
Or, you check out the BackupRestoreBase class, which has members such as Exe
cuteSql (etc). I don't
think you'll find COPY_ONLY here, but it seems to be another way of sending
TSQL commands to the
engine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<ddelapasse@.gmail.com> wrote in message
news:1165933594.476139.251730@.l12g2000cwl.googlegroups.com...
> Tibor,
> Thanks so much. I was just reading about SMO this morning (we JUST
> moved to 2005 and I haven't caught up). I do NOT see the COPY_ONLY
> exposed in SMO though. Nor do I see an .ExecuteImmediate on SMO. I
> guess I should stick with DMO and use the .ExecuteImmediate. If anyone
> knows a way to accomplish this with SMO please let me know. I'd prefer
> to dump DMO.
> Thanks for all the help!
> Deanna
>
Differentials on Master
ing saved.
1. Should only full backups be made of Master, or are differentials in between full backups allowed?
Message posted via http://www.sqlmonster.com
The systems dbs are usually way to small to do anything other than a FULL
backup. It can only take a few seconds to do a full backup on the Master
DB.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be4f91128ce74dc3b75ac0d3674ed8ea@.SQLMonster.c om...
> Just in case we overlook backing up Master after executing statements that
> modify it, we also have once a week full backups accompanied by a nightly
> differential using the NOINIT clause to append to the same backup device
> the full backups of Master are being saved.
> 1. Should only full backups be made of Master, or are differentials in
> between full backups allowed?
> --
> Message posted via http://www.sqlmonster.com
|||My problem is disk space, and for redundancy appending one backup set on top of another, so a full backup, on top of a full backup to the same backup device using NOINIT, chews up needed disk space.
1. Should only full backups be made of Master, or are differentials in between full backups allowed?
2. From what I can tell (implied from your first response) that performing differentials on Master is allowed?
3. Restoring Master using differentials would not be any more dangerous that restoring any other database using differentials, or is this a false statement?
Message posted via http://www.sqlmonster.com
|||I suppose Diff's on Master is allowed. If you are that tight on disk space
you are in trouble and that should be remedied. Master DB is only a few MB
in size and there are lots of other things that can each up disk space
faster than a Master Full backup. Why are you appending them to the same
device then? If you create a new file for each backup you can delete the
old ones before you add the new one and never use more disk space than a
full cycle (how ever many days you want to keeps worth).
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:7151d81a3055437b89361c204cb6fce1@.SQLMonster.c om...
> My problem is disk space, and for redundancy appending one backup set on
> top of another, so a full backup, on top of a full backup to the same
> backup device using NOINIT, chews up needed disk space.
> 1. Should only full backups be made of Master, or are differentials in
> between full backups allowed?
> 2. From what I can tell (implied from your first response) that performing
> differentials on Master is allowed?
> 3. Restoring Master using differentials would not be any more dangerous
> that restoring any other database using differentials, or is this a false
> statement?
> --
> Message posted via http://www.sqlmonster.com
Differentials on Master
odify it, we also have once a week full backups accompanied by a nightly dif
ferential using the NOINIT clause to append to the same backup device the fu
ll backups of Master are be
ing saved.
1. Should only full backups be made of Master, or are differentials in betwe
en full backups allowed?
Message posted via http://www.droptable.comThe systems dbs are usually way to small to do anything other than a FULL
backup. It can only take a few seconds to do a full backup on the Master
DB.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:be4f91128ce74dc3b75ac0d3674ed8ea@.SQ
droptable.com...
> Just in case we overlook backing up Master after executing statements that
> modify it, we also have once a week full backups accompanied by a nightly
> differential using the NOINIT clause to append to the same backup device
> the full backups of Master are being saved.
> 1. Should only full backups be made of Master, or are differentials in
> between full backups allowed?
> --
> Message posted via http://www.droptable.com|||My problem is disk space, and for redundancy appending one backup set on top
of another, so a full backup, on top of a full backup to the same backup de
vice using NOINIT, chews up needed disk space.
1. Should only full backups be made of Master, or are differentials in betwe
en full backups allowed?
2. From what I can tell (implied from your first response) that performing d
ifferentials on Master is allowed?
3. Restoring Master using differentials would not be any more dangerous that
restoring any other database using differentials, or is this a false statem
ent?
Message posted via http://www.droptable.com|||I suppose Diff's on Master is allowed. If you are that tight on disk space
you are in trouble and that should be remedied. Master DB is only a few MB
in size and there are lots of other things that can each up disk space
faster than a Master Full backup. Why are you appending them to the same
device then? If you create a new file for each backup you can delete the
old ones before you add the new one and never use more disk space than a
full cycle (how ever many days you want to keeps worth).
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:7151d81a3055437b89361c204cb6fce1@.SQ
droptable.com...
> My problem is disk space, and for redundancy appending one backup set on
> top of another, so a full backup, on top of a full backup to the same
> backup device using NOINIT, chews up needed disk space.
> 1. Should only full backups be made of Master, or are differentials in
> between full backups allowed?
> 2. From what I can tell (implied from your first response) that performing
> differentials on Master is allowed?
> 3. Restoring Master using differentials would not be any more dangerous
> that restoring any other database using differentials, or is this a false
> statement?
> --
> Message posted via http://www.droptable.com
Differentials on Master
1. Should only full backups be made of Master, or are differentials in between full backups allowed?
--
Message posted via http://www.sqlmonster.comThe systems dbs are usually way to small to do anything other than a FULL
backup. It can only take a few seconds to do a full backup on the Master
DB.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be4f91128ce74dc3b75ac0d3674ed8ea@.SQLMonster.com...
> Just in case we overlook backing up Master after executing statements that
> modify it, we also have once a week full backups accompanied by a nightly
> differential using the NOINIT clause to append to the same backup device
> the full backups of Master are being saved.
> 1. Should only full backups be made of Master, or are differentials in
> between full backups allowed?
> --
> Message posted via http://www.sqlmonster.com|||My problem is disk space, and for redundancy appending one backup set on top of another, so a full backup, on top of a full backup to the same backup device using NOINIT, chews up needed disk space.
1. Should only full backups be made of Master, or are differentials in between full backups allowed?
2. From what I can tell (implied from your first response) that performing differentials on Master is allowed?
3. Restoring Master using differentials would not be any more dangerous that restoring any other database using differentials, or is this a false statement?
--
Message posted via http://www.sqlmonster.com|||I suppose Diff's on Master is allowed. If you are that tight on disk space
you are in trouble and that should be remedied. Master DB is only a few MB
in size and there are lots of other things that can each up disk space
faster than a Master Full backup. Why are you appending them to the same
device then? If you create a new file for each backup you can delete the
old ones before you add the new one and never use more disk space than a
full cycle (how ever many days you want to keeps worth).
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:7151d81a3055437b89361c204cb6fce1@.SQLMonster.com...
> My problem is disk space, and for redundancy appending one backup set on
> top of another, so a full backup, on top of a full backup to the same
> backup device using NOINIT, chews up needed disk space.
> 1. Should only full backups be made of Master, or are differentials in
> between full backups allowed?
> 2. From what I can tell (implied from your first response) that performing
> differentials on Master is allowed?
> 3. Restoring Master using differentials would not be any more dangerous
> that restoring any other database using differentials, or is this a false
> statement?
> --
> Message posted via http://www.sqlmonster.com
differentials backups
file/device as the original backup? or can I make a
seperate file/device'not necessarily, you can create it on the seperate device as well.
--
- Vishal
"susan" <susanbauer@.yahoo.com> wrote in message
news:057801c37e23$7f417fe0$a301280a@.phx.gbl...
> Do differential backups have to be backed up into the same
> file/device as the original backup? or can I make a
> seperate file/device'
>
Sunday, March 11, 2012
Differential backup fails because of erroneous full backup
I am using the Simple recovery model and I'm taking a weekly full backup each Monday morning with differentials taken every 4 hours during the day.
On Wednesday afternoon, a programmer ran a process that corrupted the db and I had to restore to the most recent differential. It was 5pm in the afternoon and a differential backup had just occured at 4pm. No problem, I figured.
I restored the full backup from Monday morning and tried to restore the most recent differential backup. The differential restore failed. Since I had used T-SQL for the initial attempt, I tried using Enterprise Manager to try again.
When viewing the backup history, I see my initial full backup taken on Monday plus all the differentials. BUT, on closer inspection, I noticed another full backup in the backup history that was taken early Tuesday morning. I can't figure out where this Tuesday morning full backup came from. It wasn't taken by me (or scheduled by me) and I'm the only one with access to the server. My full backups are usually named something like HCMPRP_20070718_FULL.bak. This erroneous full backup was named something like HCMPRP_03a_361adk2k_dd53.bak. It seemed like it was a system generated name. Not something I would choose. To top it off, I could not find this backup file anywhere on the server and when I tried to restore using this full backup, it failed.
Does anyone have any clues as to where this full backup might come from? Does SQL Server trigger a full backup on its own if some threshold is reached?
I ended up having to restore using the differential taken just before this erroneous full backup and lost a day of transactions.
Any insight is greatly appreciated.
No SQL server will not trigger a backup of its own........just check in the error log when the last full backup was made.....and see if the errorenous full backup name was present in the errorlog..........|||Thanks for the response. It turns out that there was someone else who had access to the server (unbeknownst to me) that triggered the full back up. Unfortunately, this individual did not know what he was doing and deleted the full backup file shortly after it completed. This, of course, screwed up my differentials.