SQL Server - How full backup works

Asked By ktroc

30-Aug-07 01:32 PM

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

30-Aug-07 01:43 PM

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

30-Aug-07 01:45 PM

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

30-Aug-07 01:47 PM

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

30-Aug-07 02:04 PM

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

30-Aug-07 03:24 PM

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

30-Aug-07 05:46 PM

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