Category Archives: Backups

Backup all databases in SQL Server 2005 using SQL JOB


SQL script use to backup all our database. We use it with SQL job:

———————————————————————————————

DECLARE @sql VARCHAR(MAX)
SET @sql = ”
SELECT @sql = @sql+

BACKUP DATABASE [‘ + name + ‘] TO DISK = ”\\CLOUD\Root\Databases\’ + name + ‘_’ + convert(varchar(8),getdate(),112) + ‘.bak”’
FROM sys.databases
WHERE name not in (‘master’, ‘tempdb’, ‘model’, ‘msdb’, ‘pubs’, ‘Northwind’, ‘ReportServer$SQL2005′, ‘ReportServer$SQL2005TempDB’)
AND user_access_desc = ‘MULTI_USER’
AND is_read_only = 0
AND state_desc = ‘ONLINE’

–PRINT @sql
EXEC(@sql

———————————————————————————————

Advertisements

Backups , Recovery Models in SQL Server


Backups

Microsoft, in SQL Server Books Online, defines backups as:

A copy of data that is used to restore and recover data after a system failure

SQL Backups can be created a number of ways and can incorporate all or some of the data, as well as some part of the transaction log. While this article is focused on 2005 syntax, most of the concepts are applicable to 2000. This is a huge topic. At best, I’m going to scratch the surface and give you enough information so you won’t start crying again. After reading this, you should be able to set up a reasonable set of backups for your system.

Recovery Models

In order to begin working on backups, the business needs define a database recovery model. In essence, a recovery model defines what you’re going to do with the transaction log data.

There are three recovery models: Full, Simple and Bulk Logged. These are pretty easy to define:

Simple in simple recovery mode, the transaction log is not backed up so you can only recover to the most recent full or differential backup.

Full in full recovery mode you backup the database and the transaction log so that you can recover the database to any point in time.

Bulk Logged in bulk logged mode, most transactions are stored in the transaction log, but some bulk operations such as bulk loads or index creation are not logged.

The two most commonly used modes are Simple and Full. Don’t necessarily assume that, of course, you always need to use Full recovery to protect your data. It is a business decision. The business is going to tell you if you need to recover to a point in time or if you simply need the last full backup. It’s going to define if your data is recoverable by other means, such as manual entry, or if you have to protect as much as possible as it comes across the wire. You use Simple recovery if you can afford to lose the data stored since the last full or differential backup and/or you just don’t need recovery to a point in time. In Simple mode, you must restore all secondary read/write file groups when you restore the primary. You use Simple mostly on secondary databases that are not an absolute vital part of the enterprise or reporting systems, with read only access so there isn’t a transaction log to worry about anyway. You use Full if every bit of the data is vital, you need to recover to a point in time or, usually in the case of very large databases (VLDB), you need to restore individual files and file groups independently of other files and file groups.

With both Simple and full recovery models, you can now run a Copy-Only backup which allows you to copy the database to a backup file, but doesn’t affect the log, differential backup schedules or impact recovery to a point in time. I’ll try to drill down on as many of these topics as possible through the article, but not the files and filegroups.

 

Working with Simple Recovery

Enough talk. Let’s get down to running backups. Let’s assume that we’re in Simple recovery on a small to mid-sized database. I’m going to use AdventureWorks for all the sample scripts. To set it to simple recovery:

ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE

Your simplest backup strategy is to run, at regular intervals, the following SQL Server backup command, which will perform a full backup of the database:

BACKUP DATABASE AdventureWorks 
TO DISK = ‘C:\Backups\AdventureWorks.BAK’

 

What’s with all the typing you ask? Don’t we have GUI tools to handle the work for us? Yes, most simple backups can be performed using SQL Server Management Studio. However, if you want to learn and understand what Management Studio is doing for you, or if you want some fine grained control over what is backed up, how and where, then you’re going to have to break out the keyboard and put away the mouse.

The above command will precipitate a basic backup to disk. Most DBAs I know backup to file and then scrape the files onto a tape or some other media. This is because files on disk are simple and quick to recover, whereas media can sometimes be a bit of a pain. For example, we generally have two to three days worth of backups on our file systems for immediate recovery. We only go to the tape systems if we need to run restores for older backups.

What did that command do? It made a copy of all the committed data in the database. It also copied uncommitted log entries. These are used during recovery to either commit or rollback changes that occurred to the data during the backup process.

Copy-only backups

Normally, backing up a database affects other backup and restore processes. For example after running the previous command, any differential backups (a backup that only copies data changed since the last backup) would be using this as the starting point for data changes, not the backup you ran last night. As noted earlier, SQL 2005 introduces a new concept to backups, COPY_ONLY backups, which allow us to keep from interrupting the cycle:

BACKUP DATABASE AdventureWorks
TO DISK = ‘C:\Backups\AdventureWorks.bak’
WITH COPY_ONLY;

 

 

Already we’ve found one of those more granular moments when the Management Studio wouldn’t help you. If you want a copy only backup, you have to use the command line.

Differential backups

Let’s assume for a moment, that we’re still in simple recovery, but we’re dealing with a larger database, say something above 100 GB in size. Full backups can actually start to slow down the process a bit. Instead, after consultation with the business, we’ve decided to do a weekly full backup and daily differential backups. Differential backups only backup the data pages that have changed since the last full backup. Following is the SQL backup command to perform a differential backup:

BACKUP DATABASE AdventureWorks
TO DISK = ‘C:\backups\AdventureWorks.bak’ 
WITH DIFFERENTIAL

 

Now, if we had to restore this database, we’d first go to the last full backup, restore that, and then restore the differential backups in order (more on that later).

BACKUP DATABASE Adventureworks
TO DISK = ‘C:\backups\AdventureWorks.bak’
WITH INIT

 

There are a number of other backup options that I won’t be detailing here. Read the books online to see details on BLOCKSIZE, EXPIREDATE, RETAINDAYS, PASSWORD, NAME, STATS, and so on.

You can also run a statement that will check the integrity of a database backup. It doesn’t check the integrity of the data within a backup, but it does verify that the backup is formatted correctly and accessible.

RESTORE VERIFYONLY
FROM DISK = ‘C:\backups\Adventureworks.bak’

 

 

Full recovery and log backups

We’ve primarily been working on a database that was in Simple recovery mode (this used to be called Truncate Log on Checkpoint). In this mode, we do not backup the transaction logs for later recovery. Every backup under this mechanism is a database backup. Log backups are simply not possible.

However, you’ve only protected the data as of the last good backup, either full or differential. Let’s change our assumptions. Now we’re dealing with a large, mission critical application and database. We want to be able to recover this database up to the latest minute. This is a very important point. In theory, since the log entries are being stored and backed up, we’re protected up to the point of any failure. However, some failures can cause corruption of the log, making recovery to a point in time impossible. So, we have to determine what the reasonable minimum time between log backups will be. In this case we can live with no more than 15 minutes worth of lost data.

So, let’s start by putting our database in FULL recovery mode:

ALTER DATABASE AdventureWorks SET RECOVERY FULL

Then, on a scheduled basis, in this case every 15 minutes, we’ll run the SQL backup command for the transaction log:

BACKUP LOG Adventureworks
TO DISK = ‘C:\backups\AdventureWorks_Log.bak’;

This script will backup committed transactions from the transaction log. It has markers in the file that show the start and stop time. It will truncate the log when it successfully completes, cleaning out from the transaction log the committed transactions that have been written to the backup file. If necessary, you can use the WITH NO_TRUNCATE statement to capture data from the transaction log regardless of the state of the database, assuming it’s online and not in an EMERGENCY status. This is for emergencies only.

Note that we are not using the INIT statement in this case, but you can do so if you choose. When doing log backups, you’ve got options:

Run all the backups to a single file, where they’ll stack and all you have to do, on restore (covered later), is cycle through them.

Name the backups uniquely, probably using date and time in the string.

In that latter case, safety says, use INIT because you’re exercising maximum control over what gets backed up where, and you’ll be able to know exactly what a backup is, when it was taken and from where based on the name. This is yet another place where operating backups from the command line gives you more control than the GUI. We’ve used both approaches in our systems for different reasons. You can decide what is best for your technology and business requirements.

Most of the options available to the database backup are included in Log backup, including COPY_ONLY. This would allow you to capture a set of transaction data without affecting the log or the next scheduled log backup. This would be handy for taking production data to another system for troubleshooting etc.

If you have your database set to FULL Recovery, you need to run log backups. Sometimes, people forget and the transaction log grows to the point that it fills up the disk drive. In this case, you can run:

BACKUP LOG Adventureworks WITH NO_LOG;

Attaching NO_LOG to the log backup, and not specifying a location for the log, causes the inactive part of the log to be removed and it does this without a log entry itself, thus defeating the full disk drive. This is absolutely not recommended because it breaks the log chain, the series of log backups from which you would recover your database to a point in time. Microsoft recommends running a full backup immediately after using this statement. Further, they’re warning that this statement may be deprecated in a future release.

 

 

Restoring Databases

As important as SQL Server backups are, and they are vital, they are useless without the ability to restore the database.

 

Restoring a full database backup

Restoring a full database backup is as simple as it was to create:

RESTORE DATABASE Adventureworks
FROM DISK = ‘C:\Backup\AdventureWorks.bak’;

It’s really that simple – unless, as we we are backing up everything to a file as if it were a backup device. In that case, you’ll need to specify which file within the “device” you’re accessing. If you don’t know which file, you’ll need to generate a list:

RESTORE HEADERONLY
FROM DISK = ‘C:\Backup\Adventureworks.bak’;

This will give you the same list as I showed above from Management Studio. So now, if we wanted to restore the second file in the group, the COPY_ONLY backup, you would issue the following command:

RESTORE DATABASE AdventureWorks
FROM DISK = ‘C:\Backup\Adventureworks.bak’
WITH FILE = 2;

Unfortunately, if you’re following along, you may find that you just generated this error:

Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database “AdventureWorks” has not been backed up.
Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do
not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

What this means is, that your database is in full recovery mode, but you haven’t backed up the “tail of the log”, meaning the transactions entered since the last time you ran a backup. You can override this requirement if you change the previous syntax to:

RESTORE DATABASE AdventureWorks
FROM DISK = ‘C:\Backups\Adventureworks.bak’
WITH FILE = 2,
REPLACE;

That’s the first time we’ve stacked the WITH clauses (WITH FILE=2 and WITH REPLACE is represented as WITH FILE=2, REPLACE), but it won’t be the last. Read through the books online. Most of the WITH clause statements can be used in combination with the others.

What happens if we want to restore to a different database than the original? For example, we want to make a copy of our database from a separate backup. Maybe we want to move it down to a production support server where we are going to do some work on it, separate from the production copy of the database. If we take the simple approach, well, try this:

RESTORE DATABASE AdventureWorks_2
FROM DISK = ‘C:\Backups\Adventureworks.bak’
WITH FILE = 2;

In this case, you should see a whole series of errors relating to files not being overwritten. You really can create new databases from backups, but if you’re doing it on a server with the existing database, you’ll need to change the location of the physical files using the logical names. In order to know the logical names of the files for a given database, run this prior to attempting to move the files:

RESTORE FILELISTONLY
FROM DISK = ‘C:\Backups\Adventureworks.bak’
WITH FILE = 2;

 

This can then be used to identify the appropriate logical names in order to generate this script:

RESTORE DATABASE AdventureWorks_2
FROM DISK = ‘C:\Backups\Adventureworks.bak’
WITH FILE = 2,
   MOVE ‘AdventureWorks_Data’ TO ‘C:\backups\aw2_data.mdf’,
   MOVE ‘AdventureWorks_Log’ TO ‘C:\backups\aw2_log.ldf’;

 

 

Restoring a differential backup

The last method is to apply the differential backup. This requires two steps. First, we’ll restore the database, but with a twist and then we’ll apply the differential backup:

RESTORE DATABASE AdventureWorks
FROM DISK = ‘C:\Backups\Adventureworks.bak’
WITH FILE = 1 ,
   NORECOVERY,
   REPLACE;

RESTORE DATABASE AdventureWorks
FROM DISK = ‘C:\Backups\AdventureWorks.bak’
WITH FILE = 3;

Most of this is probably self-explanatory based on what we’ve already covered. The one wrinkle is the inclusion of the NORECOVERY keyword. Very simply, during a restore, transactions may have started during the backup process. Some of them complete and some don’t. At the end of a restore, completed transactions are rolled forward into the database and incomplete transactions are rolled back. Setting NORECOVERY keeps transactions open. This allows for the next set of transactions to be picked up from the next backup in order.

We’re mainly dealing with simple backups and restores in this article, but a more advanced restore in 2005 allows secondary file groups to be restored while the database is online. Its primary file group must be online during the operation. This will be more helpful for very large database systems.

 

 

Restoring SQL Server databases to a point in time

Restoring logs is not much more difficult than the differential database restore that we just completed. There’s just quite a bit more involved in restoring to a moment in time. Assuming you’re backing up your logs to a single file or device:

RESTORE HEADERONLY
FROM DISK = ‘C:\Backups\Adventureworks_log.bak’;

Otherwise, you simply go and get the file names you need. First run the database restore, taking care to leave it in a non-recovered state. Follow this up with a series of log restores to a point in time.

RESTORE DATABASE AdventureWorks FROM DISK = ‘C:\Backups\Adventureworks.bak’
WITH FILE = 1,
   NORECOVERY,
   REPLACE,
   STOPAT = ‘Oct 23, 2006 14:30:29.000’;

 

RESTORE LOG AdventureWorks
FROM DISK = ‘C:\Backups\Adventureworks_log.bak’
WITH FILE = 1,
   NORECOVERY,
   STOPAT = ‘Oct 23, 2006 14:30:29.000’;
RESTORE LOG AdventureWorks
FROM DISK = ‘C:\Backups\Adventureworks_log.bak’
WITH FILE = 2,
   NORECOVERY,
   STOPAT = ‘Oct 23, 2006 14:30:29.000’;

 

RESTORE LOG AdventureWorks
FROM DISK = ‘C:\Backups\Adventureworks_log.bak’
WITH FILE = 3,
   NORECOVERY,
   STOPAT = ‘Oct 23, 2006 14:30:29.000’;
RESTORE LOG AdventureWorks
FROM DISK = ‘C:\Backups\Adventureworks_log.bak’
WITH FILE = 4,
   STOPAT = ‘Oct 23, 2006 14:30:29.000’;

Now what we have is a database that is up to the exact, last committed transaction at 14:30:29 on the 23rd of October. Remember, during multi-step restores such as this, you have to leave the database in a recovering status. That means appending NORECOVERY to each statement until you’ve completed the restore process. If for some reason you’ve added NORECOVERY to all your statements, or you simply stop in the middle, and would like to bring the database back online, you can use this statement to complete the process:

RESTORE DATABASE Adventureworks
WITH RECOVERY;

 

Database snapshots

SQL Server 2005 introduced the concept of a snapshot, or a read-only, static view of a database. Snapshots are primarily created in order to supply a read-only version of a database for reporting purposes. However, they do function in a similar way to backups. The one primary difference is that all uncommitted transactions are rolled back. There is no option for rolling forward, capturing logs, etc., that backups provide, nor are very many SQL Server resources used at all. Rather, disk technology is used to create a copy of the data. Because of this they are much faster than backups both to create and restore.

 

A good use of snapshots, in addition to reporting, might be to create one prior to maintenance after you’ve already removed all the active users (and their transactions) from the system. While snapshots don’t support the volatility of live backups, their speed and ease of recovery make a great tool for quick recovery from a botched rollout. Snapshots are stored on the server, so you must make sure you’ve got adequate storage.

The syntax is different because you’re not backing up a database; you’re creating a new one:

CREATE DATABASE Adventureworks_ss1430
ON (NAME = AdventureWorks_Data,
FILENAME = ‘C:\Backups\AdventureWorks_data_1430.ss’)
AS SNAPSHOT OF AdventureWorks;

Now it will be accessible for read-only access. Since we’re primarily concerned with using this as a backup mechanism, let’s include the method for reverting a database to a database snapshot.

First, identify the snapshot you wish to use. If there is more than one on any database that you’re going to revert, you’ll need to delete all except the one you are using:

DROP DATABASE Adventureworks_ss1440;

Then you can revert the database by running a RESTORE statement (mixed metaphors, not good):

RESTORE DATABASE Adventureworks
FROM DATABASE_SNAPSHOT = Adventureworks_ss1430;
 

That’s it. On my system, running the database snapshots of Adventureworks took 136 ms. The full backup took 5,670 ms. The restore of the snapshot took 905ms and the database restore took 13,382ms. Incorporating this into a production rollout process could result in significant benefits

Again, it’s worth noting that there are some caveats to using the snapshot. You have to have enough disk space for a second copy of the database. You need to be careful dealing with snapshots since most of the syntax is similar to that used by databases themselves. Last, while there are snapshots attached to a database you can not run a restore from a database backup of that database.

SQL Server Database Backups


Database Backups :

 

Database backups are at the core of any SQL Server disaster recovery planning for any production system. Backups may be used to provide a means of recovery to a point-in-time when the database was last operational. Microsoft® SQL Server™ provides several types of backups that may be combined to formulate a customized disaster recovery plan depending on the nature of the data and the recovery requirements. It is highly recommended that all SQL Server databases be backed up periodically.

 

SQL Server backup media 

A database may be backed up to disk or to tape. The examples in this article assume a disk backup directly into a disk file (as opposed to a disk backup device). Any database can be backed up to a random disk file at any time. The file may either be initialized (using WITH INIT) or appended with the new backup.

 

Types of backups in SQL Server 

SQL Server provides several different kinds of backups including Complete, Differential, Transaction Log, and File(s) and Filegroup(s) backup.

A combination of these backups may be used to formulate a robust disaster recovery strategy. The following paragraphs explain each SQL Server backup type

 

 

Complete database backup 

A complete database backup creates a stand-alone image of the entire database. A complete database backup is self-dependent and may be restored to either the same or a new database on the same or a different server. This provides plenty of flexibility at the time when this backup has to be restored. A complete backup may be restored without the need for any other kind of backup. It may also be performed for databases in any recovery model. Restoring a complete database backup typically would be considered a starting point for a disaster recovery situation where the entire database is lost or damaged. It is recommended that a complete database backup be performed at regular intervals for all production databases. It is also recommended that a complete backup should be performed for system databases if there are any changes performed to the SQL Server operating environment such as creating or removing databases, configuring security, creating and modifying DTS/SSIS packages or scheduled jobs, adding and removing linked servers, etc.

 

 Backup syntax 

BACKUP DATABASE Northwind
TO DISK = ‘c:\backups\northwind.bak’
WITH INIT

Restore syntax (Same database)

RESTORE DATABASE Northwind
FROM DISK = ‘c:\backups\northwind.bak’

 

Restore syntax (New database and/or server)

 RESTORE DATABASE Northwind_new
FROM DISK = ‘c:\backups\northwind.bak’
WITH MOVE ‘northwind’ TO ‘c:\new_location\Northwind_new.mdf’
     MOVE ‘northwind_log’ TO ‘c:\new_location\Northwind_new_log.ldf’

 

 

Differential database backup 

A differential backup backs up only modified extents since the last complete backup. An extent is a group of 8 data pages each consisting of 8 KB (64 KB in total). By definition, differential backups are cumulative. The most recent differential backup contains all changes from all previous differential backups performed since the most recent complete database backup. Differential backups may be considered as an alternative for databases that are large and are modified infrequently. These would include data warehouse type of databases. Differential backups have several limitations including the following:

  • They do not provide point-in-time restore capabilities
  • They may only be restored after a complete database backup is restored
  • They may not be performed on the master database

  

Backup syntax

BACKUP DATABASE Northwind
TO DISK = ‘c:\backups\northwind_diff.bak’
WITH INIT, DIFFERENTIAL

 

Restore syntax (Same database – Note that a complete database backup is restored first using WITH NORECOVERY)

RESTORE DATABASE Northwind
FROM DISK = ‘c:\backups\northwind.bkp’
WITH NORECOVERY

RESTORE DATABASE Northwind
FROM DISK = ‘c:\northwind_diff.bkp’
WITH RECOVERY

 

 

Transaction log backup 

An SQL Server database consists of two components: data file(s) and transaction log file(s). A transaction log captures the modifications made to the database. A simple transaction may place several records in the transaction log. Each of these records is known as a log record and is assigned a unique identification number known as the log sequence number (LSN). Log records that belong to the same transaction are linked together through the LSN. If SQL Server service shuts down unexpectedly, upon restart the recovery process examines the entries in the transaction log and if there are transactions that have not been rolled forward completely, the recovery process rolls back the changes performed as part of these incomplete transactions. This operation is extremely important as it forms the basis of transactional recovery. Entries in the transaction log are also used if transactional replication is configured for the specific database.

A transaction log backup backs up all transactions since either the previous transaction log backup, or the complete database backup if there have been no transaction log backups performed for the database in the past. This backup may then be used to apply the backed-up changes, in case disaster recovery is required. Transaction log backups may only be applied to a database in an unrecovered state. A database may be in an unrecovered state if it is being restored from a set of backups as part of a disaster recovery procedure, or if it is configured as a standby database on a warm backup server. A transaction log backup also truncates the inactive portion of the transaction log, unless the database is configured as a Publisher in transactional replication and there are transactions pending propagation to Subscribers.

Each transaction log backup contains a First and Last log sequence number (LSN). Consecutive transaction log backups should have sequential LSNs for the boundary log records. These LSN values may be examined using the RESTORE HEADERONLY command. If LastLSN from the previously restored transaction log backup does not match the FirstLSN from the backup that is currently being restored, the restore operation fails with the following error: “This backup set cannot be restored because the database has not been rolled forward far enough. You must first restore all earlier logs before restoring this log”. If the above message is generated while restoring a particular transaction log backup, which is part of a set of transaction log backups that are to be restored, any attempts to restore further transaction log backups will fail with this message.

There could be several reasons for consecutive transaction log backups being out of sequence. Some of the most common reasons noted from support experience have been:

  • The database recovery model has been changed to Simple and back to either Full or Bulk-Logged. Switching the recovery mode to Simple causes the transaction log to be truncated
  • Another transaction log backup was performed between the previous successfully restored backup and the one generating this message
  • The transaction log was manually truncated between the two backups
  • The database was in Bulk-Logged recovery model and non-logged operations were performed
  • Transaction log backups are not allowed for databases in Simple Recovery model. While in Simple Recovery model, a database’s transaction log is truncated every time a CHECKPOINT is invoked for the database

Transaction log backups provide the possibility of performing a point-in-time restore or point-of-failure restore. You can also perform a restore to a named transaction with transaction log backups.

 

Backup syntax

 BACKUP LOG Northwind
TO DISK = ‘c:\backups\northwind_log_1.bak’
WITH INIT

 

Restore syntax (Same database – Note that a complete database backup is restored first using WITH NORECOVERY, then a sequence of transaction log backups)

 RESTORE DATABASE Northwind
FROM DISK = ‘c:\backups\northwind.bkp’
WITH NORECOVERY

RESTORE LOG Northwind
FROM DISK = ‘c:\northwind_log_1.bkp’
WITH RECOVERY

 

 

File(s) and Filegroup(s) backup 

Use BACKUP to back up database files and filegroups instead of the full database when time constraints make a full database backup impractical. To back up a file instead of the full database, put procedures in place to ensure that all files in the database are backed up regularly. Also, separate transaction log backups must be performed. After restoring a file backup, apply the transaction log to roll the file contents forward to make it consistent with the rest of the database.

Backup Plans and Strategy


Introduction

 

The intent of this document is to explore various backup scenario plans for SQL servers and come up with a recommendation to successfully restore them when the need arises. Database Administrators (DBAs) typically spend most of their time working on configuring and monitoring backup plans, but rarely take adequate time to test the restore path. DBAs should ask how confident they are that the backed up data can be restored when the need arises. The typical problem faced by DBAs is that they have to handle an ocean of backup sets and don’t know where to begin. More often, they spend hours trying to restore but end up restarting different series of backup files. All these lead to more downtime and data loss, in spite of having access to successful backups.

Backup Strategy

 

Taking backups is just the starting point for securing databases. What is crucial is the ability to anticipate disasters that can occur in a typical corporate environment. This list includes, but is not limited to, catastrophic events, disk crashes, network malfunctions, malicious data modifications, database corruptions, accidental data updates and so on. Each time the DBA might want to pickup a different restore sequence best suited for limited or no data loss with minimal downtime.

Having all these disaster types in mind, it is always challenging for DBAs to architect the backup plans and scheduled times, which will have a minimal impact on the production systems. In addition, backup plans need to accommodate future growth in the size of databases, as increase size requires increased backup time. It is unacceptable to have a backup plan in which by the time last night backup completes, next day’s backup starts again and in such a scenario the system always would be in the backup mode. In the subsequent paragraphs we will provide vital recommendations on backup plans, disaster simulations and restore paths for various situations and different types of databases including System and user databases.

To choose the most optimal backup plan, one must answer the following questions:

1. How much data can we afford to loose when disaster occurs?
2. What is the acceptable downtime window for restore and recovery?
3. Is this is a 24×7 database?
4. How large is the database and what type?

a. Is it a Static database? Loaded only once?
b. Is it a read-only database with periodic updates?
c. Is it a database that is frequently updated?
Here are some backup recommendations for SQL system databases:

Master Database:

The master database is essential for all Microsoft SQL Server operations. If the master database fails or becomes corrupt, it takes the whole server down with it. Unlike a critical user database that handles real-time customer transactions, the master database need not be backed up every hour or every half-hour. One needs to backup the master database for the following reasons:

a. after it is created
b. if the configuration values are changed
c. if the SQL logons are configured
d. any changes are made to the database
Model Database:

The Model databases a system database used in new database creation and is used as a template. There is no specific reason to backup the model database very often. Whenever a new object is created, database configuration is changed to make sure that all new databases are created with those objects and configuration. In such a situation, the database has to be backed up. Making a complete database backup is, in most cases, good enough.

MSDB Database:

The job schedule and the history are maintained and stored in the MSDB database. Hence, whenever jobs are scheduled through the SQL Server Agent, MSDB should be backed up. Also the information about replication and log shipping is kept in this database. Whenever, changes to a configuration related to the above mentioned items are made, the database should be backed up.

Distribution Database:

The Distribution database is available when replication is configured and the server is acting as a distributor. The recommendation is to schedule complete backups after snapshots. In the case of transactional replication, it is advisable to schedule regular log backups.

Tempdb

Normally, Tempdb need not be backed up. This database is recreated each time the SQL Server is started.

So far we have looked at backup recommendations for system databases. For user-created databases, let’s examine the following scenarios.

Scenario 1:

Consider a non 24×7 Database (about 500 MB to 1GB)
You should perform a complete database backup every night during the non-operational window and do transactional backups every hour. This approach provides both a point-in-time restore and a point-of-failure restore.

Scenario 2:

Consider a 24×7 Database (about 500 MB to 1GB)
You should perform a complete database backup every night during non-peak hours followed by a differential backup during off-peak time of the day. Transactional backups should be performed every 15 minutes, round the clock. This approach provides both a point-in-time restore and a point-of-failure restore. The restore time is also considerably less.

Scenario 3:

Consider a 24×7 Database (about 1 GB to 10GB)
You should perform a complete database backup every night. Do differential backups during off-peak time of the day. Do transactional backups every 15 minutes round the clock. Depending on the activity in the system, change transactional frequency.

Scenario 4:

Consider a 24×7 Database (about 10 GB to 60GB)
You should perform a complete database backup every night. Do differential backups during off-peak time of the day. Do transactional backups every 15 minutes round the clock. Depending on the activity in the system, change transactional frequency.

If the database grows beyond 60 GB, the time it takes to do a complete backup becomes the key factor. Taking individual file/file group backups is highly recommended thereafter. Today, sophisticated RAID disk systems are available and you should look at multiple files/file groups from a flexibility perspective rather than from a performance angle.

File/File Group backups give greater flexibility to handle very large databases for a number of reasons. Let’s take a look at some of them.

  • Recovery from isolated failures is faster. Only the damaged file needs to be restored rather than the entire database.
  • Enables greater flexibility to schedule backup jobs for very larger databases where the complete database backup is almost unmanageable.
  • One can organize file groups based on the updatability for better backup strategies. This enables frequent backups to frequently modified data files, rather than repetitive backup of static portions of the database.

You should be aware that these file/file group backups are not complete without transaction log backups. When restoring individual files, the transactions have to be rolled forward to make sure that the file or file group is consistent with the rest of the database. In order to reduce the number of transaction logs to be rolled forward, one should look into doing differential backups.

The primary disadvantage of the file and file group backups is the complexities involved in maintaining a complete set of backups to make sure that the restore yields a consistent database at the end.

Backup Plan for huge databases


The following backup plan is recommended for very large Microsoft SQL Server databases with traditional business hours operation:

 

  Full backup Saturday morning

  Differential backup Mon-Thu nights

  Transaction backup hourly Mon-Fri 8am to 6pm

— SQL full database backup

BACKUP DATABASE [AdventureWorks2008]

TO  DISK = N’F:\data\AdventureWorks2008\backup\AW8.BAK’

WITH NOFORMAT, INIT, 

NAME = N’AdventureWorks2008-Full Database Backup’,

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

— SQL diffential database backup

BACKUP DATABASE [AdventureWorks2008]

TO  DISK = N’F:\data\AdventureWorks2008\backup\AW8D.BAK’

WITH  DIFFERENTIAL , NOFORMAT, NOINIT, 

NAME = N’AdventureWorks2008-Differential Database Backup’,

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

— SQL transaction log backup

BACKUP LOG [AdventureWorks2008]

TO  DISK = N’F:\data\AdventureWorks2008\backup\AW8T0900.BAK’

WITH NOFORMAT, NOINIT,  NAME = N’AdventureWorks2008-Transaction Log  Backup’,

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

————

%d bloggers like this: