THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Having fun with new RESTORE options in 2005

You might know that we have some new options for backup and especially restore in SQL Server 2005. Below script was inspired by a newsgroup post regarding not being able to restore an old readonly filegroup backup into a new database.

Here is what I wanted to try:

  • Setting a filegroup to read only, do a backup and then forget about this filegroup. This was not an option in earlier releases. In 7.0 and 2000, restoring a file or filegroup backup meant that you also need to restore all subsequent log backups. Not very convenient if it is Dec 10 and the backup was performend Jan 01. We can do this now, but the filegroup must have been readonly all the time since the backup of that filegroup was performed.
  • Piecemal restore. This allow you to first restore the primary filegroup, and immediately after let the users in to the database. You can now, at your leisure, restore the other filegroups. This allow for the users to more quickly get into the database. They don't have to wait 10 hours for you to restore that large history table, which is only used for reporting anyhow.
  • Do above into a new database name. Say that you had to rebuild the old server and only have the backup files available.

Below is the script. Note that it will drop the databases X and X2, if such exists:

USE master
GO
IF DB_ID('x') IS NOT NULL DROP DATABASE x
GO
IF DB_ID('x2') IS NOT NULL DROP DATABASE x2
GO
CREATE DATABASE [x] ON  PRIMARY
( NAME = N'x', FILENAME = N'C:\x.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
 FILEGROUP [STUFF]
( NAME = N'x_stuff', FILENAME = N'C:\x_stuff.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'x_log', FILENAME = N'C:\x_log.ldf' , SIZE = 3072KB , FILEGROWTH = 10%)
GO

CREATE TABLE x..t(c1 int) ON "PRIMARY"
INSERT INTO x..t VALUES(1)
CREATE TABLE x..t_stuff(c1 int) ON STUFF
INSERT INTO x..t_stuff VALUES(1)
GO
ALTER DATABASE x MODIFY FILEGROUP STUFF READONLY
GO
BACKUP DATABASE x FILEGROUP = 'STUFF' TO DISK = 'C:\x_fg_STUFF.BAK' WITH INIT
GO
INSERT INTO x..t VALUES(2)
GO
BACKUP DATABASE x READ_WRITE_FILEGROUPS TO DISK = 'C:\x_fg_rw.BAK' WITH INIT
GO

--Restore the primary (read/write) filegroup
RESTORE DATABASE x2 FROM DISK = 'C:\x_fg_rw.BAK'
WITH
 PARTIAL
,MOVE 'x' TO 'C:\x2.mdf'
,MOVE 'x_stuff' TO 'C:\x2_stuff.mdf'
,MOVE 'x_log' TO 'C:\x2_log.mdf'
,REPLACE

SELECT * FROM x2..t --OK
GO
SELECT * FROM x2..t_stuff --Fails, as expected
GO

--Restore the other filegroup
RESTORE DATABASE x2 FROM DISK = 'C:\x_fg_STUFF.BAK'
WITH
 MOVE 'x' TO 'C:\x2.mdf'
,MOVE 'x_stuff' TO 'C:\x2_stuff.mdf'
,MOVE 'x_log' TO 'C:\x2_log.mdf'
GO

SELECT * FROM x2..t --OK
SELECT * FROM x2..t_stuff --OK

 

 

Published Friday, February 09, 2007 2:32 PM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement