436
C
HAPTER
33
Efficient backups without indexes
CREATE NONCLUSTERED INDEX ncix_Table1 ON dbo.Table1 (Col1)
WITH DROP_EXISTING --, ONLINE = ON
ON NCIX_FG;
The
DROP_EXISTING
option causes the newly created index to be created as the
replacement of the existing index, without needing to explicitly drop the existing
index (which could damage query performance for queries that might be in flight,
while the index is dropped). The
ONLINE
option can be used to further reduce con-
currency impact with
SQL
Server 2005 or 2008 Enterprise Edition.
The
ON

NCIX_FG
clause is the main focus of our attention in this statement, which
defines the new location for the nonclustered index.
Backing up only the PRIMARY filegroup
Now that we’ve successfully separated our nonclustered indexes (one only) from the
PRIMARY
filegroup into a separate filegroup, it’s now possible to perform a tables-only
filegroup backup. Instead of issuing a standard
BACKUP

PRIMARY
filegroup backup dif-
fers depending on whether you’re restoring only to perform data extraction purposes
(re-creation of indexes not required), or whether you intend to bring the database
fully online (re-creation of indexes is required).
Restoring from the
PRIMARY
filegroup backup to perform data extraction is sim-
ple and faster than restoring a full backup. On the other hand, restoring the database
back to its full state to bring it back into a production role—including a rebuild of all
nonclustered indexes—requires more steps than from a simple full database backup.
Let’s quickly review both of the steps required in each scenario.
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
437
Restoring the PRIMARY filegroup backup
Restoring for extraction only
When restoring a database from the
PRIMARY
(tables-only) filegroup for data extrac-
tion/examination only, you include
FILEGROUP
and
RECOVERY
(or
NORECOVERY
if you
also wish to restore a chain of log backups) clauses in your
RESTORE
command, as

is not online.
In this scenario, you might need to either remove index hints, or explicitly hint
SQL
Server to access the table via its heap or clustered index storage, instead of via the
nonclustered index (which is offline).
Restoring for production use
Restoring a database from a
PRIMARY
filegroup backup for production use does
require a few more steps than restoring from a regular full database backup. The most
significant of these involves rebuilding nonclustered indexes from the underlying
tables.
In summary, these are the steps required to bring a database fully online from a
PRIMARY
filegroup backup:
1
Restore the primary filegroup backup and recover the database.
2
Script out index definitions from the
PRIMARY
filegroup, using a new filegroup
name.
3
Restart
SQL
Server in single-user mode (with
-m
startup parameter).
4
Connect to

PRIMARY
filegroup, regardless of where the physical index stor-
age allocations are located. This is a crucial fact that enables this tech-
nique to work.
Before walking through each of these steps, two issues associated with this technique
should be discussed:

An update to system views is required to re-create nonclustered indexes (step 5).

The nonclustered index filegroup’s name must be changed (step 7).
ISSUE 1: INDEX DEFINITIONS MUST BE REMOVED BY UPDATING A SYSTEM VIEW
The process of re-creating indexes following restoration of the
PRIMARY
filegroup
requires deleting existing index metadata from the
sys.sysindexes
system view prior
to re-creating indexes (step 3 in the previous list). This fact might discourage some
from using this technique, but it should be pointed out that updating this system view
does not, in and of itself, compromise the integrity of the database, as the database
tables that contain the real data in our database are stored within the
PRIMARY
file-
group and are not affected by the update of this system view.
WARNING
Updating a system catalog is an operation that’s not supported by
Microsoft. An alternative supported solution is to reconstruct the data-
base by scripting its definition, re-creating the database, exporting all
table data from the backup, and re-importing to the new re-created
database.

MARY
filegroup backup without using any system catalog updates.
ISSUE 2: NEW NONCLUSTERED INDEX FILEGROUP MUST BE RENAMED
Another issue is that the filegroup that contained the nonclustered indexes in the
source database (
NCIX_FG
in our example) cannot be fully removed from the
restored database without further system catalog updates.
This is less of a problem though, as another filegroup can be added to the
restored database and nonclustered indexes be built into it. The name of a filegroup
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
439
Restoring the PRIMARY filegroup backup
is rarely important to an application, so this doesn’t pose a serious problem—simply
create another filegroup (for example
NCIX_FG2
) and rebuild nonclustered indexes
into it instead. This is what we’ll do in our example.
Restoring for production use—step by step
Let’s walk through the individual steps required to restore our database for produc-
tion use from a
PRIMARY
filegroup backup.
STEP 1—RESTORE THE PRIMARY FILEGROUP BACKUP AND RECOVER THE DATABASE
The first step is identical to the restore process for extraction only. Assuming our
PRI-
MARY
filegroup backup remains located in the root of E:\ drive, execute the following
command:

CREATE

INDEX

WITH

DROP_EXISTING
. Each of these generates an error
whether executed from a normal connection or dedicated administrator connection
(
DAC
), because each requires existing index allocations to be available during execu-
tion of the command.
Instead, we’ll script index definitions out to a file and use the script later (step 8),
to rebuild the indexes, after we have dropped index definitions from the sys.sysin-
dexes system catalog and created a new nonclustered index filegroup to contain the
new indexes.
To generate the index scripts, use the utility stored procedure (
SP
) described later
in this chapter, named
usp_Manage_NCIX_FileGroup
. This
SP
accepts two parame-
ters—
@ScriptOnlyOrExecute

(nvarchar(6))
and

EXEC usp_Manage_NCIX_FileGroup N'Script', N'NCIX_FG2'
Output from the execution of this procedure should contain a list of
CREATE

INDEX
statements, one per nonclustered index in the database. Save this output to a script
file (for example NCIXScripts.sql) for later use in step 7. Note that each
CREATE

INDEX
statement will include the filegroup name
NCIX_FG2
. When we run this script later,
nonclustered indexes will be re-created within this filegroup, so we’ll need to add it to
the database (which we do in step 7).
STEP 3—RESTART SQL SERVER IN SINGLE-USER MODE
Before we can re-create our nonclustered indexes, we need to remove existing index
definitions from the database by deleting them from the sys.sysindexes system catalog.
This requires us to connect to
SQL
Server in single-user mode, using the
DAC
before
we can perform the
DELETE
.
Starting
SQL
Server in single-user mode requires adding the
-m

Server 2008).
8
Click Apply, and click
OK
in the warning dialog box that appears.
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
441
Restoring the PRIMARY filegroup backup
9
When the Properties dialog box closes, right-click on the
SQL
Server instance
and select Restart.
STEP 4—CONNECT TO SQL SERVER USING DEDICATED ADMINISTRATOR CONNECTION
Once the
SQL
Server instance has been restarted, connect through the
DAC
to allow
updates to the system catalogs. Connect or reconnect an
SSMS
connection by adding
ADMIN:
to the beginning of the server name in your connection dialog box, as dis-
played in figure 5.
STEP 5—DELETE NONCLUSTERED INDEX DEFINITIONS
Once connected through the
DAC
, delete existing nonclustered index definitions

-m
startup parameter, follow the steps outlined in step 3 but remove
;

-m
from the Startup Parameters configuration before restarting the
SQL
Server
instance.
STEP 7—CREATE A NEW, RENAMED FILEGROUP
Once the
SQL
Server instance has been restarted in multi-user (normal) mode, a new
filegroup must be added prior to re-creating the database’s nonclustered indexes. This
is a two-step process; first we create the filegroup by executing the following command:
ALTER DATABASE BackupTest
ADD FILEGROUP NCIX_FG2;
Then, we add at least one file to the filegroup by executing the following command:
ALTER DATABASE BackupTest
ADD FILE (
NAME = N'NCIX_FG2_F1'
, FILENAME = N'C:\ NCIX_FG2_F1.ndf')
TO FILEGROUP NCIX_FG2;
NOTE
The name used for this new filegroup must match the name provided to
the parameter
@NCIXFGName
in step 2 because the script generated in step
2 will attempt to build nonclustered indexes into this filegroup when it is
run in step 8.

