September 29, 2010 at 3:10 pm
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
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy