Quick Backup & Restore SQL Server

Version : 1.0
Date : 20/07/2011
By : Albert van der Sel

Main Contents:

1. BACKUP & RESTORE OF REGULAR USER DATABASES
2. BACKUP & RESTORE OF SYSTEM DATABASES
3. WHAT IF THE TRANSACTION LOG IS FULL


1. BACKUP & RESTORE OF REGULAR USER DATABASES:


At least, the following backup policies are possible with regards to regular user databases:

  1. Full backups only, which are always consistent. Such a backup can be restored to get the database back
    to the situation at the time the backup was created.
    This can always be done with any database "recovery mode" (Full, Simple, Bulklogged)

  2. Full backup in combination with later Transactionlog backups. The Transaction log backups, contain
    the changes with respect to the former backup (whether that's a full- or transactionlog backup).
    In case of a restore action, restore the Full first, and then restore all subsequent transaction log backups,
    from the first transaction log backup, all the way up to the latest backup.
    Note: The database needs to use the "Full recovery model" (which is somewhat comparable to "archive mode" in Oracle).

  3. Full backup in combination with later Differential backups. The Differential backups, contain
    the changes with respect to the last FULL backup.
    Later differential backups will thus grow in size. In case of a restore action, restore the Full backup first,
    and then ONLY the most recent Differential backup.

    This policy works best when the database was set in SIMPLE mode.
    However, it can be used in any database mode, however. When de mode is set to Full, you must make (some)
    transactionlog backups as well (otherwise the Tlog keeps growing and growing).
Make sure that you understand the database FULL and SIMPLE recovery modes.

1.1 Creating a FULL database backup and restore it:

- Example Full backup:

BACKUP DATABASE TEST TO DISK='d:\backups\test_full.dmp' WITH INIT

- Example restore of a full backup (and not restoring other backups):

RESTORE DATABASE TEST FROM DISK='d:\backups\test_full.dmp' WITH REPLACE, RECOVERY

The "RECOVERY" clause means that this is your only, or last, backup to restore, and afterwards the database needs
to be recovered and opened.


1.2 Creating a FULL database backup and subsequential DIFFERENTIAL backups:

- Example backups: first the full at e.g. 01:00h am, then a number of diffs during the day.

BACKUP DATABASE TEST TO DISK='d:\backups\test_full.dmp' WITH INIT -- at 01:00h

BACKUP DATABASE TEST TO DISK='d:\backups\test_diff_0500.dmp' WITH DIFFERENTIAL, INIT -- at 05:00h

BACKUP DATABASE TEST TO DISK='d:\backups\test_diff_0900.dmp' WITH DIFFERENTIAL, INIT -- at 09:00h

BACKUP DATABASE TEST TO DISK='d:\backups\test_diff_1100.dmp' WITH DIFFERENTIAL, INIT -- at 11:00h

BACKUP DATABASE TEST TO DISK='d:\backups\test_diff_1300.dmp' WITH DIFFERENTIAL, INIT -- at 13:00h

BACKUP DATABASE TEST TO DISK='d:\backups\test_diff_1700.dmp' WITH DIFFERENTIAL, INIT -- at 17:00h

Important: A differential backup contains all the delta's with respect to the last full backup.
So, differential backups taken at a later time, are expected to be larger compared to earlier diff backups.


1.3 Restoring a FULL database backup followed by a restore of (only) the latest DIFFERENTIAL backup:

First you restore the full with the "WITH NORECOVERY" clause, then ONLY restore the LATEST differential backup
using the "WITH RECOVERY" clause.

RESTORE DATABASE TEST FROM DISK='d:\backups\test_full.dmp' WITH REPLACE, NORECOVERY -- first restore the full backup

RESTORE DATABASE TEST FROM DISK='d:\backups\test_diff_1700.dmp' WITH RECOVERY -- then restore only the latest diff backup

The above example assumes a crash happened after 17:00h.
In that case, use only the full from 01:00h and the diff backup from 17.00h.

Suppose a crash happened at 11.45h.
In that case, use only the full from 01:00h and the diff backup from 11.00h.

Note: this backup/restore policy is independent from your database "recovery model", like "full" or "simple" or "bulk logged".
So, you can ALWAYS use this backup/restore policy.


1.4 Creating a FULL database backup and subsequential TRANSACTION LOG backups:

IMPORTANT: the following backup policy only works if your database uses the Full "recovery model"

- Example backups: first the full at e.g. 01:00h am, then a number of TRANSACTION LOG backups during the day.

BACKUP DATABASE TEST TO DISK='d:\backups\test_full.dmp' WITH INIT -- at 01:00h

BACKUP LOG TEST TO DISK='d:\backups\test_log_0500.dmp' WITH INIT -- at 05:00h

BACKUP LOG TEST TO DISK='d:\backups\test_log_0900.dmp' WITH INIT -- at 09:00h

BACKUP LOG TEST TO DISK='d:\backups\test_log_1100.dmp' WITH INIT -- at 11:00h

BACKUP LOG TEST TO DISK='d:\backups\test_log_1300.dmp' WITH INIT -- at 13:00h

BACKUP LOG TEST TO DISK='d:\backups\test_log_1700.dmp' WITH INIT -- at 17:00h

(Note: database TEST needs to use the Full recovery model)

A Transaction log backup, contains the "delta's" compared to the last former backup, whether that was
a Full backup, or a transaction log backup.
So, at a restore action, you need to apply all your transaction log backups that are available.


1.5 Restoring a FULL database backup followed by a restore of ALL subseqential Transaction Log backups:

Suppose that, using the model schetched in section 1.4, a crash occurred at 11.45h.
What backups would you use to restore your database?
Since a transaction log backup, contains only the changes relative to the direct former backup, this time you
need to restore the full backup, followed by ALL applicable transaction log backups.
This is different from the differential policy, where you only needed the full- and the last differential backup.
So in this case we procede as follows:

RESTORE DATABASE TEST FROM DISK='d:\backups\test_full.dmp' WITH REPLACE, NORECOVERY

RESTORE LOG TEST FROM DISK='d:\backups\test_log_0500.dmp' WITH NORECOVERY

RESTORE LOG TEST FROM DISK='d:\backups\test_log_0900.dmp' WITH NORECOVERY

RESTORE LOG TEST FROM DISK='d:\backups\test_log_1100.dmp' WITH RECOVERY

Note: only the last restore action needs the "WITH RECOVERY" clause.

Note: If the database was not fully destroyed, and it was possible to backup the transaction log
at 11.45h (just after the crash), it would in principle be possible to backup the "tail" of the transaction log,
thereby saving all delta's between 11.00h and 11.45h.
But in most cases, it's a bit hypothetical.


1.6 Restoring to a different location:

let's show you how to restore a database to different location. That is, the filesystems
and path to where the database must be restored to, is different from the original filesystems and/or paths.

Please see the following example. In this RESTORE statement, the MOVE option tells SQL Server
the new location to where the files (which information is stored in the backup) need to be placed at the restore.

RESTORE DATABASE TEST FROM DISK='L:\temp\test_full.bak' WITH RECOVERY,
MOVE 'test_data_1' TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_data_1.mdf',
MOVE 'test_index_1' TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_index_1.ndf',
MOVE 'test_data_2' TO 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_data_2.ndf',
MOVE 'test_Log' TO 'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_Log.ldf', REPLACE

One problem often seen here, is that you do not know the logical names, like for example the name 'test_data_1'.
For an existing database, the logical and physical filenames are easy to find from the sysfiles view, like
using the query "select * from sysfiles".

Also, to retrieve logical and physical names from the backupfile itself, you can use the RESTORE FILELISTONLY command.

So, suppose you have a backupfile like d:\backups\test_full.bak, then you can retrieve the names using:

RESTORE FILELISTONLY from disk='d:\backups\test_full.bak'


1.7 Backup information recorded in the MSDB Database:

The MSDB database also contain historical information about any backups that were made.
Especially the system tables "backupset" and "backupmediafamily" carry interesting information,
as the below queries will make clear:

-- adjust the date in the below queries as you see fit.

SELECT substring(s.database_name,1,20) as "database", (s.backup_size/1024/1024) as "Size_in_MB", s.type,
s.backup_start_date, s.backup_finish_date, substring(f.physical_device_name,1,30)
FROM backupset s, backupmediafamily f
WHERE s.media_set_id=f.media_set_id
AND s.backup_start_date > '2011-05-05'
ORDER BY s.backup_start_date

SELECT backup_start_date, backup_finish_date, media_set_id,
type, substring(database_name,1,20)
FROM backupset

SELECT backup_start_date, backup_finish_date, media_set_id,
type, database_name
FROM backupset
WHERE backup_start_date>'2011-05-05'



2. BACKUP & RESTORE OF SYSTEM DATABASES:


A SQL Server instance, has 4 socalled "system databases". These are:

- master database: contains metadata about the instance, databases, logins, and much more.
- msdb database: contains jobdescriptions, history information on jobs, backups, replication etc..
- model database: can function as a template for new databases, but not much dba's use it.
- tempdb database: functions as a temporary workspace for sort operations, temp tables, index rebuilds etc..

2.1 Backup of the system databases:

You do not need to backup the tempdb database. For example, if the tempdb database files gets lost for some reason,
SQL Server will simply create the tempdb database again at startup. But if that happens,
you still need to check the sizes and the number of files, in order to check whether
tempdb is still according to your specifications.

The other system databases (master, model, msdb) are critical for proper operation.
These databases usually will be (and remain) quite small, in most cases less than 30 MB or so. So, creating backups
should be a matter of seconds.

You should only make full backups of these databases. As said above, the databases are very small and the backups
will not occupy much diskspace (if you would backup to disk, which is recommended).

Suppose that you would backup these databases to the "F:\sqlbackups" disk location. An example of backupcommands then
could be as simple as this:

BACKUP DATABASE master TO DISK='F:\sqlbackups\master.dmp' WITH INIT

BACKUP DATABASE msdb TO DISK='F:\sqlbackups\msdb.dmp' WITH INIT

BACKUP DATABASE model TO DISK='F:\sqlbackups\model.dmp' WITH INIT


2.2 Restore of the system databases:

When you want to restore a regular "user" database (like for example the database "sales"), the SQL Server instance
can be running in the usual "multi-user" mode, so no special preperation is neccessary with respect to the state of the instance.

It's different when you need to restore a system database. In this case, if you have lost a system database, your instance
will not start anyway. But you can start it in "single user mode", which also allows you to restore a system database.
To start the instance in single user mode, you need to use the "/m" parameter from the command line.
Here is an example of a restore of the master database:

NET START "MSSQLSERVER" /m

Next, connect with SQLCMD or the management studio, and use the following TSQL command:

RESTORE DATABASE MASTER FROM DISK='F:\sqlbackups\master.dmp' WITH REPLACE, RECOVERY
GO

For a named instance, the sqlcmd connect command must specify the -SComputerName\InstanceName option.

Please also remember that no other service or program may have a connection to your instance.
So, before you use the upper commands, be sure that all other services and programs that may connect to SQL server, are down.

2.3 A few notes on TEMPDB:

Usually, if the files of the TEMPDB database were lost, at a restart of the SQL Server service,
they should be recreated.
So, usually, there should be no problem.

You should also know that you do not need to backup the TEMPDB database, and you even can't:

backup database tempdb to disk='c:\tempdb\tempdb.bak' with init

Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.


However, sometimes problems do occur, and you want the Service to create the TEMPDB files at another location.

If you just want to move the TEMPDB files to another location, for example, for performance reasons,
while there are no problems, then use statements similar to:

alter database tempdb
MODIFY FILE (NAME = 'tempdev', FILENAME = 'H:\tempdb\tempdev.ldf')
GO

alter database tempdb
MODIFY FILE (NAME = 'templog', FILENAME = 'H:\tempdb\templog.ldf')
GO

(just repeat that for all the files which make up the tempdb database.)

Ofcourse, in the above statements, the H: drive is just an example.

Now, if there seems to be a problem, and the service won't start because somehow it cannot create the TEMPDB database,
then check this first:

- Are the permissions on the filesystem/path changed?
- Is the sqlservice account changed, and lacking permissions?
- Is there sufficient space for TEMPDB on the default location?

If all of the above seems OK, then you might try this:

- Start SQL Server is single user mode (as shown in section 2.2)
- Make sure no other services can connect to SQL Server (like the Agent etc..)
- Start the command utility "sqlcmd"
- Use the above "alter database tempdb" statements, and let the files point to a location of which you are sure
there cannot be a problem.
- Stop and start the service again, in normal mode.



3. WHAT IF THE TRANSACTION LOG IS FULL:


If your database uses the "Simple" recovery model, you won't run into this problem so fast.
But if the "Full" recovery model is used, in some cases, when for example large batch loads are used,
you might end up in a situation where the Transaction log is completely full.

In a production situation, this really could be a miserable situation.
Suppose you see no way to expand the log, and/or you do not have extra diskspace, and the load job is
already broken.

The following hints are actually bad advice, since you probably have a backup policy in place, using
a Full backup in combination with one ore more (usually more) Transaction Log backups.
If you use the following commands, you "break" that chain, and afterwards you must create a new Full backup again,
and create Transaction log backups afterward, using your normal policy. In effect: you need to start a new backup cycle.

In any case, if there are no other alternatives, and you are stuck with a full log, then:


SQL 2008:

BACKUP LOG YOURDATABASENAME TO DISK='NUL:'

Here you use a fake backup disk device, so to speak, but your log will be cleared.
But note that the actual log file(s) still have the same size: they will not be shrinked.
But, they are (near) empty again, so you can proceed using the database again.


Older Versions:

BACKUP LOG YOURDATABASENAME WITH TRUNCATE_ONLY