Restoring Database Backups

Congratulations to Willem Grobbelaar, a SQL Server DBA for Comparex Africa in Cape Town, South Africa, and Simon Lidberg, in Stockholm, Sweden. Willem won first prize of $100 for the best solution to the September Reader Challenge, "Restoring Database Backups." Simon won second prize of $50. Here’s a recap of the problem and the solution to the September Reader Challenge.

Problem:

Kumar is a DBA who manages several SQL Server 2000 installations. The databases are organized in filegroups and range in size from 50GB to 100GB. The following code creates a sample NorthwindDW database and tables that show the filegroups’ layout:

CREATE DATABASE \[NorthwindDW\]
ON
PRIMARY( NAME = N'NorthwindDW_Data', FILENAME = N'C:\NorthwindDW_Data.mdf'),
FILEGROUP \[fg_NorthwindDW_Y2002\] (NAME = N'NorthwindDW_Y2002',
 FILENAME = N'C:\NorthwindDW_Y2002.ndf'),
FILEGROUP \[fg_NorthwindDW_Y2003\] (NAME = N'Northwind_Y2003',
 FILENAME = N'C:\NorthwindDW_Y2003.ndf')
LOG ON (NAME = N'NorthwindDW_Log', FILENAME = N'C:\NorthwindDW_Log.ldf')
COLLATE sql_latin1_general_cp1_ci_as
GO
USE NorthwindDW
GO
CREATE TABLE Products( ProductID int PRIMARY KEY )
CREATE TABLE Orders_M20020101(
OrderID int PRIMARY KEY, ProductID int REFERENCES Products ) ON fg_NorthwindDW_Y2002
CREATE TABLE Orders_M20020102(
OrderID int PRIMARY KEY, ProductID int REFERENCES Products ) ON fg_NorthwindDW_Y2002
CREATE TABLE Orders_M20030101(
OrderID int PRIMARY KEY, ProductID int REFERENCES Products ) ON fg_NorthwindDW_Y2003
CREATE TABLE Order_M20030102(
OrderID int PRIMARY KEY, ProductID int REFERENCES Products ) ON fg_NorthwindDW_Y2003

The database contains filegroups for each year that contain fact tables for that year only. Kumar performs a full backup every Sunday and differential backups on the other days. The list below shows the details for the three most recent backups:

1. Sunday - Full Backup: NorthwindDW_full.bak
2. Monday - Differential Backup: NorthwindDW_diff_1.bak
3. Tuesday - Differential Backup: NorthwindDW_diff_2.bak

For testing purposes, Kumar needs to retrieve fact data for the year 2002 only and manipulate that data on a test SQL Server installation. Help Kumar restore the database backups efficiently, conserving disk resources and time.

Solution:

Kumar uses SQL Server 2000’s partial restore operation to restore the large database backups with only the data that he needs. Because he is interested in restoring only table t1, he partially restores the filegroup fg_sqlmagtest1 from the available backups and retrieves the necessary data.

First, he restores the full backup sqlmagtest_full.bak from Sunday. The following RESTORE command performs the filegroup’s partial restore with NORECOVERY:

RESTORE DATABASE sqlmagtest2 filegroup=N'fg_sqlmagtest1'
   FROM
        DISK = N'c:\sqlmagtest_full.bak'
   WITH NORECOVERY,
        PARTIAL,
        move N'sqlmagtest_Data' TO N'd:\sqlmagtest_Data.mdf',
        move N'fl_sqlmagtest1' TO 'd:\sqlmagtest1.ndf',
        move N'sqlmagtest_Log' TO 'd:\sqlmagtest_Log.ldf'

The NORECOVERY option instructs SQL Server not to perform any recovery operations, such as rolling back uncommitted transactions. This option is needed to perform multiple restore operations on a single database and when applying transaction log backups on a restored database. The PARTIAL clause in the RESTORE command instructs SQL Server to restore a portion of the database. Additionally, the RESTORE command specifies the required filgroup, fg_sqlmagtest1. The primary data file, log file and filegroup that include the new filegroup can then be moved to a different location on the new server by using the move option.

Then, Kumar applies the latest differential backup, sqlmagtest_diff_2.bak from Tuesday, and recovers the database. The following RESTORE command applies the differential backup on the last full backup:

RESTORE DATABASE sqlmagtest2 filegroup=N'fg_sqlmagtest1'
   FROM DISK = N'c:\sqlmagtest_diff_2.bak'
   WITH PARTIAL, 
        move N'sqlmagtest_Data' TO N'd:\sqlmagtest_Data.mdf',
        move N'fl_sqlmagtest1' TO 'd:\sqlmagtest1.ndf',
        move N'sqlmagtest_Log' TO 'd:\sqlmagtest_Log.ldf'

By restoring a single filegroup from the database backups, Kumar uses the disk resources efficiently on the new server and reduces the time taken to restore the required data.

OCTOBER READER CHALLENGE:

Now, test your SQL Server savvy in the October Reader Challenge, "A New View" (below). Submit your solution in an email message to [email protected] by September 18. SQL Server MVP 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.

Greg is a database analyst for a company hosting databases on SQL Server 7.0 and 2000 servers. He has several different users creating objects for applications in their respective schema. They don’t have permissions to query system tables directly and have been explicitly denied access on system tables such as sysobject or syscomments. The users obtain most schema information from system stored procedures, INFORMATION_SCHEMA views and metadata functions. Greg wants to additionally provide them with a view that can provide details about computed columns on tables because this information isn’t available from any system stored procedures or views. For simplicity, he wants to create a view modeled after the existing INFORMATION_SCHEMA view.

Help Greg create a view named INF_SCHEMA_COMPUTED_COLUMNS that satisfies the following requirements:

1. View should be queryable by any user who has access to the database
2. View should list only user-defined tables that contain computed columns
3. View should list only tables or columns for which the user has permissions to SELECT
4. View should provide the owner, name of the table, column name, ordinal position and expression for the computed columns