N'Execute'
to execute the scripts (moving the
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
443
usp_Manage_NCIX_Filegroup
indexes into the filegroup named in the other parameter
@NCIXFGName
) or
N'Script'
to script out the
CREATE

INDEX
statements when restoring the database.
Note that slightly different
CREATE

INDEX
statements are generated when
N'Exe-
cute'
is supplied to the
@ScriptOnlyOrExecute
parameter. When
N'Execute'
is sup-
plied,
WITH


'.

EXEC usp_Manage_NCIX_FileGroup N'Script', N'NCIX_FG2'
CREATE PROCEDURE [dbo].[usp_Manage_NCIX_FileGroup]
@ScriptOnlyOrExecute NVARCHAR(6) /* N'Script' or N'Exec' */
, @NCIXFGName NVARCHAR(255) /* the name new filegroup to move NCIXs into*/
AS
SET NOCOUNT ON
/*cursor variables*/
DECLARE @tnm NVARCHAR(128), @ixnm NVARCHAR(128), @cnm NVARCHAR(128)
, @schnm NVARCHAR(128), @isPK BIT, @isUn BIT, @isIncCol BIT
, @cixnm NVARCHAR(128), @ctbschnm NVARCHAR(256)
, @ixcr NVARCHAR(4000), @ixcnt INT, @indid INT, @order NVARCHAR(5)
, @inccols NVARCHAR(4000)
SELECT @ixcnt = 0, @cixnm = N'', @ctbschnm = N''
/*open cursor over schema / table / index / columns*/
DECLARE cr CURSOR FOR
SELECT ss.name, so.name, si.name, N'[' + sc.name + N']', is_primary_key
, CASE WHEN is_descending_key = 0 THEN N'' ELSE N' DESC' END
, is_included_column, si.index_id, is_unique
FROM sys.schemas ss
JOIN sys.objects so on ss.schema_id = so.schema_id
JOIN sys.indexes si on so.object_id = si.object_id
JOIN sys.index_columns ic ON si.object_id = ic.object_id
AND si.index_id = ic.index_id
JOIN sys.columns sc ON ic.object_id = sc.object_id
AND ic.column_id = sc.column_id
WHERE OBJECTPROPERTY (so.object_id, 'IsUserTable') = 1
AND si.index_id > 1 AND si.index_id < 255 /*only interested in NCIXs*/
ORDER BY ss.name, so.name, si.name, ic.index_column_id

+ N' WITH (DROP_EXISTING = ON'
+ CASE WHEN SERVERPROPERTY('EngineEdition') = 3
THEN N', ONLINE = ON)' ELSE N')' END
+ N' ON [' + @NCIXFGName + ']'
/*execution of create NCIX in other FG occurs
on first row of NEXT index*/
PRINT @ixcr; IF @ScriptOnlyOrExecute = N'Exec' EXEC(@ixcr)
END
SELECT @cixnm = @ixnm, @inccols = ''
SET @ixcr = N'create ' + CASE WHEN @isUn = 1
THEN N'unique ' ELSE N'' END
+ N'nonclustered index [' + @ixnm + N'] on ['
+ @schnm + N'].[' + @tnm + N'] (' + @cnm + @order
+ N', '
END
ELSE
BEGIN
/*if same index, build key of included cols csv list*/
IF @isIncCol != 0 SET @inccols = @inccols + @cnm + N', '
ELSE SET @ixcr = @ixcr + @cnm + @order + N', '
END
FETCH NEXT FROM cr INTO @schnm, @tnm, @ixnm, @cnm, @isPK, @order,
@isIncCol, @indid, @isUn
END
/*should usually be one last index (assuming there were any)*/
IF @ixcnt > 1
BEGIN
SELECT @ixcr = LEFT(@ixcr, LEN(@ixcr) - 2) + N')'
+ CASE WHEN LEN(@inccols) > 1 THEN N' INCLUDE ('
+ LEFT(@inccols, LEN(@inccols) - 2) + N')' ELSE N'' END

The script will not attempt to move tables. Heaps or clustered indexes are elim-
inated by the cursor query’s
WHERE
filter:
and si.index_id > 1 and si.index_id < 255"

Unique definitions, key order, and included columns are all also preserved, so
nonclustered indexes should be moved identically with their current definitions.

Each individual
CREATE

INDEX
statement is also printed, allowing easy review of
the commands that have been dynamically executed by the script.
Planning before moving NCIXs
into a dedicated filegroup
Using the usp_Manage_
NCIX
_FileGroup utility stored procedure makes moving all of
your database’s nonclustered indexes into a new, dedicated nonclustered index file-
group an easy process, but there are some important considerations that need to be
addressed before doing this.
Moving NCIXs temporarily requires additional disk space
As nonclustered indexes are moved into the new, dedicated nonclustered index file-
group, extra space needs to be consumed in the filesystem by the filegroup to allocate
new page extents to contain the moved nonclustered indexes.
At the same time, space is being freed within the
PRIMARY
filegroup (as each non-

WHERE OBJECTPROPERTY (object_id, 'IsUserTable') = 1
AND index_id > 1 AND index_id < 255
Here is a similar query for
SQL
Server 2000:
SELECT SUM(dpages) * 8192.0 / 1048576 as NCIXs_Mb
FROM sysindexes
WHERE OBJECTPROPERTY (id, 'IsUserTable') = 1
AND indid > 1 AND indid < 255
Moving NCIXs creates empty space in PRIMARY filegroup
Once nonclustered indexes have been moved out of the
PRIMARY
filegroup to a new
dedicated nonclustered index filegroup, a significant amount of space will have been
freed within the
PRIMARY
filegroup.
This space can be returned to the filesystem by shrinking the filegroup’s files via
DBCC

SHRINKFILE
, but using this command will significantly fragment any clustered
indexes within the filegroup.
Another option is to add another dedicated filegroup for table storage (heaps
and clustered indexes), leaving the
PRIMARY
filegroup for system tables only. The
process of building this additional filegroup requires even more filesystem space, but
allows you to re-create the table storage structures within it without fragmenting
those structures. After completion of this step, you can shrink the

FILEGROUP
clause to the
RESTORE LOG
commands. Consider this
example:
RESTORE LOG StandbyDatabase
FILEGROUP = 'PRIMARY'
FROM DISK = 'E:\PrimaryDatabase_LogBackup_20090301090005.lbak'
WITH NORECOVERY
Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
447
Summary
Using the
FILEGROUP
clause directs the
RESTORE
command to ignore entries in the
transaction log that belong to the dedicated nonclustered index filegroup, only
restoring log entries that relate to tables (ignoring log entries that relate to nonclus-
tered indexes).
An advantage from combining this technique with log shipping and piecemeal
restore is that initialization of the log shipping standby database is faster because a
smaller tables-only filegroup backup only needs to be copied from the log shipping
primary server to the standby server and restored to the standby database. Unfortu-
nately, there’s no way to eliminate index-related transaction log entries from the log
backup files, so this technique does not solve the widespread problem of routine
index maintenance breaking log shipping systems.
On the other hand, it might be considered a disadvantage that this technique
complicates the process of bringing the standby database online in the event of a

C
HAPTER
33
Efficient backups without indexes
make the difference between being able to store a second backup file rather than
only a single full database backup.
Perhaps Microsoft might consider including a feature that allows backups to be
taken of table data only, without requiring updates to the system schema, in a future
release of
SQL
Server. Such a feature might even extend to filtering log backups to
contain only table-related entries so that log shipping standby servers can be kept in a
tables-only state without needing to copy all transaction log entries (including unnec-
essary index entries) during log shipping.
Although some might consider the updates to system catalogs or increased time
required to restore a database as unacceptable, the benefits gained every day in disk
space, backup processing time, and energy saved should outweigh these issues in
many cases.

About the author
Greg Linwood is the Managing Director of MyDBA—a dedicated
SQL
Server support
business—and also Australian Director of SQLskills, a specialist provider of
SQL
Server training. Greg has worked in a range of developer and
DBA
capacities with
SQL
Server since 1993, and was the first Australian to receive Microsoft’s

database mirroring to seamlessly upgrade from
SQL
Server 2005 to
SQL
Server 2008
with a 10–15-second outage. Another example is using database mirroring to move
multiple terabytes of data from one storage system to another with a 10–15-second
outage. This chapter reveals these secret techniques.
Why should I use database mirroring?
SQL
Server 2005 introduced database mirroring, and
SQL
Server 2008 enhanced it.
Back in the pre-
SQL
Server 2005 days, you had several choices for high availability
solutions with
SQL
Server, including failover clustering, log shipping, and replica-
tion. In many situations, database mirroring offers key advantages over all older
technologies. It is simple to set up and administer, it offers fast automatic failover, it
works at the database level (rather than at the instance or table level), and it can be
used with commodity-level hardware.
Database mirroring offers fast failover (usually much faster than failover cluster-
ing), and automatic failover (unlike log shipping or replication). It also avoids the
single point of failure that you have with the shared storage in failover clustering.
In addition, it is much easier to administer than failover clustering (where adding
and removing drives can be pretty painful, for example).
How does database mirroring work?
If you are unfamiliar with how database mirroring works, here is a quick primer.

DDL
and
DML
) is sent over your
network to the mirror, where it is replayed on the mirror. Depending on how your
database mirroring is configured, this may be happening synchronously or asynchro-
nously. You must be running in high-safety mode (synchronous) with a witness, to
allow automatic failover. If you are running Enterprise Edition of
SQL
Server, you can
run in high-performance mode (asynchronous), which does not allow automatic
failover, but can still be useful for many situations.
With database mirroring, you have two complete copies of your data, which can be
a good thing. Failover clustering uses shared, external storage, usually a storage area
network (
SAN
), where drives are owned by a particular node in the cluster. Shared stor-
age is a common single point of failure. If unavailable, the cluster service will not start,
and
SQL
Server will not start on the cluster.
From a storage perspective, having a second copy of the data with the mirror can
be expensive, but it protects you from the single point of failure you have with a
failover cluster (assuming the mirror database is on a different storage system than
the principal database). This second copy of your data is ready to go after recovery has
completed. The mirror database can be available much faster than a database
restored from backups or
SAN
snapshots. As a
DBA


Amount of
I/O
capacity, space, and
RAID
level on separate storage devices
Using best practices is important to ensure that the mirror can keep up during nor-
mal operation and especially when you do log-intensive operations like index rebuilds
or reorganizations. This will also ensure that you can comfortably run your workload
on the mirror hardware for an extended period.
Make sure that both the principal and mirror are running a 64-bit version of Win-
dows Server 2008 or better. Windows Server 2008 R2 will be 64-bit only; and it is likely
that the next version after
SQL
Server 2008 will be 64-bit only; therefore, time is run-
ning out on the 32-bit era. Improved memory management alone is a great reason to
switch to a 64-bit version.
You should choose a 64-bit version of the Enterprise Edition of
SQL
Server 2008,
because Enterprise Edition has several valuable enhancements (such as parallel log
restore and log stream compression) that are important for database mirroring.
SQL
Server 2008 native backup compression is helpful when you are initially setting up a
mirror, because your backups and restores will be smaller and will usually complete
more quickly. The compressed backup files will be easier to move from the principal
to the mirror (because they are smaller).
SQL
Server 2008 also offers automatic page
repair for database mirroring.

Licensed to Kerri Ross <[email protected]>
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.