Author |
Topic |
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2011-09-20 : 20:49:03
|
I am new sqlserver Database administration..I would like to automate and create a database backup strategy on sqlserver 2008 R2 for full backup,differential backup,transactional backup.Please provide me automation scripts..Thanks for your help in advance ! |
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2011-09-21 : 01:43:06
|
Are you saving to disk or are you intending on using a third party backup took such as TDP sql client? Ultimately , both use the same commands - but the automation process is handled differently?Do you want to manage the automation through SQL Server Agent or some othe rscheduling software?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-21 : 06:16:28
|
"Please provide me automation scripts.."http://weblogs.sqlteam.com/tarad/archive/2009/12/29/Backup-SQL-Server-2005-and-2008-DatabasesAgain.aspx |
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2011-09-21 : 22:01:50
|
Thanks Kristenjackv:I am not sure which is best way to implement the backup stragerywith sqlserver scripts or third party software..I would like to back up on the drive as well as on to tape..What are the benefits |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2011-09-22 : 00:41:21
|
1) Whether to backup using third party software? This will depend on the organisations overall backup implementation. For example , is there also a requirement to backup the files on the server? How many servers ? When you start getting into that level of backup third party software can make the management easier. Currently I'm using TDP SQL part of the TSM framework. The advantage is a)TSM has it's own scheduler which send commands to the sql server b)it has different clients - therefore you can manage different platforms such as DB2 ,SQL Server ,Oracle in one area.Of course , you have to weigh up licensing and management costs2)Backing up onto drive or tape? Firstly, backup to tape , this is critical. You need to decide whether you'd backup to disk first and then move to tape. Normally, I backup to disk , and then move to tapeJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-22 : 02:45:14
|
We backup to disk first, then tape. It takes hours to get a restore from tape scheduled - the tape systems are very busy! - so we like to have a week or so backups on disk so we can restore "most use cases" from disk, promptly. |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-09-22 : 10:31:00
|
I wish I had the storage available to keep weeks of backups online, but alas - 1.4TB databases are still very large backup files even when compressed using Litespeed.It took me quite a while to get the SAN team to understand that I needed at least 3x the amount of space allocated to the data drive for backups before I had Litespeed (or 2008 and compressed backups). Now, with either of those it is easier to manage but still not going to get 5TB of backup storage to keep a weeks worth of backups online - and that for only one system. |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-09-22 : 10:35:59
|
quote: Originally posted by sqlfresher2k7 Thanks Kristenjackv:I am not sure which is best way to implement the backup stragerywith sqlserver scripts or third party software..I would like to back up on the drive as well as on to tape..What are the benefits
Database Maintenance is much more than just backing up the databases. That is a very important part of the equation - but not the only piece. You need to consider when (and how often) you perform integrity checks. You really should do that daily and I prefer having that done before the backup - and failing the backup process (don't backup) if it fails.There are also index rebuilds, statistics updates, removing old backup files, history cleanup, etc...And finally, any backup scheme needs to be defined by your business requirements. How often do you need to backup the transaction log? Do you need to perform differential backups? Daily full backups or weekly full with daily differential?These questions will be answered by your business requirements and driven by the RTO (recovery time objective). In other words, how long can the system be unavailable while you are restoring the system?Jeff |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-22 : 11:02:54
|
"I wish I had the storage available to keep weeks of backups online, but alas"Yes, we are somewhat "alas" too!We do weekly Full and daily Diff to keep the sizes small enough to hold a decent history online. Depends what the hit-rate is on your Diffs though ... a few index rebuilds and the Diff filesizes are looking like Fulls |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2011-09-23 : 01:36:17
|
Another factor to consider is : retention period. How long should you keep the backup copies on tape? This will depend on business requirements - for example , if there is financial data , there may be a requirement to keep all databases for a number of years. This will impact significantly on tapes and storage of tapesJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
Expressyourself
Starting Member
1 Post |
Posted - 2011-10-02 : 14:15:44
|
Please firstly introduce me about it. I am totally nwe in this field.unspammed |
|
|
|