question

MuhSQL avatar image
MuhSQL 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
homeworkbackup-restore
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton avatar image Shawn_Melton commented ·
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.
0 Likes 0 ·
MuhSQL avatar image MuhSQL commented ·
Thank u Shawn. I will try to find it.but it will be good to discussion again..would u plz give me the link for these answers..I will be grateful. Thanks
0 Likes 0 ·
Shawn_Melton avatar image Shawn_Melton commented ·
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.
0 Likes 0 ·
Henrik Staun Poulsen avatar image
Henrik Staun Poulsen answered
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/
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MuhSQL avatar image MuhSQL commented ·
Thank you.but I have many books no one talk about this subject in details also I tried to find it through Internet but no answer????/
0 Likes 0 ·
MuhSQL avatar image MuhSQL commented ·
Therefor I made this example as reference to be benefit for others.But no answers??????
0 Likes 0 ·
Shawn_Melton avatar image
Shawn_Melton answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MuhSQL avatar image
MuhSQL answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton avatar image Shawn_Melton commented ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.