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

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.
Dewson_958-7C07.fm Page 202 Tuesday, July 1, 2008 9:33 AM
CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY, AND MAINTENANCE
203
Figure 7-9. Restoring a database—Options tab

[ [ , ] 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:
Dewson_958-7C07.fm Page 204 Tuesday, July 1, 2008 9:33 AM

[ 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 ]
Dewson_958-7C07.fm Page 205 Tuesday, July 1, 2008 9:33 AM
206
CHAPTER 7
■ DATABASE BACKUPS, RECOVERY, AND MAINTENANCE
| STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER datetime ]
} ]
[ [ , ] { UNLOAD | NOUNLOAD } ]
]

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:
Dewson_958-7C07.fm Page 206 Tuesday, July 1, 2008 9:33 AM
CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY, 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'

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 occur-
ring, 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 after-
ward; 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 data-
bases, 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 dan-

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, high-
light it and then click OK.
Dewson_958-7C07.fm Page 210 Tuesday, July 1, 2008 9:33 AM
CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY, 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.
Dewson_958-7C07.fm Page 211 Tuesday, July 1, 2008 9:33 AM
212
CHAPTER 7

■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 <filespec> [ , n ]
FOR { ATTACH [ WITH <service_broker_option> ]
| 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, <service_broker_option>, as
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.
Dewson_958-7C07.fm Page 213 Tuesday, July 1, 2008 9:33 AM
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

You have now successfully detached and reattached the database.
Dewson_958-7C07.fm Page 214 Tuesday, July 1, 2008 9:33 AM
CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY, 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
Dewson_958-7C07.fm Page 215 Tuesday, July 1, 2008 9:33 AM
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.

Dewson_958-7C07.fm Page 218 Tuesday, July 1, 2008 9:33 AM
CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY, 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 over-
writing 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.
Dewson_958-7C07.fm Page 219 Tuesday, July 1, 2008 9:33 AM
220

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.
Dewson_958-7C07.fm Page 220 Tuesday, July 1, 2008 9:33 AM
CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY, AND MAINTENANCE
221
Creating a Database Maintenance Plan
Now that the database is up and built and the tables are there, it really is time to start considering a
whole database maintenance plan before data is entered. This will cover database corruption through to
inadvertent errors in development. Even though corruption is rare in SQL Server, it can be caused
when SQL Server loses power abruptly, for example, or through hardware issues such as a mother-
board failure or someone removing the network cable.
There are many areas to building a maintenance plan, and this section covers a lot of them. One
or two areas are only touched on, as they are quite advanced and will not be covered in this book. You
will still need a little background so that you can see how crucial this area is, and we can move on to
those more advanced areas a bit later on.
A single maintenance plan can be built for one database or several databases. A single plan can be
set up for system databases and all user databases by selecting those options at the start of the Database
Maintenance Wizard. However, it is recommended that you create a plan for all system databases, but
have a separate maintenance plan for each separate user database. The logic behind this is that each user
database will have its own needs, its own overnight routines, and even its own people for callout when

222
CHAPTER 7
■ DATABASE BACKUPS, RECOVERY, AND MAINTENANCE
3. From the Object Explorer, find the Management Node and expand it, and you should find Maintenance Plans as
the top item. Right-click and select the second option, Maintenance Plan Wizard. This starts the wizard.
4. Figure 7-22 shows the first screen of the wizard. Once you have read it, click Next.
Figure 7-22. Maintenance Plan Wizard, first screen
5. Enter a suitable name and description for the maintenance plan. You can then choose the server that the main-
tenance plan is on. This covers instances when your Management Studio is connected to more than one server.
For example, if you have a connection to your ISP that you have a SQL Server installation on, and the ISP wants
you to set up your own maintenance plan, you would change the server to that location. More likely, you will need
to do this when you are maintaining more than one server at your company installation The server you are con-
nected to will be the default. Select the authentication method you wish the plan to connect to the server as, as
shown in Figure 7-23. It is possible to run tasks under either separate schedules or one schedule. Choose separate
schedules when you want to space the tasks out or run them simultaneously. Choose a single schedule for the
entire plan when you want to run the tasks synchronously. This is the option you want to take at this time.
6. At the bottom of the screen shown in Figure 7-23, it is possible to either set the Schedule to a date and time or
leave the plan with no schedule and to run it manually. The aim is to run this automatically, so click the Change
button. You will now see the screen shown in Figure 7-24. The scheduling of the data optimization should be at
a quiet time, and unless the database is updated heavily, this maintenance plan choice will not be required frequently.
Running a maintenance plan can be quite intense for the server and should only be done during low-usage
hours. For the sake of ApressFinancial, it could be as infrequent as monthly; however, in the initial setup of
the database, while the input of data might be heavy, set this up as a weekly task for now. Once done, click OK,
and then click Next when you return back to the screen shown in Figure 7-23.
Dewson_958-7C07.fm Page 222 Tuesday, July 1, 2008 9:33 AM
CHAPTER 7 ■ DATABASE BACKUPS, RECOVERY, AND MAINTENANCE
223
Figure 7-23. Selecting the server for the maintenance plan
Figure 7-24. Defining the schedule
Dewson_958-7C07.fm Page 223 Tuesday, July 1, 2008 9:33 AM