[
Source]
The article describes four different methods of backin up a SQL Server database; when to use them and why.by Joe Lax, SQL Server ProWhen I started using SQL Server in version 4.2, backups were relatively simple. There were only two possibilities: a full backup or a transaction log backup. Starting with SQL Server 7, additional choices have proliferated. You now have four ways to back up your database.
If you are responsible for maintaining SQL Server databases, you need to be familiar with these various options and know when to apply each backup type. In this 10-Minute Solution, I show you the commands to execute each different kind of backup and discuss how to choose among the various options you have.
[THE PROBLEM]How many different ways can you back up your SQL Server database?
[THE SOLUTION]SQL Server provides four different methods for backing up your database:
- A full backup makes a complete backup of your database. You will almost always need to start your backup strategy with a full backup of your database.
- A file backup is useful when your database is so large that a full backup would take too long.
- A transaction log backup creates a copy of all changes made to the database that are currently stored in the transaction log.
- A differential backup stores all changes that have occurred to the database since the last full backup.
It all starts with learning the basics of the BACKUP command.
Components of a SQL Server DatabaseBefore exploring each of the various options in backing up a SQL Server database, let's review how information is stored in one.
A SQL Server database stores data in one or more database files. While many databases only use a single file to store data, there are good reasons sometimes to split your data among several files. As an example, if you need the extra performance, you can store tables that are frequently used in their own separate files. By placing those files on separate disks, you avoid contention for disk access. Or your database might be too large to fit on one disk volume of your server and so must use more than one file—a separate file on each disk volume. If many files are being used for the database, the files themselves are sometimes organized into separate file groups, with each group consisting of several individual files.
Each SQL Server database must also have at least one file for the transaction log, which stores all changes that are made to the database. If you are unfamiliar with the use of the SQL Server transaction log, please see my previous 10-Minute Solution, "
Using SQL Server Recovery Models", for a more in-depth description.
1. Fully Backing Up the Whole Database:Regardless of how many files your database uses, you can create a complete backup of your database with one simple command:
BACKUP DATABASE [dbname] to [backup_device]For example,
BACKUP DATABASE pubs to disk = 'c:\mssql\backup\pubs.bak'creates a complete backup of the pubs database using the file c:\mssql\backup\pubs.bak.
The database can remain online and accessible to users while this backup is being made. In order to take a consistent snapshot of the database, a copy of the transaction log is also included with the database backup.
In the backup file, SQL Server stores the names and locations of the files actually used in the database. Upon restoring the database, SQL Server recreates all the necessary files, as many as there might be. A database thus restored is equivalent to the point in time that the backup finished.
A complete database backup is very simple to execute and use in recovery. If your data doesn't change often, you might simply schedule a nightly full backup of your database. Even if you need more frequent backups, regularly scheduling a full database backup each hour might be sufficient (if your database is small enough).
2. Backing Up the Database One File at a Time:A complete database backup is certainly a powerful and simple option. In the case of a very large database, however, a complete backup might simply take too long to be feasible.
Consider a database used in a data warehousing application. The database might easily approach several hundred gigabytes in size and, as such, take too long to back up in one operation. In such a case, SQL Server provides the option of backing up individual files or file groups.
For instance, this command will back up an individual file:
BACKUP DATABASE database_name
FILE = file_name
TO DISK = 'file_for_backup'This command will back up all files in a particular group:
BACKUP DATABASE database_name
FILEGROUP = file_group_name
TO DISK = 'file_for_backup'With these commands, you can set up a schedule where a different file or file group is backed up every evening, completing a full cycle in a week.
Besides shortening the length of time it takes to create a backup, a file backup can also make recovery quicker. If only a single file in your database has been lost, a file backup will allow you to simply restore that file. However, it is important to note that a file backup cannot be used by itself. In order for the database to be recovered to a consistent state (where all the files in the database represent the same moment in time), all transaction logs (see next section) made since the file backup was created must also be applied.
Listing 1 creates a test database with two file groups.
Following the CREATE statement are two backup commands.
The first backs up only the test1 file;
the second backs up all files in the primary file group.
create database test
on primary
(name = test1,
filename = 'e:\mssqlserver\mssql$joelax2000_1\data\test1.mdf'),
(name = test2,
filename = 'e:\mssqlserver\mssql$joelax2000_1\data\test2.ndf'),
filegroup group2
(name = test3,
filename = 'e:\mssqlserver\mssql$joelax2000_1\data\test3.ndf'),
(name = test4,
filename = 'e:\mssqlserver\mssql$joelax2000_1\data\test4.ndf')
log on
(name = test_log,
filename = 'e:\mssqlserver\mssql$joelax2000_1\data\test_log.ldf')
First backup command:
backup database test
file = 'test1'
to disk = 'e:\test1.bak'
Second backup command:
backup database test
filegroup = 'primary'
to disk = 'e:\primary.bak'3. Backing Up Only the Changes to the Database:In addition to full backups, SQL Server provides two methods for backing up only the changes that have occurred to the database.
Transaction Log Backup:
This command creates a copy of all the transactions in the transaction log at the time the backup was started:
BACKUP TRAN [database_name] to disk = [file_name]Once the backup is completed, all committed transactions are removed from the log. A typical backup plan utilizing transaction backups might be to make a full backup of the database once a night (or even once a week on larger databases) and back up the transaction logs as often as every ten minutes. To recover the database under this scheme, you would start from the last full backup and apply all the transaction log backups since.
This type of scheme suffers from one problem, however. You need the full backup and every transaction log backup made up to the point when you have to recover. For example, suppose your backup scheme consists of a full database backup every Saturday at midnight and transaction log backups every hour. If your database has a problem on Thursday at midnight, you would need to restore the full database backup, as well as the 120 transaction log backups made from Saturday through Thursday, to get up to date.
For this reason, SQL Server provides yet another method of backing up changes to the database that is used in conjunction with full database and transaction log backups.
4. Differential Backup:A differential backup creates a copy of all the changes that have taken place in the database since the last full backup. The command is the same for a full database backup, with the addition of one clause:
BACKUP DATABASE [database_name] to disk = 'file_name' with differentialNow you can implement the following backup scheme: a full backup every Saturday, a differential backup every night, and a transaction log backup every hour. Once this scheme is in place, if you go down on Thursday evening, all you need to do is apply the full backup, the last differential backup (which would have been Wednesday evening), and all the transaction logs since—for a total of only 26 files.
This 10-Minute Solution covers the basics of the BACKUP command and the four types of backups that you can create. However, I haven't discussed some other additional features that SQL Server provides with this command, such as creating a backup device or password protection. Before implementing a backup plan, I suggest you perform additional research on these features by reading up on the BACKUP command in the Books Online manual. Additionally, you should remember to test your backup plan thoroughly by performing test restores on a regular basis.
Joe Lax has spent the last 10 years working in various database environments and has been a practicing DBA on all versions of SQL Server from version 4.2 onward. Joe is also a MCSE and an MCT. Recently, he has started to learn Oracle, which affords him no end of fun.
Restore a MSSQL DatabaseThe syntax for using T-SQL commands is as follows:
RESTORE DATABASE { database_name | @database_name_var }
[ FROM <> [ ,...n ] ]
[ WITH
[ RESTRICTED_USER ]
[ [ , ] FILE = { file_number | @file_number } ]
[ [ , ] PASSWORD= { password | @password_variable } ]
[ [ , ] MEDIANAME= { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD= { mediapassword | @mediapassword_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] { NORECOVERY | RECOVERY | STANDBY =undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] REPLACE ]
[ [ , ] RESTART ]
[ [ , ] STATS [ =percentage ] ]Example:ALTER DATABASE MYPAY
SET MULTI_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE MYPAY
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE MYPAY
FROM disk = 'c:\abc.bak';Note: to avoid Database in use Problem.
ALTER DATABASE MYPAY
SET MULTI_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE MYPAY
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
