How full backup works

Hello. Take this simple backup statement:
Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
With Name = 'MyDB full backup'
Would you think that if MyDB.bak already existed it would be overwritten
with the new backup? Or would it double in size, effectively containing 2
backups? I've seen it both ways I think. I ask because I'm trying to backup
this monolithic DB each night and we're running out of disk space on this
command.
I've seen some talk around here of backup compression utilities. In lieu of
that would it make sense to schedule simple batch job that deletes MyDB.bak
before proceeding with the backup? I realize we could be left vulnerable if
the subsequent backup should fail.
TIA,
KenKen,
Look at BACKUP DATABASE ... WITH INIT to write over the backup file.
Not having a reliable backup scares me, so I hate to see you depending on
such an approach. Here are a couple of options:
1. Schedule a tape backup of your .BAK file during the day, between the
nightly backups.
2. Weekly BACKUP DATABASE ... WITH INIT, then nightly ... WITH DIFFERENTIAL.
Then maybe you can fit a week's worth of backups on the disk. (Do the
database backup on the weekend if nothing much is happening then. Then, if
it fails, you can get a second shot at it before a lot of work happens.)
3. But better is to buy enough disk space for the needed backups. Use a
maintenance plan (whether SQL Server Maintenance Plan or one you roll
yourself) that will name each file with the datetime and a plan to delete
the backups that are older than you want to keep.
RLF
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
> Hello. Take this simple backup statement:
> Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
> With Name = 'MyDB full backup'
> Would you think that if MyDB.bak already existed it would be overwritten
> with the new backup? Or would it double in size, effectively containing 2
> backups? I've seen it both ways I think. I ask because I'm trying to
> backup
> this monolithic DB each night and we're running out of disk space on this
> command.
> I've seen some talk around here of backup compression utilities. In lieu
> of
> that would it make sense to schedule simple batch job that deletes
> MyDB.bak
> before proceeding with the backup? I realize we could be left vulnerable
> if
> the subsequent backup should fail.
> TIA,
> Ken|||It won't work one way one time and a different way the next if the commands
are always the same. If you don't specify the INIT option the default is
NOINIT which means to append. Decide which way you need it to be and specify
it even if it is the default behavior so there is no mistake down the road.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
> Hello. Take this simple backup statement:
> Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
> With Name = 'MyDB full backup'
> Would you think that if MyDB.bak already existed it would be overwritten
> with the new backup? Or would it double in size, effectively containing 2
> backups? I've seen it both ways I think. I ask because I'm trying to
> backup
> this monolithic DB each night and we're running out of disk space on this
> command.
> I've seen some talk around here of backup compression utilities. In lieu
> of
> that would it make sense to schedule simple batch job that deletes
> MyDB.bak
> before proceeding with the backup? I realize we could be left vulnerable
> if
> the subsequent backup should fail.
> TIA,
> Ken|||Hi there,
Default action of BACKUP command is "NOINIT" which appends to the existing
backup set. If you want to overwrite the backup set,
then you could use "WITH INIT" param.
In your case (to overwrite the existed backup set):
Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
With Name = 'MyDB full backup', INIT
Ekrem Ã?nsoy
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
> Hello. Take this simple backup statement:
> Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
> With Name = 'MyDB full backup'
> Would you think that if MyDB.bak already existed it would be overwritten
> with the new backup? Or would it double in size, effectively containing 2
> backups? I've seen it both ways I think. I ask because I'm trying to
> backup
> this monolithic DB each night and we're running out of disk space on this
> command.
> I've seen some talk around here of backup compression utilities. In lieu
> of
> that would it make sense to schedule simple batch job that deletes
> MyDB.bak
> before proceeding with the backup? I realize we could be left vulnerable
> if
> the subsequent backup should fail.
> TIA,
> Ken|||Shoot, I glossed right over that param when looking at BOL. Thanks guys for
pointing it out. In fact I'm rolling my own to do what you suggest in #2, do
differentials 6 days a week and full on the 7th.
BTW, it's nice that SQL Server can do differential backups. having said that
they don't appear to be totally efficient. Each day our data stroe grows a
little but the differential backup grows a lot.
Ken
"Russell Fields" wrote:
> Ken,
> Look at BACKUP DATABASE ... WITH INIT to write over the backup file.
> Not having a reliable backup scares me, so I hate to see you depending on
> such an approach. Here are a couple of options:
> 1. Schedule a tape backup of your .BAK file during the day, between the
> nightly backups.
> 2. Weekly BACKUP DATABASE ... WITH INIT, then nightly ... WITH DIFFERENTIAL.
> Then maybe you can fit a week's worth of backups on the disk. (Do the
> database backup on the weekend if nothing much is happening then. Then, if
> it fails, you can get a second shot at it before a lot of work happens.)
> 3. But better is to buy enough disk space for the needed backups. Use a
> maintenance plan (whether SQL Server Maintenance Plan or one you roll
> yourself) that will name each file with the datetime and a plan to delete
> the backups that are older than you want to keep.
> RLF
>
> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
> news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
> > Hello. Take this simple backup statement:
> >
> > Backup Database MyDB To Disk = N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
> > With Name = 'MyDB full backup'
> >
> > Would you think that if MyDB.bak already existed it would be overwritten
> > with the new backup? Or would it double in size, effectively containing 2
> > backups? I've seen it both ways I think. I ask because I'm trying to
> > backup
> > this monolithic DB each night and we're running out of disk space on this
> > command.
> >
> > I've seen some talk around here of backup compression utilities. In lieu
> > of
> > that would it make sense to schedule simple batch job that deletes
> > MyDB.bak
> > before proceeding with the backup? I realize we could be left vulnerable
> > if
> > the subsequent backup should fail.
> >
> > TIA,
> > Ken
>
>|||Ken,
Differentials are cumulative. So, assuming a Sunday full backup and daily
differentials, the Wednesday differential contains the Monday, Tuesday, and
Wednesday changes. If you know that you never have to go back in time more
than a couple of days, you can start timing them out.
RLF
"ktrock" <ktrock@.discussions.microsoft.com> wrote in message
news:88947E5E-4D56-4CCA-9114-C934A18F14B0@.microsoft.com...
> Shoot, I glossed right over that param when looking at BOL. Thanks guys
> for
> pointing it out. In fact I'm rolling my own to do what you suggest in #2,
> do
> differentials 6 days a week and full on the 7th.
> BTW, it's nice that SQL Server can do differential backups. having said
> that
> they don't appear to be totally efficient. Each day our data stroe grows a
> little but the differential backup grows a lot.
> Ken
>
> "Russell Fields" wrote:
>> Ken,
>> Look at BACKUP DATABASE ... WITH INIT to write over the backup file.
>> Not having a reliable backup scares me, so I hate to see you depending on
>> such an approach. Here are a couple of options:
>> 1. Schedule a tape backup of your .BAK file during the day, between the
>> nightly backups.
>> 2. Weekly BACKUP DATABASE ... WITH INIT, then nightly ... WITH
>> DIFFERENTIAL.
>> Then maybe you can fit a week's worth of backups on the disk. (Do the
>> database backup on the weekend if nothing much is happening then. Then,
>> if
>> it fails, you can get a second shot at it before a lot of work happens.)
>> 3. But better is to buy enough disk space for the needed backups. Use a
>> maintenance plan (whether SQL Server Maintenance Plan or one you roll
>> yourself) that will name each file with the datetime and a plan to delete
>> the backups that are older than you want to keep.
>> RLF
>>
>> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
>> news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
>> > Hello. Take this simple backup statement:
>> >
>> > Backup Database MyDB To Disk =>> > N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
>> > With Name = 'MyDB full backup'
>> >
>> > Would you think that if MyDB.bak already existed it would be
>> > overwritten
>> > with the new backup? Or would it double in size, effectively containing
>> > 2
>> > backups? I've seen it both ways I think. I ask because I'm trying to
>> > backup
>> > this monolithic DB each night and we're running out of disk space on
>> > this
>> > command.
>> >
>> > I've seen some talk around here of backup compression utilities. In
>> > lieu
>> > of
>> > that would it make sense to schedule simple batch job that deletes
>> > MyDB.bak
>> > before proceeding with the backup? I realize we could be left
>> > vulnerable
>> > if
>> > the subsequent backup should fail.
>> >
>> > TIA,
>> > Ken
>>|||In addition to what Russell stated the Diff's backup at the Extent (64K)
level not the row. Meaning that if 1 bit changes on the extent you get the
entire extent in the Diff backup. So they can tend to be much larger than
the changes would imply.
If you are running out of disk space for backups I suggest you purchase one
of the 3rd party backup compression utilities. They start as low as a few
hundred $ and can compress the backups as much as 80% or more. Not to
mention they are generally faster than native backups as well.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23KNsytz6HHA.2752@.TK2MSFTNGP06.phx.gbl...
> Ken,
> Differentials are cumulative. So, assuming a Sunday full backup and daily
> differentials, the Wednesday differential contains the Monday, Tuesday,
> and Wednesday changes. If you know that you never have to go back in time
> more than a couple of days, you can start timing them out.
> RLF
>
> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
> news:88947E5E-4D56-4CCA-9114-C934A18F14B0@.microsoft.com...
>> Shoot, I glossed right over that param when looking at BOL. Thanks guys
>> for
>> pointing it out. In fact I'm rolling my own to do what you suggest in #2,
>> do
>> differentials 6 days a week and full on the 7th.
>> BTW, it's nice that SQL Server can do differential backups. having said
>> that
>> they don't appear to be totally efficient. Each day our data stroe grows
>> a
>> little but the differential backup grows a lot.
>> Ken
>>
>> "Russell Fields" wrote:
>> Ken,
>> Look at BACKUP DATABASE ... WITH INIT to write over the backup file.
>> Not having a reliable backup scares me, so I hate to see you depending
>> on
>> such an approach. Here are a couple of options:
>> 1. Schedule a tape backup of your .BAK file during the day, between the
>> nightly backups.
>> 2. Weekly BACKUP DATABASE ... WITH INIT, then nightly ... WITH
>> DIFFERENTIAL.
>> Then maybe you can fit a week's worth of backups on the disk. (Do the
>> database backup on the weekend if nothing much is happening then. Then,
>> if
>> it fails, you can get a second shot at it before a lot of work happens.)
>> 3. But better is to buy enough disk space for the needed backups. Use a
>> maintenance plan (whether SQL Server Maintenance Plan or one you roll
>> yourself) that will name each file with the datetime and a plan to
>> delete
>> the backups that are older than you want to keep.
>> RLF
>>
>> "ktrock" <ktrock@.discussions.microsoft.com> wrote in message
>> news:39070F38-0083-4253-B29C-BEBFEFBE6915@.microsoft.com...
>> > Hello. Take this simple backup statement:
>> >
>> > Backup Database MyDB To Disk =>> > N'p:\Backup-MSSQL$MySqlInstance\MyDB.BAK'
>> > With Name = 'MyDB full backup'
>> >
>> > Would you think that if MyDB.bak already existed it would be
>> > overwritten
>> > with the new backup? Or would it double in size, effectively
>> > containing 2
>> > backups? I've seen it both ways I think. I ask because I'm trying to
>> > backup
>> > this monolithic DB each night and we're running out of disk space on
>> > this
>> > command.
>> >
>> > I've seen some talk around here of backup compression utilities. In
>> > lieu
>> > of
>> > that would it make sense to schedule simple batch job that deletes
>> > MyDB.bak
>> > before proceeding with the backup? I realize we could be left
>> > vulnerable
>> > if
>> > the subsequent backup should fail.
>> >
>> > TIA,
>> > Ken
>>
>