Backup to file - why NOFORMAT and NOSKIP?

[JJ]

What good would the NOFORMAT and NOSKIP options be on a scheduled backup to
a file?

I'm coming into this situation later in the game, but a third party had set
up jobs in SQL Server to back up the databases on a server, using the
commands:

BACKUP DATABASE [dbname] TO DISK = N'D:\backup\DBNAME.BAK'
WITH INIT, NOUNLOAD, NAME = N'PRODUCTION backup', NOSKIP , STATS =
10, NOFORMAT

Each backup goes to a different file. It is then backed up to tape, but not
deleted from the hard drive.

Recently, the drive where the backups were written ran out of disk space and
it appears that the DBNAME.BAK file was (apparently) corrupted. The backup
command would no longer run after that happened, terminating with an error -
and of course, no one was checking the SQL Server logs until I happened to
look at it for another purpose a week later. The error was due to some
problem with the header in the .BAK file, and it was looking at the header
because of the NOFORMAT and NOSKIP options. Deleting the corrupted .BAK
file got the backup working again.

It would appear to me that the NOSKIP and NOFORMAT options serve no purpose
if we want it to overwrite the backup file each time no matter what. Am I
correct or am I missing something?

[Hari Prasad]

Hi,

NOFORMAT and NOSKIP are used when you backup directly to tape. For disk file
backup both the options are not required.
WITH INIT option in BACKUP DATABASE command is used to overwrite the backup
file.

Thanks
Hari
SQL Server MVP

Post by JJ
What good would the NOFORMAT and NOSKIP options be on a scheduled backup
to a file?
I'm coming into this situation later in the game, but a third party had
set up jobs in SQL Server to back up the databases on a server, using the
BACKUP DATABASE [dbname] TO DISK = N'D:\backup\DBNAME.BAK'
WITH INIT, NOUNLOAD, NAME = N'PRODUCTION backup', NOSKIP , STATS =
10, NOFORMAT
Each backup goes to a different file. It is then backed up to tape, but
not deleted from the hard drive.
Recently, the drive where the backups were written ran out of disk space
and it appears that the DBNAME.BAK file was (apparently) corrupted. The
backup command would no longer run after that happened, terminating with
an error - and of course, no one was checking the SQL Server logs until I
happened to look at it for another purpose a week later. The error was
due to some problem with the header in the .BAK file, and it was looking
at the header because of the NOFORMAT and NOSKIP options. Deleting the
corrupted .BAK file got the backup working again.
It would appear to me that the NOSKIP and NOFORMAT options serve no
purpose if we want it to overwrite the backup file each time no matter
what. Am I correct or am I missing something?

[Sophie Guo [MSFT]]

Hello,

You can refer to the topic BACKUP in SQL server Books Online(BOL):

-------------------------------
NOFORMAT

Specifies the media header should not be written on all volumes used for
this backup operation and does not rewrite the backup device unless INIT is
specified.

INIT

Specifies that all backup sets should be overwritten, but preserves the
media header. If INIT is specified, any existing backup set data on that
device is overwritten.

The backup media is not overwritten if any one of the following conditions
is met:

** All backup sets on the media have not yet expired. For more
information, see the EXPIREDATE and RETAINDAYS options.


***The backup set name given in the BACKUP statement, if provided, does
not match the name on the backup media. For more information, see the NAME
clause.
Use the SKIP option to override these checks. For more information
about interactions when using SKIP, NOSKIP, INIT, and NOINIT, see the
Remarks section.

Note If the backup media is password protected, SQL Server does not write
to the media unless the media password is supplied. This check is not
overridden by the SKIP option. Password-protected media may be overwritten
only by reformatting it. For more information, see the FORMAT option.

NOSKIP

Instructs the BACKUP statement to check the expiration date of all backup
sets on the media before allowing them to be overwritten.


-------------------------------

Fore more inforamtion, refer to the BACKUP topic in BOL.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.