Partial restore to a different database

Question

I have a database with 3 filegroups: PRIMARY, AP, and GL.

I did a full database backup. I did a partial restore of just the PRIMARY and AP files successfully using this command:

-- Partial Backup (Primary & AP) to same database (works)
RESTORE DATABASE TestPartial
    FILE = N'TestPartial',
    FILE = N'TestPartialAP'
    FROM DISK = N'J:\SQL\MSSQL10_50.SGA\MSSQL\Backup\TestPartial.bak'
    WITH
        FILE = 1, NOUNLOAD, REPLACE, PARTIAL, STATS = 10

Is it possible to do this same partial restore, but to a different database? I have a database called TestPartial1, which I am trying to partially restore into. However, I'm getting the following error:

The backup set holds a backup of a database other than the existing 'TestPartial1' database.

Here is my code:

-- Partial Backup (Primary & AP) to a different database (doesn't work)
RESTORE DATABASE TestPartial1
    FILE = N'TestPartial',
    FILE = N'TestPartialAP'
    FROM DISK = N'J:\SQL\MSSQL10_50.SGA\MSSQL\Backup\TestPartial.bak'
    WITH
        FILE = 1, NOUNLOAD, REPLACE, PARTIAL, STATS = 10,
        MOVE N'TestPartial' TO N'J:\SQL\MSSQL10_50.SGA\MSSQL\DATA\TestPartial1.mdf',
        MOVE N'TestPartial_log' TO N'J:\SQL\MSSQL10_50.SGA\MSSQL\DATA\TestPartial1_log.ldf',
        MOVE N'TestPartialAP' TO N'J:\SQL\MSSQL10_50.SGA\MSSQL\DATA\TestPartial1AP.ndf'

Answer 1

Is it possible to do this same partial restore, but to a different database?

Short answer I'm afraid, no.

Best bet is to do a partial restore to a new database then copy the tables and data to your target with:

• SSIS Transfer SQL Objects Task. If this is a one off just use the import/export wizard in SSMS.
• Use an INSERT SELECT script
• If the tables are large, consider BULK INSERT.