Database Recovery Models
Other backup options
There are a few other backup
options/strategies that I wanted to quickly cover before we
move on in the series.
First, SQL Server allows for
backup to multiple devices at once, which can speed up the
time it takes to backup/restore to/from tape. The screen
below shows two files (however it could just as easily be
two tape drives) used to backup the EXBackup database. Note
that you will need both files if you ever need to restore
the backup.
Click to Enlarge
(Note: To access the " SQL Server Backup" screen in
Enterprise Manager, first right click the database you would like
to backup up, then select "All Tasks..." > "Restore
Database.")
There are also a few options I think are important to know
on the "Options" tab:
"Verifying backup upon completion" will make sure you have
a good backup by reading the tape/file and checking for any
corruption of the data. The downside is that this can add a
significant amount of time to your backup.
"Remove inactive entries from transaction log" is
equivalent to the NO TRUNCATE option of the backup command.
If checked, entries in the log file(s) that are no longer
needed after a backup will be freed for reuse. If the box
is not checked, entries in the log file(s) that are no
longer needed after a backup will not be removed
(i.e. NO TRUNCATE) for the transaction log.
Also, the Media set name options allow you to give a tape a
name that must be the same in order to write to the tape.
Additionally, you can set an expiration date before a tape
can be overwritten (without a manual override). These
options keep you from accidentally overwriting data when
you use scheduled jobs.
------
Speaking of scheduled jobs, you may be wondering how the
heck you can edit/delete backup jobs that you have created.
You can find them under the "Management" folder of your SQL
Server, then "SQL Server Agent", and finally under "Jobs."
By right clicking a job you can do things like: view its
history (i.e. if it ran or not), start it manually, or view
its properties to set the schedule. Don't worry too much
about jobs right now as we will be looking at the SQL Server
Agent in much greater detail later in this series.
------
The last object relating to backups I want to cover
are "Backup Devices." So far I have been using physical
device names to make backups (ex: e:\backups\SAT.BAK);
however, you can also create and use logical device names
when creating a backup. The main benefit of using logical
devices is that it can help you manage your backups. Let's
create a Backup Device for the above file:
To create a Backup Device: Under the Management folder of
your SQL Server, right click "Backup" and select "New
Backup Device." The following screen appears:
In our example we could put SAT in the "Name" field and
then e:\backups\SAT.BAK for the "File Name."
Now when we backup a database (or add a Scheduled database
backup), we can now select "Backup device" and choose the
logical device we created that maps back to e:\backups\SAT.BAK.
------
One final, final, final note about backups...back up the
system databases! Objects like the server logons (that we
will look at here in the next few weeks) and information
such as what other databases are on that server, are/is
stored in the master database. Having a complete server
failure and only having backups of your user databases
is not the end of the world...but it could be a lot of work
to add all the lost server info again. So back up your
master, model, and msdb databases frequently, especially if
you have just made lots of management or configuration
changes.
Well that about raps up the
backup/restore process, but backups will be back (much)
later in this series...mainly a simpler way to manage them
and how to deal with backup/restores of individual database
files. Next week we are going to turn away from management for a
little bit and start looking at how to create tables and
other database objects. As always...If
you have any technical questions please post them on the SQL message board.
Please send any non-technical questions, comments, and feedback to
my email. I hope you are finding this
series to be a useful one, and I'm looking forward to your feedback.
Mike
maubert@databasejournal.com
www.2000trainers.com
»
See All Articles by Columnist Michael Aubert
» 
Comment and Contribute
