RESTORE DATABASE ... WITH PARTIAL

[Utahduck]

I posted this to another group but I must not be subscribed as nothing
appeared. Sooo....

I've backed up a databse with the following command:

BACKUP DATABASE Thomasville
FILEGROUP = 'PRIMARY'
TO DISK = 'D:\SQLBackups\ 2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak'
WITH FORMAT,
Password = 'p@$$w0rd'

I back up using Filegroups because I dont' want to back up the large,
static ones on a regular basis. This backup worked just fine. When I
try to restore it I run:

RESTORE DATABASE Thomas
FILEGROUP = 'PRIMARY'
FROM DISK = 'D:\SQLBackups\ 2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak'
WITH PARTIAL,
PASSWORD = 'p@$$w0rd',
MOVE 'Thomasville_Da ta' TO 'd:\SQLServerDa ta\MSSQL\data
\Thomasville_Da ta_Thomas.MDF',
MOVE 'Thomasville_Lo g' TO 'd:\SQLServerDa ta\MSSQL\data
\Thomasville_Lo g_Thomas.LDF',
NORECOVERY

I get this:

Server: Msg 3135, Level 16, State 2, Line 1
The backup set in file 'D:\SQLBackups\ 2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak' was created by BACKUP DATABASE...FILE =<name>
and cannot be used for this restore operation.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Any ideas what I'm doing wrong here? Except for what applies to my
backup this is copied character for character from some Microsoft BOL.

Please help.

Thanks,
Utah

[Erland Sommarskog]

(Ut******@hotma il.com) writes:

I back up using Filegroups because I dont' want to back up the large,
static ones on a regular basis. This backup worked just fine. When I
try to restore it I run:

RESTORE DATABASE Thomas
FILEGROUP = 'PRIMARY'
FROM DISK = 'D:\SQLBackups\ 2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak'
WITH PARTIAL,
PASSWORD = 'p@$$w0rd',
MOVE 'Thomasville_Da ta' TO 'd:\SQLServerDa ta\MSSQL\data
\Thomasville_Da ta_Thomas.MDF',
MOVE 'Thomasville_Lo g' TO 'd:\SQLServerDa ta\MSSQL\data
\Thomasville_Lo g_Thomas.LDF',
NORECOVERY

I get this:

Server: Msg 3135, Level 16, State 2, Line 1
The backup set in file 'D:\SQLBackups\ 2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak' was created by BACKUP DATABASE...FILE =<name>
and cannot be used for this restore operation.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

In SQL 2000 you can only do partial restore from a full backup. SQL 2005
provides new options, although I am not certain that they addrss your
needs.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx