SQL Server 2000 Backup and Recovery Exercises

Choose an Exercise Below or From the Menu to the Left

Create Backup Device

This exercise shows you how to create a backup device using the GUI and with T-SQL. A backup device makes it much easier to organize your backups and to prepare for backup and recovery operations. Being able to create backup devices is essential in SQL Server 2000.

Database Backup

This exercise shows how to conduct a basic database backup using the GUI and how to perform the SQL Server 2000 Backup using T-SQL. You'll also learn how to schedule your backups so they'll run automatically.

Lost Database - Simple

This exercise explains what to do if you have lost your SQL Server 2000 database and your database is configured for the simple recovery model. A failure is simulated, then the restore occurs. The exercise example is presented both with GUI and T-SQL.

Lost Database - Full

This exercise explains what to do if you have lost your SQL Server 2000 database and your database is configured for the full recovery model. A failure is simulated, then the restore occurs using more complex methods than those in the simple recovery model. A database backup, differential backup, and transaction log backup are used to restore the database. The exercise example is presented both with GUI and T-SQL.

Lost Primary - Full

If you lose your primary data file and you're using the full recovery model, this exercise will help you restore the datafile through both GUI and T-SQL.

Lost Secondary - Full

The loss of a secondary data file is a serious problem, and this exercise provides step by step examples how to get the data file back through GUI and T-SQL.

Restore master

This is a very troubling problem, because without your master database, SQL Server won't start. This exercise explains how to rebuild the master database so that SQL Server 2000 will start and you can get back to work again.

Restore msdb

This is one of the more popular pages on the website. The msdb database stores a lot of key information about backups and jobs and isn't a database that you want to lose track of. This exercise covers in depth - with both GUI and T-SQL - how to recover from an msdb disaster in SQL Server 2000.

Restore model, tempdb

This exercise explains how to restore your model database - which you should be backing up periodically. It also discusses why there is no need to perform backups of tempdb in SQL Server 2000.

Attach and Detach DB

This exercise shows in depth how to successfully detach a database and re-attach it. If you're looking to move or copy a SQL Server database, detach and attach can be a very effective tool to accomplish the operation.

Point in Time Recovery

One of the more technical backup and recovery tasks is to bring the database back to a given point in time. This exercise shows how you can restore to a precise point in time using database and transaction log backups.

Using Marks

Marks can be used - usually within a development environment - to recover up to a point when you set a mark in the database.

DBCCCHECKDB

DBCCCHECKDB is a command that wise DBAs will use to ensure the integrity of the database prior to taking a backup.

Verifying Backups

When you take a backup in SQL Server 2000, you have no sure way of knowing that the write went as planned and that the files on the backup storage device are in fact readable. By verifying the database, you'll know that you can count on those backups in case you need them to perform a restore.

Restricted Modes

When performing backup and recovery operations, you'll want to restrict user access to the database in many cases. This exercise explains the SQL Server 2000 Database restricted modes and how you can utilize them in your backup and recovery operations.

Alter Recovery Model

The recovery model of a database will generally stay the same throughout its lifetime as it is established during the planning stages before a database is ever implemented. If you ever need to alter the recovery model for your database, this exercise shows you how to accomplish that task.

Stored Procs

This section presents two simple system sprocs that can assist you with backup and recovery operations