asked
How make File/Filegroup Backup & restore Strategy
I have a question about the file or filegroup backup and restore.... If I have a Dada base called [Test] with the following files and groups CREATE DATABASE [test] ON PRIMARY ( NAME = N'test', FILENAME = N'C:\DATA\test.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ), FILEGROUP [RO_FG] ( NAME = N'test2', FILENAME = N'C:\DATA\test2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ), FILEGROUP [RW_FG] ( NAME = N'test3', FILENAME = N'C:\DATA\test3.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'test_log', FILENAME = N'C:\DATA\test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%) GO ALTER DATABASE [test] SET PAGE_VERIFY CHECKSUM GO USE [test] GO IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'RW_FG') ALTER DATABASE [test] MODIFY FILEGROUP [RW_FG] DEFAULT GO ![alt text][1] also I created Backup Device for this database to hold the backup files USE [master] GO EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'test', @physicalname = N'C:\test.bak' GO supposed our database is VLDB If we need to apply the following strategy in Backup and restore how we will do int by TSQL??? - FullBackup all database on Monday every 3 months . - FullBackup RO_FG on Monday every month ---Q1 did we need that or n't ,or we will just restore it from full backup.?????? - Log backup every 5 hours. - Diff backup just for RWFileGroup.every day. Q2- How we will do the backup by TSQL???? Q3- And if our FileGroup RW_FG (FileGroup2 in pic) crashed on Wed at 12 how we will restore by TSQL?? ![alt text][2] [1]:
http://bakeryforsell.comeze.com/4.jpg [2]:
http://bakeryforsell.comeze.com/3.jpg
Backup and Restore scripting is documented pretty well in BOL and examples can be found on many different blogs on SSC. That is why there is not many answers. This subject has been written and talked about many times. It is a task that most DBAs are going to learn first thing and is vital in any environment.
I have converted your answers to comments, in place of providing repeated answers simply commenting keeps the conversation going in an more readable fashion. I will provide the links to each of your questions as an answer.
I would take a look at the SQL Deep dives book[link text][1], chapter 33. It will help you creating the restore scripts needed. [1]:
http://www.manning.com/nielsen/
This is the BOL reference to [taking files and filegroup backups][1] and [restoring file and filegroup backups][2]. The backup strategy your suggest I don't think you will every see used in a real world environment. I have not done or used file group backups but understand restores can be time consuming and get complicated quickly. So for (Q3), keeping with your strategy, that filegroup fails on Wednesday your are SOL. In order to even take a [differential file backup you are required to have a full file backup as a base][3] to start from. So your job to take the diff backup would fail anyway and you would not have a backup to go from which would then require you to do a full restore of the whole database. To fully restore the database will require the full database backup and your logs, in your strategy. The filegroup backups can only be used to restore that filegroup fully, not the full database. So if that is true and you loose the whole thing you will restore the last full backup (taken every 3 months) and every log backup since that full (taken every 5 hours). That means at least 144 log backups for each 30-day month (x3 depending on when the failure occurred within the 3 month period) would need to be restored. [1]:
http://msdn.microsoft.com/en-us/library/ms179401.aspx [2]:
http://msdn.microsoft.com/en-us/library/ms190710.aspx [3]:
http://msdn.microsoft.com/en-us/library/ms190939.aspx
Hi..Shawn OK what should I do if I have VLDB ???who we will deal with very large database let suppose you have 3 tables one of then 1TB and the others(2 tables) may be each of them is 200 MB . and some one by accident delete the table (200MB). now I should restore all the data ??? therefor I need the filegroup to restore just the table was deleted ....what you think ???? what is the good scenario for VLDB???? I checked many book and the was to dealing with filegroup but when I stated to do it I failed to do it. let we do these backup:- - full Database.(1) - full RO_FG (2 this FG contain data just read only) - diff RW_FG. (3) - log. (4) - Diff RW_FG. (5) - log. (6) - log. (7). Suppose that these steps is correct (but I don't know if (2) we should do it or n't plz tell me about this point) how we will restore database from the these backup... did we need to make Tial(0) log file first then we do restore (1)(2)(5)(6)(7)(0) or (1)(5)(6)(7)(0) or we just restore the filegroup from the full(backup). Sorry if I have many quotations.
Nope you are still out of luck as (3) and (5) cannot be done until you do a FULL file/group level backup of RW_FG. Then if said table is located in RW_FG table, you would first do a tail-log backup. Then restore your last FULL file backup of RW_FG, restore the latest diff of RW_FG, then in sequence your log backups, and then the tail-log backup.
