May 21, 2008 at 10:50 am
How do you back up TB size database? SQL native backup, tape backup, file group backup? Any hardware and software will help for both backup and restore?
How long does the backup/restore take?
Thank you.
May 21, 2008 at 11:27 am
Grab one of the 3rd party backup solutions that offer compression. Most of the time of the backup is writing the backup out to disk/tape. I use LiteSpeed. There are others.
You can do filegroup backups if you have multiple filegroups and the time you have available is too small to backup the entire database. It does increase the complexity of recovery, so be sure you're comfortable with it.
What we do (1.1 TB database) is to do a full backup on a saturday when we have a few hours free, and then a diff backup each day of the week with tran log backups running throughout the day
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2008 at 11:50 am
Gila Monster, do you use multiple backup files, and if so how many? Or do you seperate backup threads by filegroup backup?
Backup performance is always an interesting topic... Myself, I have a set of procedures that take a configureable # of backup devices, so I can adjust the number as needed on a per-db basis.
I've found creating n-1 backupfiles/threads based on the number of cpu's in the server to be about as good as it gets for easiest backup tuning. This is on a system where databases are backed up one at a time in an iterative manner. If you're backing up multiple at the same time, apply the same rule, but overall to what's running simultaneously.
Your friendly High-Tech Janitor... 🙂
May 21, 2008 at 1:46 pm
Gordon (5/21/2008)
Gila Monster, do you use multiple backup files, and if so how many?
Not at the moment, though we are considering it. Right now we only have a single backup drive.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 22, 2008 at 1:19 pm
Use the Third Party tools "Litespeed from QUEST". It will compress the backup upto 60-70%.
Litespeed has so many new features such as you can backup the database and keep that into multiple locations. ex. Local prod server(Seattle), DEV sever(Boston), TEST server(Texas). It will happen in the same time and same spped.
If you want you can create executable files as well. Just copy that file in different locations and double click on that and restore it.
You can restore only particular Object from the Full backup as well.
And also so many features having the "Litespeed"
MCP, MCTS (GDBA/EDA)
May 22, 2008 at 1:36 pm
Thank you all for your reply.
There are many things can be done to make a reliable and fast backup/restore for TB sized database. SQL server full/diff/log, filegroups, 3rd party tools, network configuration, hardware, etc.
I would like to hear how the others handle these nowadays? Must have different strategies.
May 22, 2008 at 1:54 pm
I am in TB environemnt and I would recommed Litespeed is the best solution for that. I do not have any issue with Litespeed.
MCP, MCTS (GDBA/EDA)
May 22, 2008 at 2:05 pm
What is the DB size? How long to take a full backup? How long for the restore?
May 22, 2008 at 3:05 pm
I had 15 TB and it tooks me 6 hours to backup and for restoration it tooks me 3 1/2 hours from litespeed.
MCP, MCTS (GDBA/EDA)
May 22, 2008 at 4:13 pm
Good Info...but plz more information
in one month :
Full bck - how many times
Diff bck - how many times
log bck - how many times
and when do you replace for example Full bck after 2 weeks or after a month
etc etc!
thnx for all infos!
May 22, 2008 at 4:53 pm
It depends on your organization and the application criticality as well.
I would recommend you the following.....
1) Full backup Daily after business hours,
2) If possible make 4hours diff. backup
3) evry 15 mints or 30 mints Tlog backup (Make sure your DB in FULL recovery mode).
Reteintion period will also depend.
If you have Tape bakup Config then no need to keep more than 3 days Full backup files on your local server.
For Differential:- Delete 3 days older files.
For Tlog:- Delete 2 days older files from your local server.
MCP, MCTS (GDBA/EDA)
May 22, 2008 at 5:17 pm
Manoj (5/22/2008)
I had 15 TB and it tooks me 6 hours to backup and for restoration it tooks me 3 1/2 hours from litespeed.
Manoj, that is very good figure. Does the backup go to disk? Do you have tape backup afterwards?
How big is the backup file? How long does it take to go to tape?
What kind of environment for the whole backup process? Server configure, network, tape backup software/hardware, etc. I am interested in this setup.
May 22, 2008 at 10:06 pm
Again it depends on your organization. How would you like to do cofig your system and your DB.
MCP, MCTS (GDBA/EDA)
May 23, 2008 at 12:31 am
May 23, 2008 at 2:21 am
15TB is not a size to run a "regular" backup, or a fuzzy backuptool with build-in compression. That's impossible to do with that size. You need to change the concept of availability with this size. I would suggest to take a look at several storage vendorsites and dig for some whitepapers.
I know if you have a NetApp environment, you can do SQL backups using a snapshot technology, which is very fast. So ask your storage administrator for backup solutions offered by your storage vendor.
Wilfred
The best things in life are the simple things
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply