Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Database backups

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
Go to Top of Page

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
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-09-21 : 22:01:50
Thanks Kristen

jackv:I am not sure which is best way to implement the backup stragery
with 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
Go to Top of Page

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 costs
2)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 tape

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

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.
Go to Top of Page

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.

Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-09-22 : 10:35:59
quote:
Originally posted by sqlfresher2k7

Thanks Kristen

jackv:I am not sure which is best way to implement the backup stragery
with 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
Go to Top of Page

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
Go to Top of Page

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 tapes

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -