Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jul 3, 2002

SQL Server 2000 Administration in 15 Minutes a Week: Restoring and Recovering Databases - Page 3

By Michael Aubert

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.


» See All Articles by Columnist Michael Aubert

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM