Post reply

Partial Backup and Partial Restore

  • September 29, 2010 at 3:10 pm

    #145414

    I have a database with 2 file groups. One is the Primary file group and the second one is Second File group. There are 5 tables that are located in the Second File group.

    I need to create a backup of that database and restore only the file group Primary because we don’t need the other 5 tables from the Second File Group.

    I created a Partial Backup of the Primary File Group like:

    BACKUP DATABASE [Database]

    FILEGROUP = 'Primary'

    TO DISK = N'F:\DB-Restore\Database_FullBackupPrimary_0929.bak'

    WITH NOFORMAT, NOINIT,

    NAME = N' Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    I see that the size of the Partial Backup is much smaller than the Backup (all file groups). So I'm assuming this is correct.

    I tried to do a Partial Restore or Restore of that partial backup, but the process is restoring the Second File Group and the 5 tables that I don’t need as well.

    This is what I did to do the Partial Restore.

    RESTORE FILELISTONLY FROM DISK = 'D:\VwDb_FullBackupPrimary_0929.bak'

    I check the files in that backup; It shows me the 2 File Groups. Weird because I did a Partial Backup and the size of the .bak file is smaller.

    I did a partial restore, but it’s not working. I'm still getting the 2 File Groups and the 5 tables that I don’t need.

    RESTORE DATABASE [DatabaseNew]

    FILE = 'Database',

    FILEGROUP = 'Primary'

    FROM DISK = N'D:\Database_FullBackupPrimary_0929.bak'

    WITH --FILE = 1,

    MOVE N'database' TO N'D:\MSSQL\Data\databasenew.mdf',

    MOVE N'database_log' TO N'D:\MSSQL\Data\databasenew_log.ldf',

    RECOVERY, NOUNLOAD, REPLACE, STATS = 10

    What I'm doing wrong?

  • September 29, 2010 at 3:37 pm

    #1229375

    Okay.. I just reviewed my new database and I see that the Second File Group is offline, same thing with the tables that are located in the Second File Group. Also, I see that the size of the database is smaller than the original one.

    so this Partial Backup/Restore works well.

    I was trying to get a smaller reserve size of the database because the size is big. The original database is around 50GB in total-size and only 10MB are between the 2 file groups. When I do the partial restore I see that the Database Size (with the reserve space) is still 50GB, but only 5GB are in space of the partial restore, which is good. But, Is anyway I can have a smaller reserve space? I will need to repeat this kind of process once a month

    Even if the tables are offline 'cause the filegroup is offline, we dont need them in this restore. What is the best practice for this case?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply