C H A P T E R 7
Database Backups, Recovery,
and Maintenance
Now that we have created a major part of the database in the previous chapters, and before
moving on to inserting and manipulating the data, this is a good point to take a moment to back up
the database, just so that if things go wrong, it will be possible to recover back to a stable point.
What is abundantly clear when working with any sort of system where data is held is that there
must be a comprehensible and workable backup and recovery strategy in place for when things go
wrong. The recovery may also be required to cater to problems from a hardware failure to an act of
God. In any of these instances, we may move to an offsite location, which is a building a safe distance
away from our current building housing the computing equipment. That is quite a dramatic step and
is a decision that would be taken at a higher level of authority than we probably have; however, we
must create a backup of our system and store it according to the recommendations of our board of
directors, whether they are for in-house or offsite storage. Companies have gone bust because a good
and secure backup storage plan wasn’t in place when their building burned down, for example. This
is, of course, a worst-case scenario, and there are times that moving out of the current building to a
second secure location is not necessary.
This chapter looks at different backup strategies that can be implemented by you as a developer
or an administrator and shows you how to implement them. I also show you scenarios where the
database is in use 24 hours a day, 7 days a week, and how you need to form a backup strategy around
such scenarios. From there, I show you how to perform an ad hoc backup of the database as well as
scheduled transaction log backups. I make it clear in this chapter when you would perform both of
these types of backups and when they would be useful. Of course, after the backup, you have to test
that the backup can be restored. Generally, this backup is restored onto a nonproduction system.
Some companies have complete environments established to test their disaster-recovery scenarios.
What you have to realize, and what will be demonstrated, is that there are different methods of
taking backups depending on what you are trying to achieve. The most common scenarios are
discussed and demonstrated in this chapter, but you will also get to look at database maintenance
plans.
It is imperative that you get the correct backup strategy in place and that it works. This point will
be repeated throughout the chapter.
So, in this chapter you will learn about:
• Backup strategies
• When a problem might occur during the backup and restore process
• How to take a database offline and then bring it back online
• How to create a backup
• Different media for building a backup and what needs to be considered
182 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
• The transaction log and how to back it up
• When to back up the data, and when to back up the transaction log
• Scheduling backups, and what happens if the server isn’t running at the scheduled time
• Restoring a database
• Detaching and attaching a database
• Working with users still attached to the database when you need them not to be connected
• Building SQL statements for backing up the database structure, and when it is useful to
have them
• Building a maintenance plan and when to use it
• Implementing Database Mail in your maintenance plan
Transaction Logs
Data within the database is stored, of course, on your hard drive, or on a hard drive on a server.
Imagine the work involved on the disk controller, and imagine the work SQL Server has to do every
time data is added, amended, or removed. Writing data is a slow process compared to other memorybased
processes, so inevitably, SQL Server slows down every time data is written. A good comparison
is to think how long it takes you to insert, modify, or erase a sentence, even using MS Word, compared
to how long it takes you to read a sentence. What if part of the way through writing the data, a power
outage occurred and you had no uninterruptible power supply (UPS) in place? What a mess you
would be in, not knowing what had been written to disk and therefore your tables within your database,
and what hadn’t!
It is also possible in SQL Server to update several tables at once. How would you work around
the fact that some of the tables had been updated, but when it came to updating a specific table,
the update failed? Well, this is where transaction logs come into play. Transactions themselves are
covered in Chapter 8, but very simply, a transaction is a single unit of work that can contain one or
more table modifications that are either all successful and committed to the database or, if some are
unsuccessful, all discarded. It is also possible to roll back a transaction so that no changes are made
to the database, which can either be invoked by SQL Server or by issuing a specific T-SQL command.
But you must be wondering what all this has to do with a transaction log, and you’re probably even
wondering what a transaction log is. Before we move on, there is one last area of background information
we need to discuss first.
The log file is a complex item that contains virtual, logical, and physical log files, but to keep it
simple, the transaction log has a physical size—in other words, it is like any other file on the hard
drive. The transaction log increases in size as necessary to accommodate the transactions. It is a
wraparound file. This means that when SQL Server reaches the end of the file as defined by the size
when it was set up, it wraps around to the beginning again, looking for free space to use. This allows
it to wrap around without increasing in size when there is free logical transaction space. The logical
transaction log is “contained” within the physical transaction log and is determind by a physical
point in the physical log file at where the last checkpoint or truncation action occurred, and it
continues until the next checkpoint or truncation point occurs. Virtual log files are files of no fixed
size and are built internally by SQL Server as part of the physical creation of the transaction log.
Every database within a SQL Server instance has its own transaction log. Every time SQL Server
is requested to do any data modifications—whether these are additions, deletions, or modifications—
a record is kept of the action. There are several reasons for this.
First of all, a piece of code could in fact do several different updates at once, either to different
rows of data or to rows of data in different tables or even databases. If one of the updates fails, for
example, when you are attempting to place ASCII characters into a column that only allows numerics,
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 183
then you may wish to return the values in all the updated fields to their original value. This is called
rolling back a transaction. SQL Server achieves this, in part, by looking at the data held in the transaction
log. However, any successful action where all the updates are valid could be permanently
stored on file—a process called committing a transaction.
As more and more actions are placed in the transaction log, it will become full. Some of these
actions will still be within a transaction, and others may form part of a completed transaction ready
to be committed to the database. At certain points, SQL Server will want to remove all the actions
it can to relieve some space in the transaction log for further actions. One point could be when the
transaction log reaches 70% full. SQL Server would then issue a checkpoint. The use of a checkpoint
ensures that any data modifications are actually committed to the database and not held in any
buffers, so that if a problem occurs, such as a power failure, there is a specific point that you can start
from. Therefore, at the end of a checkpoint transaction, you know the database is in a consistent and
valid state. As SQL Server knows that at a checkpoint all is well within the database, there is no need
to keep the completed transactions recorded in the transaction log stored up to the checkpoint. SQL
Server therefore issues a truncation of the transaction log to remove these records, minimizing the
size of the log on the computer. This is known as truncating the transaction log. It is thus necessary
to ensure that you have a large enough transaction log defined to hold the largest valid uncommitted
transaction, as these transactions obviously will not be truncated. A transaction log can become full
with a rogue query as well—that is, one that is coded incorrectly and keeps adding more and more
uncommitted transactions. When the transaction log reaches 70%, there is nothing to checkpoint
and eventually the transaction log will fill up, and your SQL Server will stop. This is where you will
need the help of an experienced database administrator.
If you have a power failure, you might have to “replay” all the work completed since the last
backup, and in certain scenarios, you could use the transaction log to do this. When a data modification
is completed via a T-SQL command, the details are recorded first in the transaction log. These
changes are flushed to disk and therefore no longer in memory, before SQL Server starts to make
changes to the tables that you are affecting. SQL Server doesn’t write data immediately to disk. It is
kept in a buffer cache until this cache is full or until SQL Server issues a checkpoint, and then the data
is written out. If a power failure occurs while the cache is still filling up, then that data is lost. Once
the power comes back, though, SQL Server would start from its last checkpoint state, and any updates
after the last checkpoint that were logged as successful transactions will be performed from the
transaction log.
■Note A disk cache is a space in the system that holds changes to the tables within the database. This allows
a whole block of data to be written at once, saving on the slow process of disk head movement.
Transaction logs are best kept, if at all possible, on a hard drive separate from that holding the
data. The reason for this is that data is written serially when it is written to a transaction log. Therefore,
if nothing else is on the hard drive except the transaction log, the disk heads will be in the right
place to continue writing each time. This is a minor overhead, but if performance is an issue, this is
worth considering.
Backup Strategies
Backing up a database, no matter how large or small, should form part of your database solution.
Even if a backup is taken only once a week or even once a month, it is crucial that you sit down and
decide which backup strategy is right for you. Much of this decision lies in the hands of the product
owners for your company, since they must weigh the risk they’re willing to take against the cost of
minimizing that risk. Many different strategies can be adopted within your overall main backup
strategy, depending on days of the week or perhaps periods within the month.
184 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
Based on the strategy that you choose, you have to decide what type of backup you need. Full
database backups take a complete snapshot of a database at any given point. A differential backup
backs up only the data that has changed since the last full backup. Finally, a transaction log backup
backs up only the data in the transaction log, which consists of transactions that were recently
committed to the database. All of these types of backups can be done while your SQL Server is online
and while users are actively hitting your database. To be able to restore a database to any point in
time, you have to use a combination of these backup types.
When determining your backup strategy, first look at your application and ask yourself the
following questions:
• How much of the data can be lost, if any, at any point of failure? In other words, how crucial
is it that no data is lost?
• How often is the data updated? Do you need regular backups from a performance viewpoint
as well as a recovery viewpoint? For historical databases that have their data modified only
periodically, you would, at most, complete a backup postpopulation.
• Do you need to back up all the data all of the time, or can you do this periodically, and then
only back up the data that has altered in the meanwhile?
• How much data needs to be backed up, and how long do you need to keep the copies of the
backups?
• In the event of catastrophic failure, how long will it take to completely rebuild the database, if
it’s even possible?
Many more questions can be asked, but for the moment, these are the most crucial questions
that need answers. If you can afford to allow data updates to be lost, then you need a straightforward
periodic database backup—for example, you need to back up the whole database once a week. This
is simple and easy to complete and requires little thought. However, this scenario is rare and is usually
found in data-warehousing systems or those with a large amount of static data that can be rebuilt.
Looking at the next question, if a large number of updates are taking place, then a more complex
solution is required. For every data modification, a record is kept in the transaction log file, which has
a limited amount of space. This amount of space was defined when you set up the database as a fixed
maximum size or, if you are allowing it to grow unrestrictedly, equals the amount of hard drive there
is. If you back up and clear the transaction log file, this will free up the space logically initially and
also aid performance. This is known as logical log truncation. The smaller the active part of the
transaction log file, the better. The more transactions there are in the transaction log file, the longer
it will take to recover from a corrupt database. This is due to the fact that a restore will have to restore
the data and then every transaction log backup to the point of failure. That is, each transaction log
will have to be restored to update the database, not just the latest log file. If you have multiple small
files and they are held on media that has to be mounted each time, such as a tape, then you will have
to take mounting time into consideration as well.
The third question, though, covers the real crux of the problems. If you need to back up all the
data each time, how often will that need to take place? This could well be every night, and many
production systems do just this. By completing a full data backup every night, you are allowing yourself
to be in a state where only one or two restores may need to occur to get back to a working state
in a disaster scenario. This would be the data backup, followed by the single transaction log backup,
if one was taken in the meantime. This is much better than having one data backup to be restored,
and then a log file for every day since the data file backup. What happens if the failure is on a Friday
at lunchtime and you completed your last whole database backup on a Saturday evening? That
would take one data file and six transaction log file restores to complete.
Sit down and take stock. As often as you can, take a full database backup, then take a differential
backup, followed by transaction log backups. However, you have to weigh the time that a full backup
takes against a differential backup or a transaction log; how much processing time you have to complete
these backups; and the risk level of having to complete, for example, six transaction log restores.
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 185
The problem is, there is no universally right answer. Each situation is different, and it is only
through experience, which comes from testing your solution thoroughly, that you find out what is
best for your situation.
Whatever your backup strategy, the best possible scenario is to complete a backup when nobody
is working within the database. If there are times when you can make the database unavailable, then
this is an ideal opportunity to take the backup. Although SQL Server can perform full backups while
the database is online and active, you will gain performance benefits by having an inactive database.
The first example, shortly, demonstrates one method of doing this.
When Problems May Occur
Obviously, when taking a backup, it must work; otherwise, you have wasted your time, but crucially,
you are leaving your database and organization in a vulnerable position if the backup fails. If you
have time within your backup window to verify that a backup has been successful, then you must do
it. As you will see, SQL Server gives you different options for doing this. It cannot be stressed strongly
enough that verifying a backup is just as crucial as taking the backup in the first place. There have
been situations where a backup has been taken every night; however, no one has noticed that the
backup has failed. When there is a hardware failure, there’s no backup to use as a restore on a new
machine. In one case I know of, almost a week’s worth of data was lost. Luckily, the weekend backups
had succeeded; otherwise, the company would have been in a major data loss situation. The cause
of the failed backups was that the tapes being inserted for the backup were not large enough to hold
the backup being performed. Therefore, the tapes became full, and the backup failed. Obviously,
this company failed not only to verify the backup, but also to have processes in place to check that
its backup strategy was still working after a period of implementation. The only sure and positive
way of ensuring a backup has succeeded is to restore the database to a specific restore test location
and check the data. Although you will see SQL Server does have a method of checking a backup, this
still isn’t a guarantee that the backup worked. Do take time to complete regular restores to a location
to test that everything is OK.
You should always review your backup strategy on a regular basis. Even better, put in place jobs
that run each day that give some sort of space report so that it is possible to instantly see that a potential
problem is looming. SQL Server Reporting Services is a new tool that would be ideal for
producing and distributing space reports to database administrators and developer owners alike.
Taking a Database Offline
SQL Server does not have to be offline to perform a backup, as you will see as we go through the book
and work through creating SQL Server–defined backups using wizards and T-SQL. In most environments,
you will not have the luxury of taking a database offline before backing it up, because users
are constantly making data changes. Backing up a database can take a long time, and the longer it
takes, the longer users cannot be working with the data while it is offline.
By taking your database offline, you do not have to use SQL Server to perform the backup. This
strategy is one where you take a disk backup, which means the hard drive is backed up, rather than
a specific database within a server. However, don’t forget that by taking your database offline, you
will have to take a backup of the directory using some sort of drive backup.
If you have your database on a server, no doubt some sort of server backup strategy is in place,
and so your database will be backed up fully and successfully through this method; if you can take
your database “out of service” in time for those backups, then you should do so. This does allow you
to think about your SQL Server deployment strategy. If you have several databases that can be taken
offline as part of the backup, then it is worth considering whether they can all reside on the same
physical server, and you can set your server backup times accordingly. However, this is a rare scenario,
and even rarer within a production environment. Taking the database offline means taking your
186 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
database out of service. Nobody can update or access the data, modify table structures, and so on. In
this next section, we will take ApressFinancial offline, allowing a physical backup to be taken. Just to
reiterate and clarify: this is being demonstrated only to complete your knowledge of backups, and it
is rare that you will perform this action in a live scenario.
Try It Out: Taking a Database Offline
1. Open SQL Server Management Studio and open a Query Editor pane. Enter and execute the following code:
USE master
GO
ALTER DATABASE ApressFinancial
SET OFFLINE
2. Try to click some of the nodes for the ApressFinancial database—for example, the Tables node. As shown
in Figure 7-1, we are reminded that the database is offline and therefore cannot be viewed or modified. We are
also not able to access the database through any application such as Query Editor.
Figure 7-1. The database is offline and therefore unable to be opened.
To take a database offline, SQL Server must be able to gain exclusive access to the database. This means that no user can
be in the database when we issue the command. If users are connected, then the query will continue to execute until all
users are disconnected.
As said earlier, that’s all there is to it. Our database is now no longer available for any updates or modifications, so we can
back it up using any backup utility that takes files from a hard drive.
If you have to restore from a backup completed this way, don’t forget to take the database offline first, then restore from
the backup, and then bring the database back online, ready for use:
USE master
go
ALTER DATABASE ApressFinancial
SET ONLINE
There is one area to note when using backup strategies that employ these methods. If you have a server backup that runs,
for example, at 0200 hours, do you fancy getting up every night, just before 2 a.m., taking the database offline, and then
bringing the server back up once the backup is complete? No—not many people would. Of course, there are installations
where people are working through the night, so this is less of a problem, but what if they are busy? Or forget? Then your
whole backup will fail because the files are in use, and therefore the server will not back up these files.
So let’s now look at a more friendly method of backing up the data by using SQL Server instead.
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 187
Backing Up the Data
The majority of readers will use SQL Server to back up the database. By using SQL Server, we are
keeping the backup of the database under the control of an automated process that can control its
own destiny, and as you will find out later, it can also control the system when things go wrong.
The backup will be split into two parts. The first part, which will be covered here, will be when
we perform the backup manually each time. Obviously, this means we have to be available to perform
the backup, but this can be rectified quite easily. Once this has been covered, the next section will
show you how to schedule a backup to run at a specific time, which relieves us of needing to be available
to complete a backup at the specified time.
Let’s start by looking at the manual backup.
Try It Out: Backing Up the Data
1. Ensure SQL Server Management Studio is running. Find our database, right-click, select Tasks, and then click
Back Up.
2. This brings up the SQL Server Back Up Database dialog box. Take a moment to peruse this dialog box. As
Figure 7-2 shows, a lot appears on this screen, which will be dealt with a section at a time.
Figure 7-2. Backing up a database (Full Recovery mode)
188 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
Although we have chosen our own database to back up, we could alter which database by changing the value in the combo
box. Next is the backup type, of which we have three options to choose from: Full, Differential, and Transaction Log.
The first possibility, full backup, is straightforward. Selecting the Full option ensures that the whole database will be backed
up. All the tables, the data, and so on are backed up if we use this option. We also back up enough of the transaction log
(transactions committed via code, but not physically applied to the relevant tables at the point of backup). This backup
would be seen as any starting point for a restore from a database failure.
The second possibility is the differential backup, also known as an incremental backup. Use the Differential backup option
when the circumstances are such that a full backup is either not required or not possible. This just performs a backup
on data changed since the last backup. For example, we may take a full backup on the weekend and then a differential
backup every night. When it comes to restoring, we would restore the weekend full backup, and then add to that the latest
differential backup taken from that point. A word of warning here: if you do take a week’s worth of differential backups, and
you back up to magnetic tape rather than a hard drive, do keep at least enough different tapes for each differential backup.
Therefore, use a complete backup when necessary and then in the interim, use a differential backup.
The last possibility, the transaction log backup, should be used as the most frequent method of backup providing that the
database is not in Simple Recovery mode. As data is modified, details of the modifications are stored in the transaction log.
These remain in place until an action “truncates” the transaction log, which means that the transaction log will increase
constantly in size if not in Simple Recovery. When you issue a transaction log backup, you are just backing up the transaction
log, which issues a checkpoint, and all committed transactions are stored onto the backup. This means that if a
system failure occurs, you will restore from a full backup, then from your differential backups for the week, and finally from
any transaction log backups after that point.
You are probably wondering why you can’t just use differential backups. Transaction logs can fill up during the working day,
or perhaps you have set differential backups to happen weekly because there is so little data modification. However, you
do need to account for when a transaction log may fill up before you reach the next differential backup. By taking a backup
of the transaction log, this is a great deal faster than the other two methods. Certainly in heavily used databases, you may
have several transaction log backups in the day. You see how to do this using T-SQL after we take our first full backup. At
least one backup must exist before we can take a transaction log backup, as we need a point at which the transaction log
can roll committed transactions forward from.
■Note If we were backing up the master database, then the only option that would be available to us would be
a complete database backup via the Full option.
A name and a description of your backup are always useful. You will create different backups over time, so a good description
is always something that will help at a later date. I recommend that you use some sort of date and time as part of the
description, as this will make it easier to find, and which mode of backup you have chosen.
Different types of backups will have different expiry dates. This means that after the defined date, the media you have
stored your backup on will allow the data to be overwritten if using SQL Server (you can't delete the file manually!). For
example, you might have a weekly full backup that you want to keep three instances of, and then the first full backup of
the month you may wish to keep for six months, or even longer if it is a database that you must keep for government legislation.
In this option, you can retain the backup for a set number of days (for example, 21 days) or for a set period of time
(a specific date covers for uneven days in a month, or a year, for example).
A default destination is defined, which might be more than acceptable. It will be on our hard drive, in a location below
where our data is. It is best to have a directory set aside for backups so that they are easy to find, perhaps with a name
such as SQL Server Backups. However, this is not recommended in production. What if the hard drive fails? We can gain
a substantial performance improvement by backing up the database to a separate disk or to a RAID 1 drive if one is available.
This is because data is written to the backup file in a sequential manner. It is also advisable to give the backup file a meaningful
name. In this instance, it has been given the name of the database, ApressFinancial.
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 189
3. Move to the Options tab, as shown in Figure 7-3, where we can define what options we want to happen as part
of this backup.
Figure 7-3. Database backup options
The first section of this dialog box deals with what you want to happen on second and subsequent backups. The first time
the backup is run, it creates the backup files, but when you run subsequent backups, do you want to append to the current
data or overwrite it? If this is a full backup, then you may overwrite, as you should be placing this full backup over an old
nonrequired backup. However, if this is a differential backup, where it is perhaps the second or third of the week, then you
will append to the existing backup set. This will be after the previous differential backups and means that if you need to do
a restore, all the backups will be one after another and therefore will provide the fastest retrieval.
The Check Media Set Name option forces the backup to check that where the data is going to be backed up to is still a valid
name and, if appending, that the data set has not expired.
You use the Back Up to a New Media Set, and Erase All Existing Backup Sets option when any previous backups are no
longer required. This is ideal when moving the database from development to either user testing or even production, and
you don’t want to be able to restore from an incorrect backup. There is no point in wishing to restore a production server
from a development backup, after all.
The second section deals with the reliability of the backup. It is possible to simply back up the data and trust that everything
worked perfectly, meaning no data transmission errors occurred between your SQL Server and the backup device, or
that no errors occurred when writing the data. A situation such as this is unusual, but there will be times when it does
happen. Do you trust that those times will occur when you will not need a backup? I suggest this is something you cannot
and should not rely on. Therefore, although it will increase the amount of time the backup takes, it is good to choose one
190 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
of the two options in this section. The first option allows a verification of the backup where SQL Server compares what has
been backed up with what it expects to have been backed up, and the second option allows for checksum processing
whereby SQL Server performs a mathematical calculation on the data to back up, which generates a checksum that can
then be compared once the data has been transmitted from SQL Server to the backup device. If you select the second
option, you can also specify whether to continue if you get a checksum error.
If you are doing a transaction log backup, the next area of the dialog box will be enabled. You can logically shrink the transaction
log by removing all entries that have just been backed up by selecting the first option, Truncate the Transaction Log.
To save processing time, the physical transaction log is not shrunk. The second option, Back Up the Tail of the Log, is used
when there has been some sort of database corruption. If you wish to back up transaction log records that have not been
backed up prior to performing a restore to correct the corruption, then you would use this option. To clarify, a database
becomes corrupt, and you need to be able to restore up to the last backup, then add all the transactions that have occurred
since the last backup. By executing a backup of the tail of the log, you can restore the database and then use this tail log
backup to add the missing transactions.
The penultimate area of the dialog box is available if you are using tapes as your backup medium. You can eject the tape
once the backup has finished. This is a useful option, as the computer operators would know to remove the tape for dispatch
to the safe backup area. The second option, which specifies a rewind, is useful for full backups. On differential backups,
however, SQL Server would be halted when running the next backup while the tape device got to the right place on the tape
to continue the backup.
Finally, it is possible to set the compression level for the backup. This is ideal to keep space taken for each backup to a
minimum. Notice that by default, it takes the compression level set for the server, but it’s possible to overwrite this with a
new setting. If you do use compressed backups, then you will save on I/O and the backup operation will complete in less
time, but completing the compression will increase the amount of processing SQL Server is using. It is possible to reduce
the priority of completing a compressed backup so that it doesn’t affect SQL Server by using the resource governor. This
is an advanced topic and not covered in this book.
Clicking OK starts the backup. Once the backup is finished, you should see the dialog box shown in Figure 7-4.
Figure 7-4. A successful backup
The first backup of the ApressFinancial database has now taken place and should have been successful. If we now
move to the directory on the hard drive where the backup took place, then we will see the ApressFinancial file.
Recall that it was mentioned earlier that a company lost a week’s worth of data. It had set up the option to append to
media, the tape had become full, and the administrator had not set up the proper scenario to alert someone when a
problem occurred. So there was not just one failure in the system, but two; however, it still highlights that if you are using
the option to append to media, you must check that enough room is available on the medium that you are appending to for
the backup to succeed.
Creating a backup of your database and the data is the most crucial action in building a database solution. Get it wrong,
and you may as well go home. Well, not quite, but if (or when) things go wrong, and you don’t have a valid or recent enough
backup that is acceptable to the users of your database, it will take a long time for you as a developer to recover from that
situation and get back to the excellent working relationship you had beforehand.
The backup taken in the preceding example is the simplest backup to perform. It is a complete backup of our particular
SQL Server database, and it happens while we are watching. If it goes wrong, we will instantly see and be able to deal with
it. However, most backups do not happen when you are there and instead happen throughout the night. In the next section,
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 191
you will see more about scheduling jobs and how to schedule a task to run through the night. However, it doesn’t cover
what to do when things go wrong. This is a difficult area to discuss and should be integrated with our database maintenance
plan, which is covered later in this chapter in the section “Creating a Database Maintenance Plan.” This example
demonstrates how to complete a backup manually rather than as an automated process.
Before moving on, there are a couple more points concerning backups that you must keep in mind, and it is recommended
strongly that these directions be followed. First of all, keep a regular and up-to-date backup of the master and msdb
system databases. SQL Server very rarely becomes corrupted, but it can happen for any number of reasons, from a hard
drive failure to a developer altering the database in error. It really doesn’t matter, but if you don’t have a backup of the
master database, you could find yourself in trouble. However, be warned. Restoring the master database should not be
performed unless you really have to, and only if you are experienced with SQL Server. Restoring the master database is
not like restoring other databases, and it has to be completed outside SQL Server Management Studio. This book quite
deliberately does not cover having to restore the master database, since it is a very advanced topic. If you wish to know
more, then take a look at Books Online for more details.
When it comes to the msdb database and when to back it up, it could be that a daily backup is required. If you recall, this
database holds job schedules and other information pertinent to the SQL Server Agent for scheduling. If you have jobs that
run each day, and you need to keep information about when jobs were run, a daily backup may be required. However, if you
only wish to keep a backup of jobs that are set up and there is no need to know when certain jobs ran and whether they
were successful or not, then perhaps look at backing up this database weekly.
The model database should be backed up if any details within the model database have been altered. This should be
pretty infrequent, and therefore backing up this database need not be as regular as any other database; once a week is
probably frequent enough.
Backing up tempdb is not necessary, as this should be seen as a transient database, which has no set state. Therefore, it
would be very unusual to back up this database.
■Note When SQL Server is restarted, tempdb is dropped and is re-created as part of the startup process.
As you can see, it is not just your own databases that need to be considered and remembered when it comes to dealing
with your backup strategy. A database within SQL Server is not an insular arrangement and affects the system databases
just as much. If in doubt, back it up more frequently than is required!
Backing Up the Database Using T-SQL
Now that we have backed up the database using the wizard, it is useful to demonstrate performing a
backup with T-SQL. These commands and statements can be used within a stored procedure that
can be scheduled to run at required intervals as part of an overnight task.
There are two different types of backups. It is possible to back up either the database or specific
file groups or files that are part of the database. The code for the database backup follows. The highlighted
code demonstrates which of the two possible options is the optional default used when neither
option is specified:
BACKUP DATABASE { database_name | @database_name_var }
TO <> [ ,...n ]
[ [ MIRROR TO <> [ ,...n ] ] [ ...next-mirror ] ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]
192 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
| { COMPRESSION | NO_COMPRESSION }
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] { FORMAT | NOFORMAT } ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] STATS [ = percentage ] ]
[ [ , ] COPY_ONLY ]
]
If instead you just wish to back up specific files or file groups, the difference in the code is highlighted
in the BACKUP DATABASE statement shown here:
BACKUP DATABASE { database_name | @database_name_var }
TO
[ [ MIRROR TO
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
| { COMPRESSION | NO_COMPRESSION }
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] { FORMAT | NOFORMAT } ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] STATS [ = percentage ] ]
[ [ , ] COPY_ONLY ]
I can now give a brief description of all the options that are available. We looked at some of these
options previously with the Back Up Database dialog box. Seeing these descriptions allows you to
compare options within T-SQL and within the backup dialog boxes:
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 193
• database_name | @database_name_var: Either the name of a database or a local variable that
gives the name of the database to back up.
• file_or_filegroup: The name of the file or file group to back up.
• backup_device: The name of the logical or physical backup device to use.
• MIRROR TO: The backup file is mirrored to two to four different file locations.
• BLOCKSIZE: The block size to use. For example, if backing up to CD-ROM, then you would set
a block size of 2048.
• CHECKSUM | NO_CHECKSUM: Specifies whether to perform checksum calculations to ensure the
transmission of data.
• STOP_ON_ERROR | CONTINUE_AFTER_ERROR: Specifies whether to stop on a checksum error or not.
• DESCRIPTION: A description of the backup.
• DIFFERENTIAL: If this is a differential backup, then specify this option. Without this option, a
full backup is taken.
• EXPIREDATE: The date the backup expires and is therefore available to be overwritten.
• RETAINDAYS: The number of days the backup will be kept before the system will allow it to be
overwritten.
• COMPRESSION: Lets you compress your backup and reduce the amount of space taken.
■Note Compression backups are only available on the Developer and Enterprise editions of SQL Server. If you
don’t define this option, then the server instance setting will be used. You can change it at the server level via the
sp_configure system stored procedure.
• PASSWORD: The password associated with the backup. This must be supplied when interrogating
the backup for any restore operation. There is no strong encryption on this option, so there is
the potential that it could be broken easily.
• FORMAT | NOFORMAT: Specifies whether to format the storage medium or not.
• INIT | NOINIT: Keeps the media header created with the format but erases the contents.
• NOSKIP | SKIP: If you want to skip the checking of expiredate or retaindays when using the
media set, then select the SKIP option. Otherwise, expiredate and retaindays will be checked.
■Note A media set is an ordered set of backups on the same disk or tape.
• MEDIADESCRIPTION: Gives a description to the media set.
• MEDIANAME: Names the media set.
• MEDIAPASSWORD: Gives the media set its password.
• NAME: Names the backup set.
• NOREWIND | REWIND: Specifies whether to rewind a tape or not.
• NOUNLOAD | UNLOAD: Specifies whether the tape is unloaded or kept on the tape drive.
194 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
• STATS [ = percentage ]: SQL Server provides a message at this percentage interval telling you
how much of the approximate backup has completed. It’s useful for gauging the progress of
long-running backups.
• COPY_ONLY: Tells SQL Server that this is a copy of the data. It cannot be used as a full backup
point for differential backups, as the differential backups will be in line with the last “pure”
full backup. This option is ideal if you take weekly backups for dumping the data to a user test
region, as it will not affect the production backup process.
The only remaining option is for files or file groups where you can name the file or file group that
the backup is for. The preceding options do not change for files or file groups.
Try It Out: Backing Up the Database Using T-SQL for a Full and Differential Backup
1. Open up a fresh Query Editor window. It doesn’t matter which database it is pointing to, as the BACKUP
DATABASE statement defines the database we will be working with.
2. The T-SQL that we need for our full backup follows. Enter the code (keeping the name of where the backup is
located via the TO DISK option and the WITH NAME option all on one line). Notice that there are no options
defined for several of the options, as we are taking the default.
BACKUP DATABASE ApressFinancial
TO DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\
ApressFinancial.bak'
WITH NAME = 'ApressFinancial-Full Database Backup',
SKIP,
NOUNLOAD,
STATS = 10
3. Execute the code, and you will see results similar to those that follow. The main points to notice are the stats
messages that come out in approximations of 10 percentage points. It then lists the number of data pages backed
up and the number of log pages backed up. The on file part of the message details which file within the media
set the backup now is. In this case, this is the third backup. You will possibly see on file 2 unless a second
or subsequent backup has been taken in the meantime. The final message is the one of greatest interest, as it
shows that the backup was successful, and it displays the amount of time taken.
13 percent processed.
22 percent processed.
31 percent processed.
40 percent processed.
54 percent processed.
63 percent processed.
72 percent processed.
81 percent processed.
90 percent processed.
Processed 176 pages for database 'ApressFinancial', file 'ApressFinancial'
on file 2.
100 percent processed.
Processed 1 pages for database 'ApressFinancial', file 'ApressFinancial_log'
on file 2.
BACKUP DATABASE successfully processed 177 pages in 0.380 seconds (3.815 MB/sec).
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 195
4. Although useful to see, not many of the options were used. However, Figure 7-5 shows the next backup of the
database to be taken, which is a differential backup. We will not allow this backup to expire until 60 days have
elapsed, as shown in Figure 7-5. We will also be adding this differential backup to the full backup. At the end of
the screen shots, don’t back up, but click the Generate Script button which will place the T-SQL equivalent code
in a new query window where you will then be asked to run it.
Figure 7-5. Backing up a database (differential)
5. Figure 7-6 shows that we are appending to the same media set as the full backup and that we have included
some reliability checking. Make sure your version matches the figure.
6. The code that would be the equivalent of these two figures has been split in two. The first part is the differential
backup. Again, ensure that the TO DISK, DESCRIPTION, and NAME options are all on the same line of the Query
Editor window pane.
BACKUP DATABASE [ApressFinancial] TO DISK =
N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\
Backup\ApressFinancial.bak'
WITH DIFFERENTIAL ,
DESCRIPTION = 'This is a differential backup',
RETAINDAYS = 60, NOFORMAT, NOINIT,
MEDIANAME = N'ApressBackup',
NAME = N'ApressFinancial-Differential Database Backup',
NOSKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
196 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
Figure 7-6. Options for backing up a database (differential)
7. The second part is where the reliability checking takes place. This is more complex T-SQL than we have covered,
so for the moment just trust that it works and that it does what it is supposed to. You will encounter this code
once more when looking at more complex T-SQL later in the book in Chapter 11. However, the basis of the code
is that a check is made in the msdb database to retrieve the last backup set that was taken, that we do a “restore”
of the database as verification only without actually restoring any data, and that the restore can complete successfully.
If it can’t verify the backup set or that the restore is OK, then you will get an error message.
declare @backupSetId as int
select @backupSetId = position
from msdb..backupset
where database_name=N'ApressFinancial'
and backup_set_id=
(select max(backup_set_id)
from msdb..backupset
where database_name=N'ApressFinancial' )
if @backupSetId is null
begin
raiserror(N'Verify failed. Backup information for database ''ApressFinancial''
not found.', 16, 1)
end
RESTORE VERIFYONLY FROM
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 197
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER
\MSSQL\Backup\ApressFinancial.bak'
WITH FILE = @backupSetId,
NOUNLOAD,
NOREWIND
8. When the code is executed, you will see something like the results that follow. Again, they contain details of the
amount of data backed up as well as which file number on the media set the backup is.
19 percent processed.
39 percent processed.
58 percent processed.
78 percent processed.
97 percent processed.
Processed 40 pages for database 'ApressFinancial',
file 'ApressFinancial' on file 3.
100 percent processed.
Processed 1 pages for database 'ApressFinancial',
file 'ApressFinancial_log' on file 3.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages
in 0.433 seconds (0.774 MB/sec).
The backup set on file 3 is valid.
Transaction Log Backup Using T-SQL
You can back up not only the data, but also, and just as importantly, the transaction log for the database.
Just to recap, the transaction log is a file used by databases to log every transaction, including
DML actions such as rebuilding indexes. In other words, every data modification that has taken
place on any table within the database will be recorded within the transaction log. The transaction
log is then used in many different scenarios within a database solution, but where it is most useful,
from a database recovery point of view, is when a database crashes. In this case, the transaction log
can be used to move forward from the last data backup, using the transactions listed within the
transaction log.
If a database crash occurs, then the full and differential backups will only take you to the last
valid backup. For data entered since that point, the only way to restore the information is to then
“replay” the transactions that were committed and recorded as committed in the transaction log.
Any actions that were in progress at the time of the failure that were within a transaction that was still
in progress would have to be rerun from the start.
So, to clarify, if you were in the process of deleting data within a table and the power was switched
off, you would use your full and differential backups to restore the data. You would then use the
information within the transaction log to replay all successful transactions, but because the delete
had not been successful, the table would have all the data still within it.
Backing up the transaction log is a good strategy to employ when a large number of updates
occur to the data through the day. A transaction log backup should take place at set times throughout
the day depending on how large the transaction log has grown and how crucial it was to get your
system back up and running after any unexpected outage. When a transaction log is backed up, the
transaction log itself is logically shrunk in size so that the transaction log is kept small. It also gives
you point-in-time recoverability; this means that you can quickly restore to any time in the past
where the transaction was backed up.
198 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
Backing up a transaction log is similar to backing up a database. The full syntax is as follows and
really only differs from a database backup by using the LOG keyword instead of DATABASE and the
options NO_TRUNCATE and NORECOVERY/STANDBY:
BACKUP LOG { database_name | @database_name_var }
{
TO
[ [ MIRROR TO
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] { STOP_ON_ERROR | CONTINUE_AFTER_ERROR } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ ,] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
| { COMPRESSION | NO_COMPRESSION }
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] { FORMAT | NOFORMAT } ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] NO_TRUNCATE ]
[ [ , ] { NORECOVERY | STANDBY = undo_file_name } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] STATS [ = percentage ] ]
[ [ , ] COPY_ONLY ]
]
}
Now let’s look at the options not covered earlier when looking at backing up the database:
• LOG: Determines that we wish to produce a backup of the transaction log rather than a backup
of a database or files/file groups.
• NO_TRUNCATE: Doesn’t truncate the log after the backup. If the database is corrupt, using this
option will allow the backup to be attempted at least. Without this option, you will get an
error message.
• NORECOVERY | STANDBY: After the backup, the database will be in a state whereby it looks to
anyone trying to connect as if it is still being restored.
■Note The LOG options NO_TRUNCATE and NORECOVER | STANDBY are used when the database is corrupt and
you wish to back up the transaction log prior to performing a restore.
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 199
Try It Out: Backing Up the Transaction Log Using T-SQL
1. In a Query Editor pane, enter the following T-SQL code. This backs up the transaction log to the same media set
as the full and differential backups. While developing and learning SQL Server, this is a valid scenario, and in
some production setups you may want to back up to the same place as your daily full backup. However, the
downside is that if you take several transaction log backups between each differential backup and full backup,
then SQL Server will have to “skip” these if they were not required as part of the restore operation. On a tape
drive, this could cause significant overhead. In this scenario, you would be better off saving the transaction log
files to a different media set.
BACKUP LOG ApressFinancial
TO DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\
ApressFinancial.bak'
WITH NOFORMAT, NOINIT,
NAME = N'ApressFinancial-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD,
STATS = 10
2. This code replicates the Truncate the Transaction Log option, as shown in Figure 7-7. Execute the code.
Figure 7-7. Backing up a transaction log
200 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
3. After execution, you should see output similar to the following, where the transaction log has been successfully
backed up and placed on file 4:
100 percent processed.
Processed 4 pages for database 'ApressFinancial', file
'ApressFinancial_log' on file 4.
BACKUP LOG successfully processed 4 pages in 0.135 seconds (0.235 MB/sec).
Restoring a Database
Now that the data has been backed up, what if you need to complete a restore? As has been mentioned,
there are two scenarios where a restore could be required: from a backup or when a media failure
occurs. The second type of restore is not one you wish to perform, but you could set it up by creating
a long-running transaction and then simply switching your computer off—not one of life’s greatest
ideas to do deliberately! This book therefore will not be demonstrating this option, and it is not really
for a beginner to attempt. However, I will discuss the concept within this section of the chapter. The
first option, a simple restore, is easy to replicate and perform, and this will be the option we will be
looking at.
You can choose between two means to restore the database: SQL Server Management Studio
and T-SQL. This is a scenario that you hope you will never perform in a production environment, but
it will happen. If you just need a restore within the development environment to remove test data
and get back to a stable predefined set of data to complete the testing, then this next section should
help you. It might also be that you do a weekly refresh of your user test region from a production
backup. Before completing the restore, let’s first modify the ApressFinancial database to prove that
the restore works, as there is no data within the database yet to prove the restore has worked by that
method. Keep in mind, however, that a restore will restore not only the data structures, but also the
data, the permissions, and other areas of the database not yet covered in the book—for example,
views, stored procedures, and so on.
Restoring Using SQL Server Management Studio
The restore demonstrated in the following example will be a complete database restore of our
ApressFinancial database. In other words, it will restore all the full and differential backups taken.
Try It Out: Restoring a Database
1. Add a new column to the ShareDetails.Shares table using the following code in a Query Editor pane:
USE ApressFinancial
GO
ALTER TABLE ShareDetails.Shares
ADD DummyColumn varchar(30)
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 201
2. Once you have confirmed that the column has been added by looking in the Object Explorer, we can now use the
backup we finished earlier to restore the database, which will remove the changes we have just completed. From
the Object Explorer window, select the ApressFinancial database, right-click, and select Tasks ➤ Restore ➤
Database. This brings up the dialog box shown in Figure 7-8. It is possible to change the database you wish to
restore by changing the name in the To Database combo box or by simply overwriting the name that is there. The
second option, To a Point in Time, is used if you are restoring the database as well as rolling forward changes
recorded in the transaction log. This situation is similar to the scenario mentioned earlier about a power failure
or hard drive failure. As we are not doing this here, leave this option as it is. When taking a backup, details are
stored in msdb, but it is possible to restore a database from a backup that is not in msdb. For example, if you are
rebuilding a server due to corruption, and msdb was one of the databases corrupted, it is necessary to have the
option of finding a backup file and restoring from that instead. Or perhaps the last full backup taken is not the
backup you wish to restore. This might occur in a development scenario where you wish to restore to a backup
before major changes were done that you wish to remove. There would be no transaction log involved or required
to be involved, therefore restoring to a point in time would not be a valid scenario. This is where you could use
the From Device option. By selecting this option and clicking the ellipsis to the right, you can navigate to any old
backup files. Finally, you can click which of the items in the backup you wish to restore. The default is all files to
be selected, as you can see in Figure 7-8. The settings shown will give us a backup that is as fresh as possible
(the Most Recent Possible value for the To a Point in Time setting).
Figure 7-8. Restoring a database—General tab
202 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
3. Moving to the Options page, shown in Figure 7-9, there are a number of points to consider:
• Overwrite the Existing Database: This is the most likely option to be enabled for a normal restore. You
would disable it if you wished to create a restore on the same server but where the restore would alter the
name of the database. You cannot have any items not backed up within the transaction log; if you do, the
restore will fail.
• Preserve the Replication Settings: A more advanced option for when a database is sending changes to
another database. For the time being, leave this option disabled.
• Prompt Before Restoring Each Backup: If you wish a prompt message before each restore file is activated,
then select this. This is ideal if you need to swap media over.
• Restrict Access to the Restored Database: You may wish to check out the database after a restore to ensure
the restore is what you wish, or in a production environment to run further checks on the database integrity.
• Restore the Database Files As: This grid allows you to move or rename the MDF and LDF files.
• Leave the Database Ready to Use: This option defines whether users can immediately connect and work
with the data after the restore. If a transaction is in progress, such as deleting rows within a table, then
the connection could occur once the deletion has been rolled back and the table is back in its “original” state.
• Leave the Database Non-operational: With this option, you can indicate that the database has been partially
restored and you are unsure if you need to perform additional actions. If a transaction is in progress, such
as deleting a table, then whatever has been deleted will still be deleted and will not be rolled back.
• Leave the Database in Read-Only Mode: A combination of the first two options. If a transaction is in progress,
such as deleting rows in a table, then the connection could occur once the deletion has been rolled back.
However, the changes are also kept in a separate file, so that any of these actions that have been rolled
back can be reapplied. This might happen if there are several actions within a transaction and some can
be reapplied.
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 203
Figure 7-9. Restoring a database—Options tab
4. Once you have the option settings you require, a quick click of OK performs the restore. You should see the
message in Figure 7-10. If you then move back to the database after clicking OK, you will see that the column
we just added has been “removed” when you look in Object Explorer.
Figure 7-10. Restore successful
204 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
Restoring Using T-SQL
Using the wizard is a pretty fast way to restore a database, and when under pressure, it may even
be the best way forward. However, it is not the most flexible way of performing a restore, as some
options that are available via T-SQL are not in this wizard. Some of these options were covered when
we performed a backup, such as performing checksums when transferring data from the media
device back to the database or unloading media at the end of the restore. If there is also a password
on the backup medium, this option is not available within the wizard, but you can use passwords
with T-SQL. Being comfortable building a restore via T-SQL is important in becoming a more proficient
and professional developer or administrator.
The syntax for restoring a database is similar to that for database backups. After looking at the
syntax, we will then go through the options you will not be familiar with.
RESTORE DATABASE { database_name | @database_name_var }
[ FROM
[ WITH
[ { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
[ [ , ] FILE = { file_number | @file_number } ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword |
@mediapassword_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] { RECOVERY | NORECOVERY | STANDBY =
{standby_file_name | @standby_file_name_var }
} ]
[ [ , ] REPLACE ]
[ [ , ] RESTART ]
[ [ , ] RESTRICTED_USER ]
[ [ , ] { REWIND | NOREWIND } ]
[ [ , ] STATS [ = percentage ] ]
[ [ , ] { STOPAT = { date_time | @date_time_var }
| STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER datetime ]
| STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER datetime ]
} ]
[ [ , ] { UNLOAD | NOUNLOAD } ]
]
The options we have not yet covered are as follows:
• KEEP_REPLICATION: When working with replication, consider using this option. Replication is
when changes completed in one database are automatically sent to another database. The
most common scenario is when you have a central database replicating changes to satellite
databases, and possibly vice versa.
• MOVE: When completing a restore, the MDF and LDF files that are being restored have to be placed
where they were backed up from. However, by using this option, you can change that location.
• RECOVERY | NORECOVERY | STANDBY: These three options are the same, and in the same order, as
their counterparts (in parentheses) in the wizard:
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 205
• RECOVERY (Leave the Database Ready to Use): This option defines that after the restore is
finished, users can immediately connect to and work with the data. If a transaction is in
progress, such as updating rows in a table, then not until the updates have been rolled back
and therefore the table is back in its “original” state will connections to the database
be allowed.
• NORECOVERY (Leave the Database Non-operational): With this option, you are indicating
that the database has been partially restored, and you are unsure whether you need to
perform additional actions. If a transaction is in progress, such as inserting rows in a table,
then the insertions will not be rolled back. This allows additional restores to get to a specific
point in time.
• STANDBY (Leave the Database in Read-Only Mode): A combination of the first two options.
If a transaction is in progress, such as deleting rows in a table, then the deletion will be
rolled back. However, the changes are also in a separate file, so that any of these actions
that have been rolled back can be reapplied. This might happen if several actions occurred
within a transaction and some can be reapplied.
• REPLACE: This works the same as the wizard option Overwrite the Existing Database.
• RESTART: If a restore is stopped partway through, then using this option will restart the restore
at the point it was stopped.
• RESTRICTED_USER: Use this with the RECOVERY option to only allow users in specific restricted
groups to access the database. Use this to allow further checking by a database owner, or by
the dbowner, dbcreator, or sysadmin roles.
• STOPAT | STOPATMARK | STOPBEFOREMARK: Used to specify a specific date and time at which to stop
the restore.
The syntax for restoring the transaction log is exactly the same, with the only difference being
the definition: you are completing a LOG rather than a DATABASE restore:
RESTORE LOG { database_name | @database_name_var }
[ FROM
[ WITH
[ { CHECKSUM | NO_CHECKSUM } ]
[ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
[ [ , ] FILE = { file_number | @file_number } ]
[ [ , ] KEEP_REPLICATION ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
[ ,...n ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] { RECOVERY | NORECOVERY | STANDBY =
{standby_file_name | @standby_file_name_var } }
]
[ [ , ] REPLACE ]
[ [ , ] RESTART ]
[ [ , ] RESTRICTED_USER ]
[ [ , ] { REWIND | NOREWIND } ]
[ [ , ] STATS [=percentage ] ]
[ [ , ] { STOPAT = { date_time | @date_time_var }
| STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER datetime ]
206 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
| STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER datetime ]
} ]
[ [ , ] { UNLOAD | NOUNLOAD } ]
]
Try It Out: Restoring Using T-SQL
1. Open up an empty Query Editor pane and enter the following code. This will add the column that we want to see
“removed” after a restore.
USE ApressFinancial
GO
ALTER TABLE ShareDetails.Shares
ADD DummyColumn varchar(30)
2. Now replace this code with the restore code that follows. Don’t execute any of the code just yet, as this piece of
code is the first part only. Recall that when performing the backups, FILE 2 was the FULL backup taken. This
is what the first restore will do.
■Note Ensure that the FROM DISK option is all on one line. Also recall that FILE = 2 may be FILE = 3 or any
other number, depending on the backups taken, and this may be the case with different file numbers as you progress.
USE Master
GO
RESTORE DATABASE [ApressFinancial]
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\
ApressFinancial.bak' WITH FILE = 2,
NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
3. Continue the code with the second part of the restore, which will be the differential backup restore. This uses
FILE 3 from the backup set.
RESTORE DATABASE [ApressFinancial]
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\
ApressFinancial.bak' WITH FILE = 4,
NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
4. The final part of the restore operation is to restore the transaction log file. Once all this code is in, you can run it all.
RESTORE LOG [ApressFinancial]
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\
ApressFinancial.bak' WITH FILE = 5,
NOUNLOAD, STATS = 10
5. Once the code has fully executed, you should see results similar to those listed here:
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 207
13 percent processed.
22 percent processed.
31 percent processed.
40 percent processed.
54 percent processed.
63 percent processed.
72 percent processed.
81 percent processed.
90 percent processed.
100 percent processed.
Processed 176 pages for database 'ApressFinancial', file 'ApressFinancial'
on file 2.
Processed 1 pages for database 'ApressFinancial',
file 'ApressFinancial_log' on file 2.
RESTORE DATABASE successfully processed 177 pages in 0.284 seconds
(5.105 MB/sec).
24 percent processed.
48 percent processed.
72 percent processed.
97 percent processed.
100 percent processed.
Processed 32 pages for database 'ApressFinancial', file 'ApressFinancial'
on file 3.
Processed 1 pages for database 'ApressFinancial',
file 'ApressFinancial_log' on file 3.
RESTORE DATABASE successfully processed 33 pages in 0.066 seconds
(4.088 MB/sec).
100 percent processed.
Processed 0 pages for database 'ApressFinancial', file 'ApressFinancial'
on file 4.
Processed 4 pages for database 'ApressFinancial',
file 'ApressFinancial_log' on file 4.
RESTORE LOG successfully processed 4 pages in 0.013 seconds (2.441 MB/sec).
We can now move back to the ShareDetails.Shares table and check that the column added has now been removed.
You may have to perform a refresh within Object Explorer first to see the changes.
Restoring a database in production will in most instances take place under pressure, as the database will have become
corrupt or been inadvertently damaged. The production system is obviously down and not working, and we have irate
users wanting to know how long before the system will be up. This is hopefully the worst-case scenario, but it is that sort
of level of pressure that we will be working under when we have to restore the database. Therefore, having the correct
backup strategy for your organization based on full, differential, and transaction log backups is crucial. Full database
backups for a system that requires high availability so that the restore takes the least amount of time may be what you need.
Detaching and Attaching a Database
Now that we can back up and restore a database, we have other methods available for dealing with
the database. There may be a time in the life of our SQL Server database when we have to move it
from one server to another, or in fact just from one hard drive to another. For example, perhaps we
currently have ApressFinancial on our C drive, and this is getting full, so we would like to move our
database to another hard drive. Or perhaps we are moving from an old slower server to a new faster
208 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
server or a server on a better network. By detaching and reattaching the database, we can do this
simply and easily.
I would like to make a couple of points here; they may seem straightforward and really obvious,
but better to mention them than cause problems at a later stage. First of all, no updates can be occurring,
no jobs can be running, and no users can be attached. Secondly, just in case, take a full backup
before moving the database. This may add time to the process, but it is better to be safe than sorry.
Ensure that where you are moving the database to has enough disk space, not only for the move, but
also for expected future growth; otherwise, you will be moving your database twice. You should not
attach your database to a server without immediately completing a backup on the new server afterward;
this way, you can ensure that the databases are protected in their new state.
Detaching a database physically removes the details from the SQL Server master and msdb databases,
but does not remove the files from the disk that it resides on. However, detaching the database
from SQL Server then allows you to safely move, copy, or delete the files that make up the database,
if you so desire. This is the only way that a database should be physically removed from a server for
moving it.
Detaching and Attaching Using SQL Server Management Studio
We’ll start by using SSMS to detach and attach a database.
Try It Out: Detaching a Database
1. First of all, it is necessary to ensure that nobody is logged in to the database, and even if someone is, that
the user is not doing any updates. For the moment, I want you to ignore this and to have a connection. Ensure
that SQL Server Management Studio is running and that there is a Query Editor pane with a connection to the
ApressFinancial database. Find the ApressFinancial database in Object Explorer and ensure that is
selected. Right-click and select Tasks ➤ Detach.
2. This brings up the Detach Database dialog box for the ApressFinancial database, as shown in Figure 7-11.
We haven’t removed all the connected users, so you can do this by selecting the Drop Connections check box.
The second option, Update Statistics, means that the SQL Server statistics for indexes and so on will be updated
before the database is detached. The information is stored separately from the other data files in SQL Server, so
selecting this option ensures that when the database is detached that the files are not lost and therefore don’t
need re-creating. The status is Not Ready due to the message indicating that there is still “2 Active connection(s).”
Although you only have one Query Editor open, the second connection is for the T-SQL IntelliSense.
3. In this example select the Drop Connections box. However, in a production environment, this could be very dangerous,
so you should not select it without some thought. You can then click OK to finish detaching the database.
That’s it. The database is detached, is no longer part of SQL Server, and is ready to be removed or even deleted. If you
check the Object Explorer in SQL Server Management Studio, you will see that the database is no longer listed.
Detaching a database, although seemingly a simple and innocuous operation, has the potential to be fraught with problems and
worries. As the example demonstrated, ensuring that there are no users attached to the database at the time of detaching
is not as easy as it first may seem. Setting up the database options to eliminate connections or to stop updates is only possible
once everyone has been removed from connections to the database. There is no easy way of removing connections
safely, as you never know what an application with a connection to the database is doing. You could remove a connection
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 209
that is in the middle of processing. If you are going down the route of detaching the database, though, there is an obvious
reason to do this, such as moving servers and removing the database, so you would have a plan of action to do this. Users
would have been notified days or weeks in advance, and the database owner would have coordinated a date and time
when nobody should be connected. Also, the database owner would be around if there were any problems, and he or she
could make the decision to kill any connections left hanging around.
Figure 7-11. Detaching a database
Detaching the database is a process that removes entries within the SQL Server system tables to inform SQL Server that
this database is no longer within this instance of SQL Server and therefore cannot be used. It is as simple as that. If you
are removing the database completely, then you will need to delete the files from the directory they were created in.
It is possible to detach the database using a system stored procedure, although this does not let you kill the connections.
This has to be done via a T-SQL command.
We need to reattach the database before being able to demonstrate this, so let’s do that now. This would occur on our new
SQL Server instances after physically moving the files.
210 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
Try It Out: Attaching a Database
1. Within Object Explorer, highlight the Databases node, right-click, and select Attach.
2. This brings up the Attach Databases dialog box, shown in Figure 7-12. To add a database, click Add.
Figure 7-12. Options for attaching a database
3. This brings up the Locate Database Files explorer, shown in Figure 7-13. You can use this like other Windows
Explorers to locate where your database MDF files are. Once you find the database you want to reattach, highlight
it and then click OK.
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 211
Figure 7-13. Locating the database to attach
4. This brings you back to the Attach Databases dialog box with the details filled in, as you see in Figure 7-14. Take
a moment to look over the information in this dialog box. Any problems will be detailed in the Messages column.
It is possible to attach more than one database, but it is best to do databases one at a time.
5. This then leaves us to click OK to reattach the database. Moving to Object Explorer, you should see your database
at the bottom of the list, where it will remain until the explorer is refreshed.
Attaching a database involves informing SQL Server of the name and the location of the data files and the transaction log
files. This data can be placed anywhere on a computer, but it is recommended you place the data in a sensible location.
For example, the folders tempfiles or tobedeleted sport extreme names, but do demonstrate the unsuitability that should
be avoided.
When moving the data from one physical server to another, the data does not need to be in a subdirectory of Microsoft SQL
Server installation found under Program Files. In fact, in production environments, this is the last place you would locate
the data. You would generally want to keep these files away from any program files or the pagefile.sys file, because SQL
Server’s performance can be maximized when these files are separated. However, for the purpose of this book, placing the
data in the DATA directory under the instance of SQL Server is perfectly valid and acceptable.
Once the two data files have been copied, it is a simple process of using a couple of mouse clicks to attach these files into
the instance. What happens in the background, very basically, is that SQL Server takes the name of the database and the
location of the data files and places them into internal tables that are used to store information about databases. It then
scans the data files to retrieve information, such as the names of the tables, to populate the system tables where necessary.
212 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
Figure 7-14. Database located, preparing to attach
The main point to keep in mind is the database owner (see Chapters 1, 2, and 5 ). It is just as important to use a valid database
owner and not the sa login when attaching a database as it is when creating a database. The database, when it is
attached, will be given the owner of the login attaching the database.
Detaching and Attaching Using T-SQL
Detaching and attaching a database is an ideal way to move a database from one server to another
as part of an overall solution. It’s clean and simple and ideal if you are rolling out a “base” database
to many client sites, but it’s not the only way of doing it. Detaching a database is simply removing
it logically from a server, but keeping the physical files. This then allows these files to be moved to
anywhere, from another hard drive to a DVD, for further copying to a client computer if need be, and
then reattaching the database at the other end.
Detaching a database removes entries from the master and msdb databases. The physical backup
files will still be there, so if you do need to complete a restore after a detach and reattach, then you
can use the From Device option in the Restore Wizard to define the full location in the RESTORE T-SQL
command to get to those files.
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 213
■Note Detaching a database can only be done by a member of the db_owner role.
sp_detach_db [ @dbname= ] 'dbname'
[ , [ @skipchecks= ] 'skipchecks' ]
[ , [ @KeepFulltextIndexFile= ] 'KeepFulltextIndexFile' ]
The options are straightforward, with each being optional. If they are not supplied, then the
default value is mentioned within the following bulleted list:
• dbname: The name of the database to detach. If this option is missed, then no database will be
detached.
• skipchecks: NULL (the default) will update statistics. true will skip the updating of statistics.
• KeepFulltextIndexFile: true (the default) will keep all the full text index files that have been
generated with this database.
■Note Full text index files are special files that hold information about data set up for full-text searching, which
is an area outside the scope of this book. Basically, full-text searching gives the ability to search on all text in a
column or multiple columns of data, and is also functionality used by search engines.
You might be expecting that you would use a stored procedure called sp_attach_db to reattach
the database. This command does exist, but it will be made obsolete in future versions of SQL Server.
The correct syntax is a “specialized” CREATE DATABASE command:
CREATE DATABASE database_name
ON
FOR { ATTACH [ WITH
| ATTACH_REBUILD_LOG }
The syntax is easy to follow. The first option, ON, specifies the name of the primary database file
to attach, which has the mdf suffix. We will ignore the second option,
this is for a more advanced database.
The third option, ATTACH_REBUILD_LOG, is for situations where you wish to attach a database but
at least one transaction log file is missing. Specifying this option rebuilds the transaction log. No
database can be attached when SQL Server believes that there are missing files. If you do use this
option, then you will lose the full, differential, and transaction log backup chains that exist on SQL
Server, so complete a full backup after attaching to reestablish the backup baseline. This option
tends to be used when you deliberately wish to lose the transaction log file, such as a read-only
version of the database for reporting purposes.
■Note If you receive any error messages, then reattach all files associated with the database, not just the main
primary file.
We can now detach and reattach ApressFinancial.
214 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
Try It Out: Detaching and Reattaching a Database
1. The first test we will do is to try to detach ApressFinancial while there are still active connections so that we
can see what happens. Open up a Query Editor pane and point it to ApressFinancial database. Then open a
second pane and enter the sp_detach_db code as follows. Once you have done so, execute the code. Take note
that we are explicitly moving this connection to a “safe” system database, away from the database we wish
to detach.
USE master
GO
sp_detach_db 'ApressFinancial'
2. The results you will see will be similar to the following:
Msg 3703, Level 16, State 2, Line 1
Cannot detach the database 'ApressFinancial' because it is currently in use
3. Close the Query Editor pane opened earlier and any other Query Editor panes that have connections pointing to
ApressFinancial and then try rerunning the code again. This time you should see the following message:
Command(s) completed successfully.
4. You can also achieve the same result within the code and without needing to close your panes. If you run the
following code and move back to a pane that had a connection, you will be presented with a reconnection
pane. Once connected, if your default database is not ApressFinancial, then you won’t be able to change to
that database until you move out of single-user mode or use the pane that had the following code within it:
ALTER DATABASE ApressFinancial
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
5. Change SINGLE_USER to MULTI_USER once you test the following code:
ALTER DATABASE ApressFinancial
SET MULTI_USER
6. Now that the database has been detached, we need to reattach it, simulating a move to a new server. Enter the
following code in the same Query Editor pane. Replace the FILENAME parameters with the path to where your
database is located and ensure that the path is all on one line.
CREATE DATABASE ApressFinancial
ON (FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\
Data\ApressFinancial.MDF')
FOR ATTACH
7. After executing the code, you should see the following message:
Command(s) completed successfully.
You have now successfully detached and reattached the database.
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 215
Producing SQL Script for the Database
This section demonstrates a different method of backing up the structure of the database and the
tables and indexes contained within it by using T-SQL commands to complete this.
■Note Only the structure will be generated as T-SQL commands; no data will be backed up—only the schema
that is needed to re-create the actual database can be scripted here.
The usefulness of this procedure is limited and is really only helpful for keeping structure backups
or producing an empty database, but it is useful to know rather than going through the process of
copying the database with all the data when the data is not required.
This method tends to be used to keep the structure within a source repository such as Visual
SourceSafe. It is also useful for setting up empty databases when moving from development to testing or
into production.
Try It Out: Producing the Database SQL
1. Ensure that SQL Server Management Studio is running and that you have expanded the server so that you can
see the ApressFinancial database. Right-click, and select Tasks ➤ Generate Scripts. This brings up the
wizard shown in Figure 7-15 that allows the database to be scripted. Every attached database will be listed.
Select ApressFinancial and click Next.
■Note You can select the check box at the bottom of the screen, which will script all the objects if you wish. This
will enable the Finish button so that you can go straight to the end.
Figure 7-15. Scripting—selecting the database
216 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
2. On the second screen are a number of options about the scripting. Take a moment to look them over. Most of these
options should be clear to you from the setup options we have covered in setting up the database so far. A bulleted list
at the end of the example clarifies the options for you. Figure 7-16 shows the default settings. Click Next.
Figure 7-16. Options for the script
3. As shown in Figure 7-17, you are presented with a list of objects that you could script. At the bottom of the dialog
is a Select All option. Press this, as we want to script everything. Once you have the options you wish to script,
click Next. You will then be taken through each object group one at a time. Within each screen there will be a list
of possible objects you wish to script. For example, Figure 7-18 demonstrates what you will see when you get to
the dialog for the stored procedures. Every stored procedure within the database will be listed. As you go through
each dialog, click Select All and click Next.
■Note The Script Statistics option significantly increases the time taken to generate the script. Leave this option
off in most cases; it is really only useful when moving from a user test environment that is very similar to how the
system will work in production.
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 217
Figure 7-17. Options selected for scripting
Figure 7-18. Scripting for every stored procedure
4. After you have been presented with the dialogs for all the objects in the database of the areas selected in
Figure 7-16, where you should have selected every object, you are now presented with the screen shown
in Figure 7-19. This allows you specify how the script should be saved. There are three possibilities. Choose to
script to a new Query Editor and then select Next.
218 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
Figure 7-19. Where to store the script
5. This brings you to a summary screen, shown in Figure 7-20, where you can expand what has been selected. You
may find that this screen is not of much use, as there are so few screens within this wizard. However, you can
use it for categorizing what objects are to be scripted. Take a moment to investigate this screen.
Figure 7-20. Script summary
6. Click Finish. The wizard will start to generate the script. At the end, you will see a summary of how the script
production went. Any errors will be within the Message column on the right, as shown in Figure 7-21.
CHAPTER 7 ■ DATABASE BACKUPS, RECOVER Y , AND MAINTENANCE 219
Figure 7-21. Generating the script
The options available to you within the wizard are detailed here:
• Append to File: If you set this to true, then SQL Server will append the script to the file selected instead of overwriting
it.
• Continue Scripting on Error: If there are any problems in producing the script, you can decide if you wish to
continue scripting or not.
• Convert UDDTs to Base Types: As part of SQL Server, you can change the base data types, such as int, to
your own named type, so you could name a “copy” of int as myint. This is a bit more advanced, but if you
do this, then selecting true will convert myint back to int.
• Generate Script for Dependent Objects: A very useful option. If there are any dependencies on what you are
wanting to script and you haven’t selected that object to script, then there will be problems rebuilding the
object later. Selecting true means that these dependent objects will also be scripted.
• Include Descriptive Headers: This includes a date-time stamp as well as a short descriptive header of each
object as it is reached within the script.
• Include If NOT EXISTS: If you select all the objects to be scripted and set this to true, SQL Server will put a test
around each object so that if that object is already in the database when the script is run, it won’t be created.
There will be no test for specific columns when scripting a table, but there will be a test for the table itself.
• Script Behavior: You can generate a script for creating items or dropping items.
• Script Collation: If you wish the SQL Server collation to be scripted, enable this option. This is useful if you are
unsure of the collation the script will then be run against.
• Script Database Create: This specifies whether you wish a CREATE DATABASE statement to be scripted or not.
• Script Defaults: We have some default values that will be set on columns when rows are added. Setting this
to true will set these defaults.
• Script Extended Properties: Extra properties can be placed on every SQL Server object. These will be scripted
if you select true.
220 CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY , AND MAINTENANCE
• Script Logins: This scripts all Windows and SQL Server authentication logins.
• Script Object-Level Permissions: Each object will have permissions on who can do what. For example, on a
table, you can set up permissions on who can add, delete, or select the data. This option includes these options.
• Script Owner: This scripts the owner of the database if specified.
• Script Statistics: This specifies whether to script the SQL Server column and index statistics. It avoids rebuilding
them when re-creating the database using the script; however, it increases the time taken to build the script
as well as the size of the script.
• Script USE DATABASE: Between each object, this specifies whether to script a USE database statement or not.
It’s ideal if used with scripting-dependent objects.
• Script Check Constraints: This script checks constraints.
• Script Foreign Keys: Any foreign keys are scripted.
• Script Full-Text Indexes: If you have any full-text indexes, this indicates whether you want to script them or not.
• Script Indexes: This specifies whether to script table and view indexes.
• Script Primary Keys: This dictates whether to script primary keys or not.
• Script Triggers: For any trigger, this specifies whether you wish these to be within the script.
• Script Unique Keys: Any unique keys are scripted.
This concludes our look at the different methods of backing up, restoring, moving, and scripting databases. While this
covers every way of ensuring your database structure and data should never be lost, you still need to maintain the database
on a regular basis. This is what we will take a look at in the next section of this chapter.
Maintaining Your Database
At this point, we have now created a backup and performed a restore of the example database. We
have also covered the different methods to back up and restore the database. However, we have no
real plan for regular maintenance and detection and reporting of problems in our database strategy.
Any jobs for backup of the database or transaction log that we have demonstrated so far are held as
single units of work called steps. Not only that, there is nothing in place that will look after the data
and indexes held within the database to ensure that they are still functioning correctly and that the
data is still stored in the optimal fashion. Without a process that runs regularly, we would need to
perform all of this by hand regularly and check the results each time. What a waste of time, and boring
to boot!
This section will demonstrate building a plan and then checking on the plan after it has run
to ensure that all has gone well with it. This plan will perform regular backups and checks on the
database and keep it in optimum health. This section will then show you how to set up the ability
to e-mail results.
To do this, we will use the Database Maintenance Wizard, which will monitor corruption within
the database, optimize how the data is stored, and back up both the database and transaction logs.
Finally, the wizard will schedule all of this to occur at regular intervals. You will also see how to set
up and configure SQL Server Database Mail.
Some areas of this chapter, like the backup screens, are straightforward, as they were covered
earlier in the chapter; however, this now brings the whole maintenance of the database into one
wizard.
