## MS BackOffice Unleashed

- Creating Devices
  - Initializing Database Devices
  - Designating Default Devices
  - Mirroring Database Devices
  - Resizing Database Devices
  - Creating Backup Devices
  - Getting Information on Devices
  - Dropping Devices
- Creating Databases
  - Changing the Ownership of a Database
  - Changing Database Options
  - Renaming a Database
  - Changing the Size of a Database
  - Getting Information About Databases
  - Dropping Databases
- Using Segments
  - Adding Segments
  - Extending Segments
  - Getting Information About Segments
  - Dropping Segments
- Managing Security
  - Managing Security at the Server Level
    - Standard Security
    - Integrated Security
    - Mixed Security
  - Managing Security at the Database Level
    - Adding Groups
    - Adding Users
    - Getting User and Group Information
    - Dropping Aliases, Users, Groups, and Logins
  - Managing Security at the Object Level
- Backup and Recovery
  - Backing Up and Restoring Tables
  - Backing Up and Restoring Databases
  - Incremental Backup and Recovery
  - Recovering the Master Database
- Summary

#
— 30 —

##
SQL Server Administration

After SQL Server has been successfully installed on a server, the system administrator undertakes various responsibilities to manage it. Some of these responsibilities include creating database devices, managing space on existing devices, creating databases and monitoring space usage, creating segments, creating backup devices, backing up and restoring databases, creating SQL Server logins, managing security, configuring SQL Server for optimal performance, designing databases, performance, and tuning, and diagnosing SQL Server problems. Depending on the size of the applications and the number of SQL Servers in an organization, one or more system administrators might be assigned to perform these functions. SQL Server provides a special login called sa (System Administrator). This login is responsible for managing SQL Server. Anyone who can connect to SQL Server using the sa login and password can perform system administrative tasks.

> > SQL Server provides a graphical tool, called SQL Enterprise Manager, that enables system administrators to manage multiple SQL Servers in an organization. Although graphical tools are convenient for performing day-to-day operations, they do not provide any details on how a particular task is executed at SQL Server. For this reason, this chapter focuses on the commands that are used to accomplish such tasks (such as creating devices, databases, segments, and so on). You can send all these commands to SQL Server by using a query tool such as ISQL/W.

###
Creating Devices

SQL Server supports multiple databases, both system and user. Before a user database can be created on a SQL Server, it is necessary to initialize database devices on which the various user databases can be created. A database device can hold one or more databases, depending on the space available on the device.

####
Initializing Database Devices

A database device is a file on an operating system, and it must be initialized before it can be used for creating databases on it. A database device on a SQL Server is created (or initialized) by use of the DISK INIT T-SQL. command. After a device has been successfully initialized, multiple databases can be created on that particular device. This is the syntax of the DISK INIT command:

    DISK INIT NAME = "logical name of the device",

     PHYSNAME = "physical name ",

     VDEVNO = device number,

     SIZE = size of device on 2K blocks

     [, VSTART = virtual address]

All the parameters of the DISK INIT command are described here:

- NAME (mandatory parameter). This is the logical name of the device. A logical name is a method of mapping a physical filename to a more meaningful name. After a device has been initialized, it is always referred to and accessed by its logical name. The logical name of the device is meaningful only within SQL Server; it has no significance outside SQL Server. This name should conform to SQL Server naming rules (for example, the name cannot exceed 30 characters and cannot have embedded spaces in it).
- PHYSNAME (mandatory parameter). This is the actual physical location of the file. This should be a valid path and must conform to operating system rules for file naming conventions. The account name under which SQL Server is running should have permission to create this file. SQL Server does not enable you to create a database device across the network.
- VDEVNO (mandatory parameter). This parameter stands for virtual device number. Each database device is assigned a unique VDEVNO ranging from 0 to 255. Because device number 0 is reserved for the MASTER device, the legal values for this parameter are 1 through 255. When you create a device, you must provide a VDEVNO number that is not in use. To find out the virtual device numbers that are in use, execute the sp_helpdevice system stored procedure.
- SIZE (mandatory parameter). This parameter specifies the size of the device in terms of 2K blocks. For example, a value of 8192 for this parameter creates a 16M device (8192*2K). The minimum size of a device is 1M.
- VSTART (optional parameter). This parameter specifies the starting virtual page number in a device. It is usually not specified during DISK INIT. If specified, however, it should be set to 0.

Only an sa can issue a DISK INIT command, and permissions cannot be granted on this command to other users. The following example creates a logical device called test_dev that is 64M in size:

    DISK INIT NAME = "test_dev",

     PHYSNAME = "c:\dbdata\test_dev.dat ",

     VDEVNO = 2

     SIZE = 32768

> > As a result of the DISK INIT command, a row is inserted into the sysdevices system table in the master database. Information such as logical name, physical name, type of device (database or backup), status (default, mirrored), and size is stored in this table.

####
Designating Default Devices

SQL Server enables you to designate database devices as default devices. By designating default devices, you do not have to provide the names of database devices at the time of database creation. In such cases, space for a database is allocated from the default devices. The devices are used in the order of their logical names; that is, when space on the first device is consumed, the next device is used for further allocation. A device can be designated as a default device by use of the sp_diskdefault system stored procedure. The syntax for sp_diskdefault is as follows:

    sp_diskdefault "logical name" , <defaulton | defaultoff>

When a device is created using the DISK INIT command, the device is created with the defaultoff option. The following example sets device test_dev as a default device:

    Exec sp_diskdefault "test_dev", defaulton

> > The status column of the sysdevices table is a bit-mapped field that is used by SQL Server to indicate various attributes of a device. These attributes include default disk, mirror enabled, serial writes, and so on. Therefore, when you execute sp_diskdefault to turn default on for a device, SQL Server updates the bitmap of the status column in sysdevices and sets it to 1.
> > After a new installation of SQL Server, MASTER is the only device that's available on the server. The SETUP program designates MASTER as the default device. You should remove MASTER from the list of default devices by executing this command:
> > Exec sp_diskdefault "master", defaultoff

####
Mirroring Database Devices

Disk mirroring provides fault tolerance in the event of a disk failure. This enables the secondary (mirrored) device to become active, in case of a primary device failure, until the faulty device is fixed and reinstalled. Fault tolerance to a system can be provided at various levels by using

- Hardware-based RAID (Redundant Array of Inexpensive Disks)
- Windows NT–based RAID
- SQL Server–based disk mirroring

Hardware-based RAID provides the most reliable and configurable fault tolerance. In the event of a disk crash, the system continues to run because the mirror device takes over. RAID-based disk controllers recognize the new drive after the faulty drive is replaced, bringing the system back to the level of tolerance where it was before the disk failure. Hardware-based fault tolerance does not cause any performance degradation to the system because it is handled at the controller level and is completely transparent to the operating system and the applications. Hardware-based fault tolerance, however, suffers from a cost disadvantage. Check your hardware installation guide to see whether your hardware supports fault tolerance.

Windows NT–based fault tolerance can be done only on Windows NT Server. Windows NT–based fault tolerance allows logical drives to be mirrored. In the event of media failure, the system continues to run, but unlike with hardware-based fault tolerance, you must shut down the system to repair the drive that failed. Operating system–based fault tolerance is less expensive than hardware-based tolerance, but it adds more overhead to system processors and can contribute to performance degradation, especially for applications that are CPU intensive.

Fault tolerance is supported by SQL Server also for database devices. SQL Server T-SQL commands enable you to mirror, unmirror, and remirror a database device. In case of a device failure, the mirror device takes over and the system continues to run. The syntax for mirroring a database device is as shown here:

    DISK MIRROR NAME = "logical device name",

     MIRROR = "physical name of the mirror file",

     [, WRITES SERIAL | NOSERIAL ]

Only an sa can run this command, and permissions cannot be granted to other users to run this command. Mirroring is enabled after successful execution of the DISK MIRROR command. All the parameters of the DISK MIRROR command are described here:

- NAME (mandatory parameter). This is the logical name of the device you want to mirror. This device should have been previously created using the DISK INIT command.
- MIRROR (mandatory parameter). This should be the complete and valid pathname of the mirror device file. This file cannot be an existing file on the operating system.
- WRITES (optional parameter). The SERIAL parameter specifies whether writes to the mirrored device take place after writes to the database device are finished. The NOSERIAL parameter specifies whether writes can take place simultaneously on both the devices. The default for disk mirroring is SERIAL.

The following example creates a mirror device called test_dev.mir for a database device called test_dev:

    DISK MIRROR NAME = "test_dev",

     MIRROR = "e:\dbmirror\test_dev.mir"

    Creating the physical file for the mirror...

    Starting Dynamic Mirroring of 32768 pages for logical device 'test_dev'.

     512 pages mirrored...

     1024 pages mirrored...

     1536 pages mirrored...

    .....

    .....

    .....

     31744 pages mirrored...

     32256 pages mirrored...

     32768 pages mirrored...

> > When you issue the DISK MIRROR command, SQL Server updates the mirrorname column of sysdevices for the logical device specified in the command. It also updates the status column to indicate that mirroring is enabled. For the example given previously, this is the entry in the sysdevices table:
> > SELECT * FROM master..sysdevices
> > WHERE name = "test_dev"
> > low high status cntrltype name phyname mirrorname stripeset
> > ----------- ----------- ------ --------- ------------------------------ ------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------- ------------------------------
> > 33554432 33587199 738 0 test_dev c:\dbdata\test_dev.dat e:\dbmirror\test_dev.mir (null)
> > (1 row(s) affected)

After a device is mirrored, it is possible to unmirror it without shutting down SQL Server. SQL Server provides disk unmirroring options such as unmirror the primary device (permanently or temporarily) and unmirror the mirror device (permanently or temporarily). The T-SQL command DISK UNMIRROR causes mirroring to be disabled for a database device. This is the complete syntax for this command:

    DISK UNMIRROR NAME = "logical device name"

     [, SIDE = PRIMARY | SECONDARY ]

     [, MODE = RETAIN | REMOVE ]

Only an sa can run this command, and permissions cannot granted to other users to run this command. Mirroring is disabled after successful execution of the DISK UNMIRROR command. All the parameters of this command are described here:

- NAME (mandatory parameter). This is the logical name of the device you want to unmirror. This device should have been previously created using the DISK INIT command.
- SIDE (optional parameter). This option specifies whether the database device (primary side) or its mirror (secondary side) should be disabled. The default is the mirror device.
- MODE (optional parameter). This option specifies whether unmirroring is permanent (remove) or temporary (retain). The default is retain.

The following example temporarily disables the secondary side of the test_dev device:

    DISK UNMIRROR NAME = "test_dev",

     SIDE = "SECONDARY",

     MODE = "RETAIN"

If a device is unmirrored permanently, you must reissue the DISK MIRROR command to enable fault tolerance. If, however, the mirroring is disabled temporarily, you can remirror the device by using the DISK REMIRROR command. The complete syntax of this command is shown here:

    DISK REMIRROR NAME = "logcal device name"

The NAME parameter is the logical device name for which mirroring was previously turned off using the DISK UNMIRROR command. The following example shows you how to reactivate fault tolerance for the test_dev device:

    DISK REMIRROR NAME = "test_dev"

    Starting Dynamic Mirroring of 32768 pages for logical device 'test_dev'.

    512 pages mirrored...

    1024 pages mirrored...

    1536 pages mirrored...

    .....

    .....

    ......

     31232 pages mirrored...

     31744 pages mirrored...

     32256 pages mirrored...

     32768 pages mirrored...

> > You can mirror the MASTER device by using the DISK MIRROR command. It is also possible to specify a mirror device for the MASTER device at the time of SQL Server startup by supplying the -r mirror filename option. In the event of loss of the MASTER device, SQL Server can be started with this filename and the -d option.
> > From a flexibility, recoverability, and performance point of view, fault tolerance is best provided at the hardware level or by the operating system. Using SQL Server for fault tolerance is not advisable. In fact, future releases of SQL Server might not support disk mirroring.

####
Resizing Database Devices

After a device has been created using the DISK INIT T-SQL command, it is possible to increase the size of a device by using the DISK RESIZE command. After a device has been created, it is not possible to reduce the size of the device. Only an sa can run this command. The syntax of this command is as shown here:

    DISK RESIZE NAME = "logical device name",

     SIZE = new size in 2KB Pages

The following example increases the size of the device test_dev to 80M:

    DISK RESIZE NAME = "test_dev",

     SIZE = 40960

If you specify a size that is smaller than the existing device size, SQL Server returns an error and the command fails.

####
Creating Backup Devices

Databases are backed up on dump devices. These devices are used to restore databases in the event of database corruption or device failure. SQL Server database dump devices are different from the database devices. You cannot create a database on a dump device, and it is not possible to dump a database on a database device. But as with database devices, you create dump devices by providing a logical name and a physical location of the backup file. SQL Server dump devices are created using the sp_addumpdevice system stored procedure. A dump device is not tied to a database. SQL Server enables you to perform backups on disk files, tapes, and diskettes. Unlike database devices, a dump device file can be a file on a network. This is the syntax of sp_addumpdevice:

    sp_addumpdevice {"disk" | "tape" | "diskette" }, "logical_name", "physical_name",

     [, {{cntrltype [, noskip | skip [, media_capacity]]} | {@devstatus = {noskip | skip}}}]

All the parameters of this procedure are described here:

- "disk" | "tape" | "diskette" (mandatory parameter). This parameter specifies the backup media for the database.
- logical_name (mandatory parameter). This is the logical name of the dump device. A logical name is a method of mapping a physical filename to a more meaningful name. After a dump device is created, it is always referred to and accessed by its logical name.
- physical_name (mandatory parameter). This is the actual physical location of the file. SQL Server does not verify the validity of the path.
- cntrltype (optional parameter). This is used for backward compatibility with previous versions of SQL Server.
- noskip | skip (optional parameter). This parameter is specified for the tape dump devices. This parameter is used to read (noskip) or ignore (skip) ANSI labels on a tape.
- media_capacity (optional parameter). This is used for backward compatibility with previous versions of SQL Server.
- @devstatus (optional parameter). This parameter is also used to specify noskip or skip.

