Full Back Up With Understanding of Parameters Like,init,Format,Name,Stat

A simple T-SQL script for full backups

Take a look at Listing 3-8, which shows a script that can be used to take a full backup of the newly populated DatabaseForFullBackups database.

USE master GO BACKUP DATABASE [DatabaseForFullBackups] TO DISK = N'C:\SQLBackups\Chapter3\DatabaseForFullBackups_Full_Native_2.bak' WITH FORMAT, INIT, NAME = N'DatabaseForFullBackups-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO

Listing 3-8: Native full backup T-SQL script.

This script may look like it has some extra parameters, compared to the native GUI backup that we did earlier, but this is the scripted output of that same backup, with only the output file name modified, and a few other very minor tweaks. So what do each of these parameters mean?

The meaning of the first line should be fairly obvious; it is instructing SQL Server to perform a full backup of the DatabaseForFullBackups database. This leads into the second line where we have chosen to back up to disk, and given a complete file path to the resulting backup file. The remainder of the parameters are new, so let's go through each one.108 Chapter 3: Full Database Backups

                        FORMAT This option tells SQL Server whether or not to overwrite the media header infor­mation. The FORMAT option will erase any information in a backup set that already exists when the backup is initialized (NOFORMAT will preserve it).

                        INIT By default, when scripting a backup generated by the Backup wizard, this parameter will be set to NOINIT, which lets SQL Server know not to initialize a media set when taking the backup and instead append any new backup data to the existing backup set. However, since we adopt the rule of one backup per backup set, it's useful to use INIT instead, to make sure that, if a command gets run twice, we overwrite the existing set and still end up with only one backup in the set.

                        NAME The NAME parameter is simply used to identify the backup set. If it is not supplied, the set will not record a name.

                        SKIP Using the SKIP parameter will cause SQL Server to skip the expiration check that it normally does on the backup set. It doesn't care if any backups existing in the backup set have been marked for availability to be overwritten.

                        NOREWIND This parameter will cause SQL Server to keep a tape device open and ready for use when the backup operation is complete. This is a performance boost to users of tape drives since the tape is already at the next writing point instead of having to search for the correct position. This is obviously a tape-only option.

                NOUNLOAD When backing up to a tape drive, this parameter instructs SQL Server not to unload the tape from the drive when the backup operation is completed.

                        STATS This option may prove useful to you when performing query-based backups. The STATS parameter defines the time intervals on which SQL Server should update the "backup progress" messages. For example, using stats=10 will cause SQL Server to send a status message to the query output for each 10 percent of the backup completion.

As noted, if we wished to overwrite an existing backup set, we'd want to specify the INIT parameter but, beyond that, none of these secondary parameters, including the backup set NAME descriptor, are required. As such, we can actually use a much simplified BACKUP command, as shown in Listing 3-9.

BACKUP DATABASE [DatabaseForFullBackups] TO DISK = N'C:\SQLBackups\Chapter3\DatabaseForFullBackups_Full_Native_2.bak' GO

Listing 3-9: Slimmed-down native T-SQL backup code.

Go ahead and start Management Studio and connect to your test server. Once you have connected, open a new query