Backup and Restore Database in SQL Server 2005

Backing up data

Typically, you use scripts to create backups. In this section, I briefly describe

how to back up a database.

 

Note: Before a backup, SQL Server 2005 automatically creates a checkpoint

so that the data you back up contains all changes to database pages.

To back up a database, use the BACKUP DATABASE statement. You specify

the database to be backed up and the destination it is to be backed up to. For

example, to back up to a hard drive, use the following T-SQL code (which

assumes you've formatted the destination drive):

 

USE myDatabaseName

GO

BACKUP DATABASE myDatabaseName

TO DISK = 'C:\filePath\myDatabaseName.bak'

NAME = 'The name of the back up.'

 

Checking backups

I give this topic a separate section because it is crucially important and all

too easy to overlook. You must check that you can restore from the backup

tapes or other backup media. Just imagine — you've been backing up faithfully

for months, secure in the knowledge that you're safe if disaster happens,

only to discover that you can't use the backups.

 

The longer you leave between checking that you can restore a backup, the

greater the possibility that none of the backups are usable! Therefore, you

should frequently check that you can restore your backup tapes or other

backup media. Bypassing this crucially important task is false economy.

The exact frequency that you choose to check backups depends on your situation.

The more important the data, the more often you need to confirm that

you can restore from tapes or other media.

 

Restoring data

Backups are only useful if you know how to restore the data. You can carry

out a simple full restore by using T-SQL code like the following:

 

RESTORE databaseName

FROM DISK = 'C:\filePath\backupFilename'