The following example creates two disk dump devices, one on a local hard drive (testdb_l_dump) and one on the network (testdb_n_dump):

    Exec sp_addumpdevice "disk", "testdb_l_dump", "d:\dump00\dbdumps\testdb.dmp"

    Exec sp_addumpdevice "disk", "testdb_n_dump", "\\backupserver\e$\dump00\dbdumps\testdb.dmp"

    'Disk' device added.

    'Disk' device added.

When you execute the sp_addumpdevice procedure, SQL Server does not create the physical dump device file and also does not validate the path. Dump file path validity and appropriate permissions are checked at the time of backup.

####
Getting Information on Devices

The system stored procedure sp_helpdevice provides information about a specific device when a device name is passed as a parameter. If no parameters are passed to sp_helpdevice, it displays information about all the devices on SQL Server. The following example displays information about the device test_dev:

    Exec sp_helpdevice "test_dev:"

    device_name physical_name description    status cntrltype device_number low high

    ------------------------------ ----------------------------------------------
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    ------ --------- ------------- ----------- -----------

    test_dev c:\dbdata\test_dev.dat special, MIRROR ENABLED, mirrored on 'e:\dbmirror\test_dev.mir', serial writes, reads mirrored, physical disk, 64 MB   738 0 2 33554432 33587199

####
Dropping Devices

A database or a dump device can be dropped by using the sp_dropdevice system stored procedure. The two parameters to this procedure are the logical device name (mandatory parameter) and DELFILE (optional parameter). A database device cannot be dropped if a database is created on it. Without the optional DELFILE, the sp_dropdevice stored procedure simply removes a row from the sysdevices table of the master database and frees up the virtual device number. It does not delete the physical file. As a result, you cannot create another database device with the same physical filename (however, you can create one with same logical name but with a different physical name). To delete the physical file associated with the logical name, either supply the DELFILE parameter when executing sp_dropdevice or delete the file from the operating system command line after executing sp_dropdevice. The following example shows how to drop a SQL Server device:

    Exec sp_dropdevice "test_dev", DELFILE

    File: 'c:\dbdata\test_dev.dat' closed.

    Device dropped.

    (0 row(s) affected)

    Physical file deleted.

###
Creating Databases

SQL Server can hold up to 32,767 databases, including system databases. A database is a collection of objects such as tables, triggers, stored procedures, views, and rules that take up space in a database. Users of a database perform various kinds of operations to select and modify data using various T-SQL commands such as SELECT, DELETE, UPDATE, and INSERT. SQL Server provides a mechanism by which all data modifications are first written to a part of the database called Transaction Log and then written to the actual tables. This technique of writing first to the transaction log is also called a write-ahead scheme and is used for recovering transactions in the event of a SQL Server crash or shutdown. When a SQL Server is started after a crash or a shutdown, the transaction log of the database is used to roll forward or roll back changes that took place before the crash or shutdown. All the committed changes that exist in the transaction log but not in the data tables are written (rolled forward) to the relevant tables. All uncommitted transactions at the time of the crash/shutdown are undone (rolled back). This method of recovery is performed on all the databases of SQL Server after the restart. Therefore, it is obvious that the transaction log plays an important role for up-to-the-minute recovery of transactions. At the time of database creation, you can specify the names of devices on which database objects and data will reside and the names of devices on which a transaction log will be created. A database is created on the database devices by using the CREATE DATABASE T-SQL command. The relationship between a database and a device is many-to-many; that is, a single database can span multiple devices, and a single device can hold multiple databases. The complete syntax of CREATE DATABASE is shown here:

    CREATE DATABASE database_name

    [ON {DEFAULT | device_name} [= size]

     [, device_name [= size]]....]

    [LOG ON device_name [= size]

     [, device_name [= size]]....]

    [FOR LOAD]

Typically, databases on a SQL Server are created by an sa. It is possible, however, to grant CREATE DATABASE permissions to other users. All the parameters of this command are explained here:

- database_name (mandatory parameter). This is the name by which a database will be recognized within a SQL Server. The name of the database can have a maximum of 30 characters.
- ON (optional parameter). This parameter is supplied if you do not want to use a default database or default size.
- device_name (optional parameter). This parameter is required to specify the database device on which you want to create your database. You can specify multiple device names if you want to spread the database across multiple devices.
- size (optional parameter). This parameter is used to specify the size of the database or the transaction log as a whole or on a particular database device.
- LOG ON (optional parameter). This parameter is used to specify a device for the transaction log of a database. It is very important to create a transaction log on a device that is different from the database device from a recovery point of view. SQL Server provides the facility to back up the entire database using the DUMP DATABASE command. It also allows incremental changes on a database (changes that are stored in the transaction log) to be backed up by use of the DUMP TRANSACTION command. If the LOG ON option is not used during the CREATE DATABASE command, the space allocated for a database will be shared by the data and the transaction log, and it will not be possible to back up the transaction log (and hence the incremental changes). In such cases if you lose your database in the event of a disk crash, you can recover the database only up to the last database backup. If, however, a transaction log is created on its own device, it is possible to back up incremental changes. In case of a disk crash, you can load the database from the previous database backup and apply the incremental transaction log backups to bring the database up to the point of the last transaction dump.
- FOR LOAD (optional parameter). This parameter is used to create database space that will act like a placeholder. A database created by using this option can be used only to load a database from a database backup with the LOAD DATABASE command. After a database is loaded, it can be accessed like an ordinary database. When a database is created without the FOR LOAD option, SQL Server initializes all the allocation pages on the database devices with zeroes. If you specify the FOR LOAD option, SQL Server simply allocates the space for the database and does not initialize allocation pages with zeroes. Therefore, a database is created faster with this option as compared to normal database creation.

When you create a database, SQL Server copies all the system tables from the model database into the new template. These catalog tables are essential for the internal working of SQL Server. For this reason, a newly created database cannot be smaller than the model database. One of the configuration parameters of SQL Server is database size, which represents the minimum size of the database. The default value for this parameter is 2M. If you do not specify any size information during CREATE DATABASE, SQL Server creates the database of the size of the model database or the database size SQL Server configuration parameter—whichever is larger. You can set the database size configuration parameter value by executing the sp_configure system stored procedure or by using SQL Enterprise Manager. In its simplest form, a database can be created as shown here:

    CREATE DATABASE TESTDB

This example creates a database of 2M on the first default database device. If no default devices are available or if the space is less than 2M, SQL returns an error. The following example creates the database TESTDB (500M data, 100M log) on two devices, DB01 and DB02 (250M each), and places the transaction log on LOG01 (100M):

    CREATE DATABASE TESTDB ON

     DB01=250 , DB02 =250

     LOG ON LOG01 = 100

> > Following are some of the things you should know about the CREATE DATABASE command:
> > If not enough space is available on a particular device, SQL Server does not give an error, but instead allocates whatever space is available on the device.
> > If you don't provide the LOG ON option during the CREATE DATABASE command, it is still possible to create a log device for the database later by executing the sp_logdevice stored procedure.
> > When you create a database, the system tables affected in the master database are sysdatabases and sysusages. The table sysdatabases stores the information about the database name, creation date, owner, database options (readonly, single user, and so on). The table sysusages maps database allocation to various devices.

####
Changing the Ownership of a Database

Each database on a SQL Server is assigned an owner. This login is recognized by a special name within that particular database: DBO (database owner). This login, being the owner of the database, has all the privileges within that database. The login ID that creates the database (usually the sa) automatically becomes the owner of the database. In a multiserver, multidatabase environment, sometimes it is necessary to assign database ownership to individual logins to distribute database responsibilities to individuals and reduce workload on the system administrator. This way, the system administrator can focus more on server-related issues (such as device creation and configuration). A database ownership is changed by executing the sp_changedbowner system stored procedure. This procedure must be executed by the sa from within the context of a database. A database context is similar to a working directory on an operating system. A user on a SQL Server can be a valid user of multiple databases and at any given time must be working in a particular database. This is accomplished via the USE T-SQL command. This command enables a user to switch from the current working database to another (provided that the user is a valid user in the other database). Therefore, to change the ownership of a database, the sa must take these steps:

1. Switch to the database by executing the USE T-SQL command.
2. Execute the sp_changedbowner login_name command. The login name must exist on SQL Server.

The following example changes the ownership of the database TESTDB to the SQL Server login TESTDB_ADM:

    USE TESTDB

    go

    Exec sp_changedbowner TESTDB_ADM

    go

There can be only one owner (DBO) of a database. It is possible, however, to impersonate a database owner's privileges within a database by executing the sp_addalias system stored procedure. This way, individual logins can maintain their identity and have the DBO privileges at the same time. Aliases are created in a database by which one or more database users can be mapped to a particular user, thereby inheriting all the privileges held by that user. This technique enables a database user to impersonate another database user. Aliases are generally created to impersonate a DBO so that multiple user IDs can perform the function of a DBO. The syntax of this stored procedure is

    sp_addalias login_name, name_in_database

in which login_name is the login ID you want to map, and name_in_database is the user name to which you want to map. It is important to note that the login_name cannot be an existing user of the database. The following example maps the SQL Server login Johnm to DBO:

    USE TESTDB

    go

    Exec sp_addalias Johnm, "dbo"

    Alias user added.

####
Changing Database Options

SQL Server enables you to set certain database options for an individual database. These options can be turned on or off by the DBO or the system administrator. All these options are set by execution of the sp_dboption system stored procedure. The complete syntax of this procedure is as follows:

    sp_dboption [ database_name, option_name, { TRUE | FALSE }]

If you execute this stored procedure without providing any parameters, it displays all the options that can be set using this procedure. All the parameters of this command are explained here:

- DATABASE_NAME (optional parameter). This parameter is used to specify the name of the database for which you want to set database options. If you execute this procedure without providing the OPTION_NAME parameter value, this procedure displays the options that are currently set for the database.
- OPTION_NAME. This parameter is used to specify a particular option you want to set for a database. These are some of the commonly used options:
  ANSI null default: At the time of table creation, if you don't specify NULL or NOT NULL as a column property, SQL Server defaults to NOT NULL. The ANSI standard is that if you don't specify the NULL property of a column during CREATE TABLE, the default should be NULL. SQL Server provides compatibility with ANSI standards by enabling you to set this flag to TRUE.
  DBO use only: This option is used to prevent access to the database by ordinary users of the database. If this option is set to TRUE, only an sa or a DBO can use the database. This is a useful option if you want to perform some maintenance on the database and don't want interference from the user transactions.
  no chkpt. on recovery: If this option is set to TRUE, no CHECKPOINT record is added to the database after SQL Server startup. CHECKPOINT is an internal SQL Server process that is activated automatically by SQL Server to write completed transactions from memory to the disk. This method provides faster recovery when a SQL Server is restarted after a crash or shutdown. After the recovery (that is, rolling forward/rolling back transactions), SQL Server by default writes a checkpoint record to record the time stamp. The default for this option is TRUE. It is required to be turned off if you are loading transaction log backups from another server.
  offline: This option is used to make a database unavailable.
  published: This option must be set to TRUE if data is published from the database for performing data replication.
  read only: If this option is set to TRUE, no data modifications (such as DELETE, INSERT, and UPDATE) are allowed inside a database.
  select into/bulkcopy: This option is required to be set to TRUE if you want to import data (using the bcp utility) into tables that have indexes and triggers on them.
  single user: If this option is set to TRUE, only one user can access the database at any given time. Even an sa or a DBO cannot access the database if the database is being used by another user.
  subscribed: This option must be set to TRUE if a database is subscribing data from a publishing database.
  trunc. log on chkpt: If this option is set to TRUE, the CHECKPOINT process forces all the committed transactions to the database device and then removes all these transactions from the transaction log without making a backup copy of the transaction log. This is a useful option in a test environment, where full recovery is not an issue. This option should be turned off for production databases to ensure full recoverability.

The following example sets the trunc. log on chkpt and select into/bulkcopy options on for database TESTDB:

    Exec sp_dboption "tempdb", "trunc. log on chkpt", TRUE

    Exec sp_dboption "tempdb", "select into", TRUE

    CHECKPOINTing database that was changed.

    CHECKPOINTing database that was changed.

The following example displays the options that have been set on the database TESTDB:

    Exec sp_dboption "TESTDB"

    The following options are set:

    -----------------------------------

    select into/bulkcopy

    trunc. log on chkpt.

> > If you want to set certain options on all the database, set those options on the model database. When a new database is created, it will inherit all the options from the model database. The following example sets the trunc. log on chkpt option to TRUE for the model database:
> > Exec sp_dboption "model", "trunc. log on chkpt", TRUE
> > All the databases created this command has been executed will have trunc. log on chkpt option set to TRUE.

####
Renaming a Database

SQL Server provides the sp_renamedb system stored procedure to rename an existing database. A database must be brought into single-user mode before it can be renamed. The following example renames a database called TESTDB to DEVDB:

    Exec sp_dboption 'TESTDB', 'single user', true

    go

    Exec sp_renamedb 'TESTDB', 'DEVDB

    go

    Exec sp_dboption 'DEVDB', 'single user', false

    go

    CHECKPOINTing database that was changed.

    DBCC execution completed. If DBCC printed error messages, see your System Administrator.

    Database is renamed and in single-user mode.

    System Administrator (sa) must reset it to multi-user mode with sp_dboption

    CHECKPOINTing database that was changed.

    DBCC execution completed. If DBCC printed error messages, see your System Administrator.

####
Changing the Size of a Database

Each database on SQL Server is created with a fixed amount of space. As objects are created in a database and data is added, the amount of free space decreases. For a database to stay active, it is important that there always be enough room to grow. A database size can be increased with the ALTER DATABASE command. This command is similar to the CREATE DATABASE command. This command can be run by the sa or by the database owner (DBO), provided that the DBO has CREATE DATABASE permissions. Following is the complete syntax of this command:

    ALTER DATABASE database_name

    [ON} {DEFAULT | database_device } [=size],

    [, database_device } [=size...]

    FOR LOAD

The minimum size by which a database can be expanded is 1M. The following example increases the size of the database TESTDB by 100M on device DB03.

    ALTER DATABASE TESTDB ON DB03 =100

SQL Server enables you to reclaim database space if it has been over-allocated. The SQL Server T-SQL command DBCC SHRINKDB enables you to reclaim only unused database space. The syntax of this command is as follows:

    DBCC SHRINKDB (database_name [, new_size [, 'MASTEROVERRIDE']])

Before this command can be executed, the database must be brought into single-user mode with sp_dboption. The new_size parameter is specified in terms of 2K pages. If you are not sure how much to specify as the new size, run this command without specifying the new_size value, and SQL Server returns the minimum size the database can be reduced to. The following example reduces the size of TESTDB to 160M.

    exec sp_dboption 'TESTDB', 'single user', TRUE

    go

    USE TESTDB

    go

    DBCC SHRINKDB("TESTDB,81920)

    go

    CHECKPOINTing database that was changed.

    DBCC execution completed. If DBCC printed error messages, see your System Administrator.

####
Getting Information About Databases

The system stored procedure sp_helpdb provides information about a specific database when a database name is passed as a parameter. If no parameters were passed to sp_helpdb, it displays information about all the databases on SQL Server. The following example displays information about the TESTDB database:

    Exec sp_helpdb "TESTDB"

    name db_size owner dbid created

    ------------------------ ------------- ------------------------ ------ -----------
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    TESTDB 400.00 MB TESTDB_ADM 8 Mar 8 1996 select into/bulkcopy, trunc. log on chkpt., ANSI null default

    device_fragments size usage

    ------------------------------ ------------- --------------------

    DB01 250.00 MB data only

    DB01 250.00 MB data only

    DB03 100.00 MB data only

    LOG01 100.00 MB log only

To find out the space used within a database, execute the sp_spaceused stored procedure. This procedure reports space usage at the table level or for the entire database. This procedure reports space usage for the current database. The following example shows the space usage of the database TESTDB:

    USE TESTDB

    go

    Exec sp_spaceused

    go

    database_name database_size unallocated space

    ------------------------------ ------------------ ------------------

    TESTDB 700.00 MB 567.63 MB

    reserved data index_size unused

    ------------------ ------------------ ------------------ ------------------

    135552 KB 56810 KB 63670 KB 15072 KB

####
Dropping Databases

If a database is not required any longer, it can be dropped with the DROP DATABASE T-SQL command. When a database is dropped, all the objects and data contained in it are removed, and allocation on various devices is released. The syntax for this command is as follows:

    DROP DATABASE database_name [, database name ]

The following example drops the database TESTDB:

    DROP DATABASE TESTDB

This command can be run only by the sa or by the database owner. It is not possible to drop a database if it is in use.

###
Using Segments

When a database is initially created on SQL Server with the CREATE DATABASE command, all the space allocated to the database is shared by all the objects of the database, and there is no way to allocate space to specific database objects. A segment is a mechanism by which one can manage the growth of database objects on particular device segments. This helps in better placement of data and improved performance. When you first create a database, SQL Server creates three segments in the database:

- System Segment is used to store system catalog tables. By default, this segment spans across all the devices on which a database is created.
- Default Segment is used to store tables and indexes. By default, this segment spans across all the devices on which a database is created.
- Log Segment is used to store the transaction log. By default, this segment spans across all the devices specified in the LOG ON clause of CREATE DATABASE or in the sp_logdevice stored procedure. If the transaction log is not on a separate device, it shares the database space with the other two segments.

####
Adding Segments

SQL Server enables you to create user-defined segments in a database. These segments are a subset of the space allocated to a database. At the time of table creation and index creation, you can specify the segments on which these objects should be created. After an object is defined on a particular segment, it cannot grow beyond the segment boundary. If a segment gets full, however, it is possible to extend the size of a segment. You create segments in a database by executing the sp_addsegment system stored procedure from the database in which you want to define the segment. The syntax of this stored procedure is

    sp_addsegment segment_name, logical_device_name

in which segment_name is the name of the segment you want to create, and logical_device_name is the name of the device on which the database is created.

The following example creates the table_seg segment on DB01 and the index_seg segment on DB02 for the database TESTDB:

    USE TESTDB

    go

    Exec sp_addsegment table_seg, DB01

    Exec sp_addsegment index_seg, DB02

Any object that gets created on segment table_seg can use all the DB01 space that is assigned for the database. It is important to note that table_seg cannot use all the space on device DB01, but only the space allocated to the database TESTDB. The following example creates the table test_table on table_seg and a nonclustered index on index_seg:

    create table test_table ( col1 int, col2 int ) on table_seg

    create unique nonclustered index nc1 on test_table ( col1 ) on index_seg.

By creating test_table on table_seg and a nonclustered index on index_seg, you can control the placement of the data and index segment on different devices, and the growth of one does not interfere with that of the other. Also, the devices DB01 and DB02 are on two different controllers, so there is a big performance improvement because the I/O is now split across controllers. A logical diagram of the use of segments in a database is shown in Figure 30.1.

FIGURE 30.1. Using database segments.

> > If you create a table on one segment and its clustered index on another segment, the entire data moves to the segment on which the clustered index is defined. The reason is that in case of a clustered index on a table, data is physically sorted on the key, and the bottom of the index page is the table data; therefore, you cannot isolate the data from the index.

####
Extending Segments

As objects are created in database segments and data is added, the amount of free space decreases. For a database to stay active, it is important that there be enough space for objects to grow. It is possible to extend a segment on devices on which the database is created. A segment can be extended with the sp_extendsegment system stored procedure. The syntax for this stored procedure is

    sp_extendsegment segment_name, logical_device_name

in which segment_name is the name of the segment you want to extend, and logical_device_name is the name of the device on which the database has been created.

The following example extends the table_seg segment on DB03 for the database TESTDB:

    USE TESTDB

    go

    Exec sp_extendsegment table_seg, DB01

If the database size is increased on a particular device, the segment defined on that device automatically gets expanded to make use of the additional space.

####
Getting Information About Segments

The system stored procedure sp_helpsegment provides information about the objects in a segment when a segment name is passed as a parameter. If no parameter is passed to sp_helpsegment, it simply lists all the segments on a database. The following example displays information about the table_seg segment of the TESTDB database:

    USE TESTDB

    go

    Exec sp_helpsegment "table_seg"

    segment name status

    ------- ------------------------------ ------

    3 table_seg 0

    device size

    ------------------------------ ----------------------

    DB01 250MB

    DB03 100MB

    table_name index_name indid

    ------------------------------ ------------------------------ ------

    test_table idx1 0

####
Dropping Segments

A segment can be dropped if no objects are defined on it. SQL Server returns an error if you try to drop a segment that is being used. If there are objects on a database, you need to reassign them to a different segment before you can drop a segment. You can drop a segment by executing the sp_dropsegment system stored procedure. A segment can be dropped from a specific device, or it can be dropped completely. The syntax of sp_dropsegment stored procedure is as follows:

    sp_dropsegment database_name, [device_name]

The following example drops the table_seg segment from the DB03 database device for the database TESTDB:

    USE TESTDB

    go

    Exec sp_dropsegment "table_seg", "DB03"

    go

    DBCC execution completed. If DBCC printed error messages, see your System Administrator.

    Segment reference to device dropped.

The system stored procedure sp_helpdb also provides information about the segments on a database.

> > Segments are particularly useful when you want to split a large table across multiple devices. This technique can help improve performance of the system because I/Os on the large table can now be split across multiple drives. Although the use of segments can provide better overall system performance, segments add more complexity and reduce overall manageability of the database. Hardware-based RAID can also distribute load across various disks and might provide better performance than the use of segments alone.

###
Managing Security

In a business environment, it is important to protect business-critical information stored in a database. SQL Server can prevent unauthorized access to data at the following levels:

- Server Level: At this level SQL Server authenticates whether a connection request should be granted or denied based on the user ID and password information.
- Database Level: At this level SQL Server verifies whether a particular login is a valid user of the database.
- Object Level: At this level SQL Server verifies permissions on the objects a user is trying to access.

####
Managing Security at the Server Level

This is the first step of user authentication in which SQL Server determines whether a login is a valid SQL Server login. SQL Server security can be configured to handle user connections in one of the following ways:

- Standard security
- Integrated security
- Mixed security

#####
Standard Security

In this security mode, SQL Server validates the user logins directly. To access a SQL Server, a user must supply a user ID and a password. This security mode does not require any user account on Windows NT. This is the default security mode for SQL Server. User logins are created on SQL Server with the sp_addlogin system stored procedure. Following is the syntax for this procedure:

    sp_addlogin login_id, [password, [,defdb [ ,deflanguage ]]]

All the parameters of sp_addlogin are explained here:

- login_id (mandatory parameter). This is the name of the user you want to create an account.
- password (optional parameter). This is the password for the login_id that will be used to authenticate for connection to SQL Server. You are not required to provide a password to this procedure, although omitting it is not recommended.
- defdb (optional parameter). A valid user of SQL Server can use multiple databases. This parameter enables you to define a default database for a user. If this parameter is not specified, a user is connected to the master database.
- deflanguage (optional parameter). This is the default language for a user connection. If this is not specified, the language used by SQL Server is chosen. The following example creates a login called testuser with a default database of TESTDB:
  EXEC sp_addlogin "testuser", "123user", TESTDB
  New login created.

> > Creating a login with a default database does not make a login a valid user of the database. After a login has been created on SQL Server, it is necessary to add this login to all the databases this login should be allowed to access. You accomplish this task by executing the sp_adduser system stored procedure from within a database. The stored procedure sp_adduser is discussed later in this chapter.

#####
Integrated Security

In this security mode a user should have a Windows NT account, and login authentication is done by the operating system. After a login is verified by Windows NT, SQL Server does not validate the login, and a user can access SQL Server. Integrated security has advantages over standard security because it provides better security (C2 compliance, password expiration and aging, and so on) and better manageability at the enterprise level (accounts can be created at machine level or at domain level). The disadvantages are that integrated security works only with the Named Pipes protocol and the Multi-protocol, and each user who uses integrated security to log on to SQL Server is mapped to a default login of SQL Server (or as an sa, if the user has administrative privileges). Because all users are mapped to one login of SQL Server, all the users of SQL Server have same permissions within a database.

#####
Mixed Security

This mode of security combines the standard security and integrated security methods. In this mode, if a login request is first matched against an account name on Windows NT and if a match is found, integrated security rules are applied. Otherwise, SQL Server validates the login ID using standard security mode by validating the user name and password.

> > Mixed security is very useful if you forget the sa password. Because Standard security does not let you connect to SQL Server without a valid password, you can still connect to SQL Server by using your network account name, and if the account name is a part of the administrative group, SQL Server lets you connect as an sa. After you are connected, you can change the sa password by using the sp_password system stored procedure.

####
Managing Security at the Database Level

To access a database, a user needs to be assigned to the database. A user can be a user in multiple databases. Before a user can be added to a database, the user should already have a login created (the sa must have created the login by using the sp_addlogin procedure) on SQL Server.

#####
Adding Groups

SQL Server also enables you to create groups within a database. A group is a logical way of defining a collection of users. Groups in SQL Server provide a convenient method of managing object permissions. After a group is created, it is possible to grant and revoke object permissions to the group, and all the users who exist in the group automatically inherit the permissions assigned to the group. In SQL Server, a user can belong to only one group. A group in a database can be created with the sp_addgroup system stored procedure. The syntax is as follows:

    sp_addgroup "group_name"

Either the DBO or the sa can run this command. The following example creates three groups called readonly, readwrite, and developer in the database TESTDB:

    USE TESTDB

    go

    Exec sp_addgroup readonly

    Exec sp_addgroup readwrite

    Exec sp_addgroup developer

    go

    New group added.

    New group added.

    New group added.

It is not necessary for a user to be a part of any user-defined group. Each database of SQL Server has a built-in group called public. All the users of a database by default are a part of this group.

#####
Adding Users

All users in a SQL Server database, by default, belong to the group public. Users can be added to a database with the sp_adduser system stored procedure. The syntax for this command is as follows:

    sp_adduser user_name, [name_in_database, [, group_name]

All the parameters of this procedure are described here:

- user_name (mandatory parameter). This is the name that was used at the time of creation of the login; that is, it is the name used in the sp_addlogin command.
- name_in_database (optional parameter). If this parameter is not specified, the user of the database is given the same name as the user's login name on SQL Server. By using this parameter, you can specify a name in the database that is different from the login name (that is, the name required to connect to SQL Server).
- group_name (optional parameter). This is the name of the group the user is assigned to. The group name must already exist in the database before a user can be assigned to it.

This stored procedure can be executed only by the DBO or the sa. The following example creates a user called Tom who has a SQL Server login name of testuser and assigns it to the readonly group in the database TESTDB:

    USE TESTDB

    go

    Exec sp_adduser testuser, Tom, readonly

    go

#####
Getting User and Group Information

Information on the users and groups of a database can be obtained via the sp_helpuser and sp_helpgroup system stored procedures. Both of these stored procedures accept user name/group name as input parameters. If no input parameters are provided, they display information about all the users/groups of the database. The following example provides information about the user Tom in the database TESTDB:

    USE TESTDB

    go

    Exec sp_helpuser "Tom"

    go

    UserName GroupName LoginName DefDBName UserID SUserID

    -------- --------- --------- --------- ------ -------

    Tom readonly testuser TESTDB 3 11

#####
Dropping Aliases, Users, Groups, and Logins

You can drop an alias user from a database by executing the sp_dropalias stored procedure by passing the login_id to the procedure. You cannot drop an alias if objects are owned by the user in the database. Therefore, to drop an alias you must drop the objects owned by the user.

You can drop a user from a database by executing the sp_dropuser stored procedure by passing the user name to the procedure. You cannot drop a user if objects are owned by the user in the database. Therefore, to drop a user you must drop the objects owned by the user.

You can drop a group from a database by executing the sp_dropgroup stored procedure by passing the group name to the procedure. You cannot drop a group if users are assigned to it. Therefore, before you can drop a group, you can either drop users from the database by executing sp_dropuser or assign them to a different group by executing the sp_changegroup system stored procedure.

You can drop a login from a SQL Server by executing the sp_droplogin system stored procedure by passing the login name to the procedure. You cannot drop a login if it is created as a user in a database. Therefore, to drop a login you must drop the user from the databases where it exists.

The following examples demonstrate how the previously listed actions can be accomplished:

    USE TESTDB

    go

    Exec sp_dropalias John

    Exec sp_dropgroup developer

    Exec sp_dropuser Tom

    Exec sp_droplogin testuser

    go

    Alias user dropped.

    Group has been dropped.

    User has been dropped from current database.

    Login dropped.

> > SQL Server enables a user on one SQL Server to execute a stored procedure on another SQL Server. To provide this functionality, all the steps similar to the ones mentioned previously (such as create login and add user) are taken at the remote server. The various stored procedures used to accomplish this are sp_addserver, sp_addremote_login, and sp_remoteoption.

####
Managing Security at the Object Level

A database is a collection of objects (such as tables, views, and procedures). It is important to allow and restrict access on these objects to various users of the database, depending on the privileges of a user, as governed by the business. Users within a database are assigned permissions. Some logins of SQL Server have more privileges than others. A user connection to a SQL Server can be at one of the following privilege levels:

- System Administrator (sa): This login ID is at the highest permission level. Anyone who has access to the sa password has all the privileges within SQL Server. This login owns everything in a SQL Server, directly or indirectly (through permission levels). There are certain commands that only an sa can run (such as DISK INIT and DISK MIRROR).
- Database Owner (DBO): This is a special login that owns a particular database and is a superuser within that database. This login owns everything within the database, directly or indirectly (through permission levels). There certain commands that only a DBO can run within a database (such as LOAD DATABASE and DROP DATABASE). DBO is the login that grants (or revokes) permissions (such as CREATE TABLE and CREATE PROCEDURE) to the ordinary users of the database.
- Other Users: These are the regular logins of the database; they work with the privileges assigned to them by the DBO. Based on the privileges assigned to them, individual users can create objects in database, and they can select or modify data in tables owned by them or other users (including the DBO). A user who creates an object has all permissions on it. For others to access that object, they are required to have permissions for performing various operations on the object. There are two T-SQL commands to control permissions on an object: GRANT and REVOKE. Both these commands specify the privilege list, the object name, and the user or group to which a privilege is applicable. The following example creates a table called test_table and grants all permissions to the readwrite group, revokes all the permission from a user called Tom, and grants SELECT access to the readonly group:
  USE TESTDB
  go
  CREATE TABLE test_table ( col1 int, col2 int )
  go
  GRANT ALL ON test_table to readwrite
  REVOKE ALL ON test_table from Tom
  GRANT SELECT ON test_table to public
  go

> > Unless specific permissions are granted, even the DBO (and the sa) don't have any permissions on an object created by a user. The workaround for this situation is that a DBO impersonates the user by the SETUSER T-SQL command and then grants permissions to himself.

SQL Server also enables system administrators to grant permissions to run certain T-SQL administrative commands, such as CREATE DATABASE and DUMP DATABASE, to some users of SQL Server.

One of ways you can provide security on table data is by using views in a database. Views provide a snapshot of data from one or more underlying database tables. To an end-user, a view looks like a regular table. By using views, you can restrict direct access to the database tables by providing a horizontal (that is, specific rows) and vertical (that is, specific columns) snapshot of the data from underlying tables. Therefore, views help simplify data representation and provide logical data independence. Because permissions are required to be granted on the view and not on the underlying tables, the end-users need not know about the underlying table structures and data from which the view was created. Thus, views provide excellent data security.

Enhanced data security can be achieved via SQL Server stored procedures. A user who has permissions to execute a stored procedure does not require any permissions on the underlying tables used within the procedure. Therefore, by granting execute permissions on stored procedures and revoking all permissions on the underlying tables, you can prevent any direct access to the database tables. This way, all data manipulation on the tables can be done entirely with stored procedures.

###
Backup and Recovery

Database backups are required to recover from failures such as widespread database corruption or media failure. SQL Server provides various T-SQL commands to back up data in a database. One of the features of SQL Server backups is that users can access the database while backups are being performed. This method of backing up, called dynamic dumps, is quite useful in a 7x24 environment, in which it is not feasible to secure any downtime. Because backups are very I/O intensive, they do affect system performance; therefore, it is important to perform database backups during a time of low activity on the server. Backups are generally performed by the sa or DBO, but it is possible to grant access to back up a database to a user of the database. SQL Server enables you to perform data backups at the following levels:

- Back up an individual table
- Back up an entire database
- Back up incremental changes (backup transaction log) of a database

####
Backing Up and Restoring Tables

Table-level backups are useful in situations in which most of the tables in a database are static in nature. In such cases backing up a few active tables of the database can save time compared with backing up the entire database. The T-SQL command to back up a table is this:

    DUMP TABLE [ [database_name].owner.]table_name

    TO dump_device, [dump_device2 [, dump_device3.....]]

    WITH options

All the parameters of this command are explained here:

- database_name (optional parameter). You can specify the name of the database in which the table exists. If you don't specify a database name, it is assumed that the table is being backed up from the current database.
- table_name (mandatory parameter): This parameter specifies the name of the table that needs to be backed up.
- dump_device (mandatory parameter). This parameter specifies the logical name of the dump device.
- dump_device2, dump_device3... (optional parameters). Additional dump devices can be specified if you want to stripe dumps across various devices. This is a useful feature for large databases. It results in improved performance because the I/Os are split across various devices. This feature also, however, results in additional maintenance, because you need to keep track of all the devices a database was dumped on.
- options (optional parameters). This parameter specifies various dump options such as init (overwrite a previous dump file), noinit (add to existing dump file), and stats (show percentage of dump completed).

> > It is also possible to dump a table without specifying a logical dump device name. You can specify the absolute path by specifying disk="path" in the DUMP TABLE command.

The following example dumps the employee table to a disk file:

    DUMP TABLE TESTDB..employee TO disk="c:\temp\employee.dmp" with INIT, STATS=10

    Msg 3211, Level 10, State 5

    10 percent dumped

    Msg 3211, Level 10, State 5

    20 percent dumped

    Msg 3211, Level 10, State 2

    100 percent dumped

    Msg 4035, Level 10, State 1

    Database 'TESTDB' table 'employee' (24 pages) dumped to file <1> on device 'c:\temp\employee.dmp'.

A table can be restored with the LOAD TABLE command. The syntax is similar to that of the DUMP TABLE command. The following example restores new_employee, which has the same structure as the employee table, from a previously taken dump:

    LOAD TABLE TESTDB..new_employee FROM disk="c:\temp\employee.dmp" with STATS=10

    Msg 4039, Level 10, State 1

    Warning, file <1> on device 'c:\temp\employee.dmp' was dumped from database 'TESTDB'.

    Msg 3211, Level 10, State 6

    10 percent loaded

    Msg 3211, Level 10, State 6

    20 percent loaded

    Msg 3211, Level 10, State 6

    30 percent loaded

    Msg 3211, Level 10, State 6

    40 percent loaded

    Msg 3211, Level 10, State 6

    50 percent loaded

    Msg 3211, Level 10, State 6

    60 percent loaded

    Msg 3211, Level 10, State 6

    70 percent loaded

    Msg 3211, Level 10, State 6

    80 percent loaded

    Msg 3211, Level 10, State 6

    90 percent loaded

    Msg 3211, Level 10, State 6

    100 percent loaded

####
Backing Up and Restoring Databases

Database dumps are performed to back up the entire contents of a database. Database backups are done with the DUMP DATABASE T-SQL command. This command copies all the object definitions, including SQL Server internal objects, and the data in the dump device files. Database dump files are used to recover databases in case one of the device (on which the database is created) crashes. A database dump file can also be used to restore a database on another server, provided that the character set and sort order are compatible. For example, you might set up a database on a test server and want to keep it in sync with a database on a production server. Each night after the database backups are performed on the production server, you can take that dump file and load it on the test server. The syntax of DUMP DATABASE is similar to that of the DUMP TABLE command. The following example dumps the TESTDB database to a logical device called testdb_dump:

    DUMP DATABASE TESTDB TO testdb_dump WITH INIT, STATS=50

    Msg 3211, Level 10, State 2

    50 percent dumped

    Msg 3211, Level 10, State 2

    100 percent dumped

    Msg 4035, Level 10, State 1

    Database 'TESTDB' (324 pages) dumped to file <1> on device 'testdb_dump'

A database can be restored with the LOAD DATABASE command. To load a database, you need to take into account the following rules:

- The target database should not be in use by anyone.
- You must be a DBO (or an sa).
- The target database should be created before it can be loaded.
- The target database should be at least as big as the database for which the dump was initially taken.
- The character set and sort order of the server where the database is loaded should be same as the server from which the database dump was initially taken.

The following example loads the database TESTDB from the logical dump device testdb_dump:

    LOAD DATABASE TESTDB FROM testdb_dump WITH STATS=30

    30 percent loaded

    Msg 3211, Level 10, State 2

    60 percent loaded

    Msg 3211, Level 10, State 2

    90 percent loaded

    Msg 3211, Level 10, State 2

    100 percent loaded

####
Incremental Backup and Recovery

SQL Server enables you to back up incremental changes in a database by providing the DUMP TRANSACTION T-SQL command. By doing incremental backups, you can ensure full recoverability of the database. The syntax of DUMP TRANSACTION is similar to that of DUMP TABLE and DUMP DATABASE. This is the complete syntax of this command:

    DUMP TRANSACTION {database_name | local_variable_name}

    TO dump_device [, dump_device2 [, dump_device3...]]

    [WITH { TRUNCATE_ONLY | NO_LOG | NO_TRUNCATE }

    options

When you dump the transaction log, it backs up all the committed changes that took place since the last transaction log dump. After the successful dump, SQL Server clears those transactions from the transaction log. In some cases (such as test or development environments), if you don't want to make a backup copy of the transaction log of a database, and you only want to clear out the transaction log, you should use the dump option of TRUNCATE_ONLY. When you are using this option, it is not necessary to specify a dump device. The transaction log of database is created with finite space, and it can fill up completely over a period as a result of data modifications taking place in the database. If the transaction log becomes completely full, you cannot back up the transaction log on a device or use the TRUNCATE_ONLY option. You must use the NO_LOG option to clear out all the committed transactions from the transaction log. This option removes all committed transactions without creating a backup copy. Therefore, it is quite important to back up your databases using the DUMP DATABASE command if you have used the TRUNCATE_ONLY or NO_LOG option to create space in the transaction log.

In the event of a database device failure, you can still back up the transaction log to a dump device by using the DUMP TRANSACTION command with the NO_TRUNCATE option. The following example illustrates a typical sequence of database backups. The database dumps are taken each day at 6:00 a.m., and incremental transaction log backups are taken every hour (that is, 7:00 a.m., 8:00 a.m., and so on).

    6:00 AM:

     DUMP DATABASE TESTDB TO testdb_dump with init.

    7:00 AM

     DUMP TRANSACTION TESTDB TO disk="e:\tranlog\testdb_tran_1" with init

    8:00AM

     DUMP TRANSACTION TESTDB TO disk="e:\tranlog\testdb_tran_2" with init

Assume that the database in the preceding example is created on the logical device DB01 and has a transaction log created on LOG01, and assume that a disk crash occurs at 8:30 a.m. These are the three possible failure scenarios:

- Scenario 1: Device DB01 crashes, but LOG01 is still intact.
- Scenario 2: Device LOG01 crashes, but DB01 is still accessible.
- Scenario 3: Both DB01 and LOG01 crash.

In scenario 1, because LOG01 is accessible, you can still back up all the changes that took place between 8:00 a.m. and 8:30 a.m. by dumping the transaction log with the NO_TRUNCATE option. To bring the system back up to the point of failure, you can now load the database dump (6:00 a.m.) and the transaction log dumps (7:00 a.m., 8:00 a.m., and 8:30 a.m.).

In scenario 2, failure on the transaction log device renders the database useless (even though DB01 is still active). Therefore, in this case you can recover the system only up to 8:00 a.m., and all the changes made after that are lost. For this reason, it is strongly recommended that you mirror the transaction log device of the database.

In scenario 3, because both of the devices have failed, you can bring the system back up to 8 a.m., and all the changes made after that are lost.

####
Recovering the Master Database

It is quite important to keep an up-to-date backup copy of the master database. Recovery of the master database is different from that of other databases. For the master database, data and transaction logs are on the same device. Therefore, it is not possible to back up incremental changes. To recover the master database, you must go back to the latest copy of the master database dump. Any changes made to the master database after the dump cannot be recovered. To recover a master database, you must take the following steps:

1. Shut down SQL Server (if it has not already crashed), and run SQL SETUP from the SQL Server 6.5 program group. Continue to the Microsoft SQL Server 6.5 options screen, and select Rebuild Master. The next few screens prompt you to select the character set, sort order, and location of the SQL Server installation directory. It is important that you choose the original character set and sort order; otherwise, you will not be able to load the master database dump file.
2. After a successful master database rebuild, shut down the SQL Server and start it in single-user mode from the command line. For example, if the MASTER device location is c:\mssql\data\master.dat, you can start SQL Server in single-user mode by typing this:

    c:\mssql\binn\sqlservr /dc:\mssql\data\master.dat /m

1. Connect to SQL Server as an sa. After the master rebuild, the password is set to NULL. Create a dump device that points to the location of the master dump file you want to restore. Load the master database using the LOAD DATABASE command. After the successful load, SQL Server shuts itself down. Now you can start SQL Server from the command line (without supplying the /m option) or by using the SQL Service Manager graphical tool.

###
Summary

SQL Server provides various T-SQL commands and system stored procedures to perform administrative tasks. Certain tasks such as database backups and transaction log dumps are executed more frequently. It is important that database and transaction logs be backed up regularly to provide full recovery in the event of a disk crash. Based on the activity on your system, you can schedule backups for tables, databases, and transaction logs.