Restoring a Database

Congratulations to Quentin Ran, an independent consultant in Houston, Texas, and Anith Sen, an independent database consultant in Cordova, Tennessee. Quentin won first prize of $100 for the best solution to the March Reader Challenge, "Restoring a Database." Anith won second prize of $50. Here’s a recap of the problem and the solution to the March Reader Challenge.

Problem:

Keith is the DBA for a company that runs several SQL Server 2000 data warehouses. He has a crucial database that contains the fact and dimension tables for the data warehouse. Keith performs full backups every week and periodic log backups after the database backup. The company’s development team has requested the latest copy of the database along with any log backups for testing purposes. The team needs to run ad hoc queries against the database at different points in time to collect statistics. Keith uses the following sequence of steps to create the database and log backups:

CREATE DATABASE DWALTER DATABASE DW SET recovery bulk_loggedRAISERROR ('-- Performing full backup...', 0, 1) WITH nowait-- Full backup of databaseBACKUP DATABASE DW TO DISK = 'c:tempDW.bak' WITH initGO-- Create table t1CREATE TABLE DW..t1 ( i int IDENTITY )INSERT INTO DW..t1 DEFAULT VALUES-- Initial log backupRAISERROR ('-- Initial log backup...', 0, 1) WITH nowaitBACKUP log DW TO DISK = 'c:tempDW.trn.1' WITH init-- Create table t2 for bulk loadingCREATE TABLE DW..t2 ( c char( 8000 ) DEFAULT 'x' )INSERT DW..t2 DEFAULT VALUES-- Add new log file on a different volume because of space constraintsALTER DATABASE DW ADD log FILE ( name = 'DW_TempLog' , filename ='c:tempDW_TempLog.ldf' )-- Bulk inserts and other operations here-- Log backup after first ALTER DATABASE commandRAISERROR ('-- Log backup after first ALTER DATABASE...', 0, 1) WITH nowaitBACKUP log DW TO DISK = 'c:tempDW.trn.2' WITH init-- Remove temporary log fileALTER DATABASE DW REMOVE FILE 'DW_Templog'-- Log backup after second ALTER DATABASE commandRAISERROR ('-- Log backup after second ALTER DATABASE...', 0, 1) WITH nowaitBACKUP log DW TO DISK = 'c:tempDW.trn.3' WITH initDROP DATABASE DWGO

Keith also needs to provide the commands for restoring the database (in read-only format) up to and including the latest log backup, DW.trn.3. Help Keith write the script to restore a read-only copy of the database after different backups have been restored on a development server.

Solution:

Keith can use the STANDBY clause of the RESTORE DATABASE or RESTORE LOG statements to create a read-only copy of the database on a different server. The STANDBY clause requires a file that stores the UNDO actions resulting from uncommitted transactions. To restore the read-only copy of the initial database, Keith can use the following statement:

-- Initial database stateRESTORE DATABASE DW1  FROM DISK = 'c:tempDW.bak'  WITH MOVE 'DW' TO 'c:tempDW1.mdf'    , MOVE 'DW log' TO 'c:tempDW1.ldf'    , STANDBY = 'c:tempDW1.undo'

The MOVE option in the RESTORE statement must specify an alternate path for the database files if they’re in a different location than the original database. Keith can also restore the first log backup that DW.trn.1 contains by using the following statement:

-- Database after first log backupRESTORE LOG DW1  FROM DISK = 'c:tempDW.trn.1'  WITH STANDBY = 'c:tempDW1.undo'

Note that the MOVE option isn’t required in the RESTORE LOG statement because the database’s structure hasn’t changed since the last restore.

To restore the next log backup that DW.trn.2 contains, Keith needs to use the MOVE option again because a new log file was added to the database before the backup. Here is the RESTORE statement that restores the DW.trn.2 backup file:

-- Database after second log backupRESTORE LOG DW1  FROM DISK = 'c:tempDW.trn.2'  WITH MOVE 'DW_TempLog' TO 'c:tempDW1_TempLog.ldf    , STANDBY = 'c:tempDW1.undo'

Keith can restore the last log backup, which DW.trn.3 contains, without using the MOVE option because the restore will remove the log file that he previously added.

-- Database after third log backupRESTORE LOG DW1  FROM DISK = 'c:tempDW.trn.3'  WITH STANDBY = 'c:tempDW1.undo'

Alternatively, Keith can use the STOP AT clause with the RESTORE statements to restore a database to a specific point in time.

APRIL READER CHALLENGE:

Now, test your SQL Server savvy in the April Reader Challenge, "Protecting Against SQL Injection" (below). Submit your solution in an email message to [email protected] by March 18. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Jeff is a database architect, responsible for designing and developing database solutions using SQL Server 2000. Jeff’s main concern when performing code reviews is the use of dynamic SQL in stored procedures that his Web application uses for search purposes. He’s worried that the dynamic SQL will put his system at risk for SQL injection attacks, in which an attacker compromises the system’s security by executing unauthorized code.

The example below uses the sample Northwind database objects to show how a user exploits the dynamic SQL construction in a stored procedure. The Web page in this example lets the user specify searches for customers or suppliers based on relationship, city, company, or contact name. The Web page executes the following stored procedure, SearchCustomersAndSuppliers:

CREATE PROCEDURE SearchCustomersAndSuppliers(@Relationship varchar(9) = NULL,@City nvarchar(30) = NULL,@CompanyName nvarchar(80) = NULL,@ContactName nvarchar(60) = NULL,@Debug bit = 0)ASBEGIN  DECLARE @SQL nvarchar(4000), @WhereClause nvarchar(500)  SET @SQL = N'SELECT City, CompanyName, ContactName, Relationship FROM "Customer AND Suppliers BY City"'  SET @WhereClause = N' WHERE 1=1'  IF @Relationship IS NOT NULL    SET @WhereClause = @WhereClause + ' AND Relationship = ''' + @Relationship + ''''  IF @City IS NOT NULL    SET @WhereClause = @WhereClause + ' AND City LIKE N''' + @City + ''''  IF @CompanyName IS NOT NULL    SET @WhereClause = @WhereClause + ' AND CompanyName LIKE N''' + @CompanyName + ''''  IF @ContactName IS NOT NULL    SET @WhereClause = @WhereClause + ' AND ContactName LIKE N''' + @ContactName + ''''  IF @Debug = 1    PRINT @SQL  IF @Debug = 1    PRINT @WhereClause  EXEC(@SQL + @WhereClause)ENDGO

The parameters for city, company, and contact name let users conduct wildcard searches by using LIKE patterns, meaning they can search for words “like” cty and still get city. The @Relationship parameter limits the search to a specific value.

Using the SearchCustomersAndSuppliers stored procedure, Jeff issues the following command to return the generated queries and a list of customers from the Customers table, which demonstrates the security danger of forming dynamic SQL without specific checks:

EXEC SearchCustomersAndSuppliers  @CompanyName = N'%'';SELECT * FROM customers;PRINT ''',  @Debug = 1

By injecting SQL code into the search parameters an attacker can potentially perform unauthorized actions depending on the permissions of the user account, the Web page, or application executing the stored procedure.

Help Jeff write the dynamic SQL to prevent SQL injection attacks. Ultimately, he wants to suggest to the developers a more secure dynamic SQL approach as a standard technique for stored procedures that require dynamic SQL.