June 8, 2009 at 11:14 am
I'm looking for a way to reduce my non production database size. Current production db is 800g, 500g of this is history and archived data. When I bring this database down to the other platforms I'd like to trim down the DB.
Currently we backup and restore the entire DB. My thought was to move the history and archived tables to a separate filegroup and exclude it from the backup and restore.
Is this possible. I can run a filegroup specific backup, but is it possible to do the restore without the other (non primary) filegroup? Every article I've read discusses the need to restore the tranlogs. Is this required?
June 14, 2009 at 5:33 am
christian.benvenuto (6/8/2009)
Currently we backup and restore the entire DB. My thought was to move the history and archived tables to a separate filegroup and exclude it from the backup and restore.Is this possible. I can run a filegroup specific backup, but is it possible to do the restore without the other (non primary) filegroup? Every article I've read discusses the need to restore the tranlogs. Is this required?
Yes, it is possible to restore a single filegroup in conjunction with the PRIMARY filegroup (which is a must). If the historical data will be read-only, you can make that filegroup read-only and then take Partial Backups (which will include all primary filegroup and all other read-write filegroups)
You need tran log backup to ensure point-in-time recovery and to keep your log from filling ur hard drive if your database is in full recovery mode.
In simple recovery mode, there cant be any log backup so no question of restoring them.
Following links will guide you further.
http://sqlserverpedia.com/wiki/Restoring_File/Filegroup_Backups#Filegroup_Restores
http://msdn.microsoft.com/en-us/library/ms178099.aspx
http://msdn.microsoft.com/en-us/library/ms179401(SQL.90).aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply