Tuesday, March 8, 2011

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
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'

1 comment :