RESTORE DATABASE ... WITH PARTIAL

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_Data' TO 'd:\SQLServerData\MSSQL\data
\Thomasville_Data_Thomas.MDF',
MOVE 'Thomasville_Log' TO 'd:\SQLServerData\MSSQL\data
\Thomasville_Log_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(Utahduck@.hotmail.com) writes:

Quote:

Originally Posted by

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_Data' TO 'd:\SQLServerData\MSSQL\data
\Thomasville_Data_Thomas.MDF',
MOVE 'Thomasville_Log' TO 'd:\SQLServerData\MSSQL\data
\Thomasville_Log_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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

RESTORE DATABASE ... WITH PARTIAL

I've been having some issues so forgive me if this double-posts.

Because I have some large but static Filegroups within a database I
prefer to backup my filegroups instead of everything at once. This is
SQL Server 2000.

The other day I backed up a file with this command:

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

The backup worked flawlessly. But when I try to restore it into
another database:

RESTORE DATABASE Thomas
FILEGROUP = 'PRIMARY'
FROM DISK = 'D:\SQLBackups\2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak'
WITH PARTIAL,
MOVE 'Thomasville_Data' TO 'd:\SQLServerData\MSSQL\data
\Thomasville_Data_Thomas.MDF',
MOVE 'Thomasville_Log' TO 'd:\SQLServerData\MSSQL\data
\Thomasville_Log_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.

It works if I remove the PARTIAL clause but then I can't recover my
database afterwards. I need the PARTIAL because I'm only restoring
one filegroup and not the entire database.

Please help.

-UtahduckOn Apr 12, 3:21 am, "Utahduck" <Utahd...@.hotmail.comwrote:

Quote:

Originally Posted by

I've been having some issues so forgive me if this double-posts.
>
Because I have some large but static Filegroups within a database I
prefer to backup my filegroups instead of everything at once. This is
SQL Server 2000.
>
The other day I backed up a file with this command:
>
BACKUP DATABASE Thomasville
FILEGROUP = 'PRIMARY'
TO DISK = 'D:\SQLBackups\2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak'
WITH FORMAT
>
The backup worked flawlessly. But when I try to restore it into
another database:
>
RESTORE DATABASE Thomas
FILEGROUP = 'PRIMARY'
FROM DISK = 'D:\SQLBackups\2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak'
WITH PARTIAL,
MOVE 'Thomasville_Data' TO 'd:\SQLServerData\MSSQL\data
\Thomasville_Data_Thomas.MDF',
MOVE 'Thomasville_Log' TO 'd:\SQLServerData\MSSQL\data
\Thomasville_Log_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.
>
It works if I remove the PARTIAL clause but then I can't recover my
database afterwards. I need the PARTIAL because I'm only restoring
one filegroup and not the entire database.
>
Please help.
>
-Utahduck

tnnx