Backup routines

Hi there,

I am looking for some advice and opinions on daily backup routines on SQL2000 and SQL2005, I want to know what peoples best practices are for nightly full backups. Currently I have the following in place,

job: daily backup

step1. truncate log
step2. shrink log
step3. backup
step. updateusage

job weekly admin

step1. defrag indexes (sometimes re-index + update stats)
step2. truncate log
step3. shrink db
step4. checkdb

Does this look good enough?
is there anything else I should add?
What do you have in place currently?

Thanks for your help in advanceYou should do log dumps more frequently to improve recoverability in case of system failure.
I backup my databases nightly, and do hourly log dumps througout the day. All backups and logs are dumped to disk, and then copied to a network location to guarantee recoverability.|||and you ideally want to move a set of backups offsite and preferably not in the same town\city\suburban sprawl office park just in case of things like hurricanes\flooding\tornados.

another hint. you do not want to truncate your logs. you want to back them up.|||I am lazy and manage a number of different servers. My priorities are to have a consistent backup strategy from server to server and to ensure that the backup will always be there when I need it.

For SQL 2000:
I create two maintenance plans (skipping optimizations, shrinkage and integrity checks).
One maintenance plan is for all the system databases. I do a full backup on these nightly.
One maintenance plan is for all user databases. I do a full backup on these nightly and a transaction log backup hourly (in test, only every three hours).

Depending on the size of the backup, I will backup over the network (we are running 1 GB switched) or local to a dedicated dump disk. Generally, if the aggregate of user databases to be backed up is over 25 GB, then I will back it up locally (we use a SAN, I have a separate job that makes an image copy of the backup disk and runs it to tape once per week).

I set the retention policy to a value based on business requirements and available storage.

For SQL 2005:
I create two maintenance plans.
One does a full backup on all databases (user and system). This runs nightly.
The other maintenance plan does a transaction log backup every hour (three hours in test).
I then edit the maintenance plan to add the retention policy and cleanup activity logs and reports.

All that being said, when you design your backup policy, you need to understand:

1. How much data is your business willing to lose (all, some or none)?
2. How much time is your business willing to spend recovering the database (days, weeks, hours, minutes or seconds)?
3. How much money is your business willing to spend on backup and recovery?

These are the principle drivers for your backup strategy. The technology just lets you achieve the requirements that your business sets for you.

Once you have developed your backup strategy be SURE that you test it. Practice recovery at LEAST every three months. Practice different types of recovery (new build, recover to point in time, recover a full backup, etc).

Remember too, that there are other things that you need to backup on your database server besides just the databases:
1. Linked Server settings
2. DTS Packages
3. Logins
4. Jobs

Regards,

hmscott|||You should do log dumps more frequently to improve recoverability in case of system failure.
I backup my databases nightly, and do hourly log dumps througout the day. All backups and logs are dumped to disk, and then copied to a network location to guarantee recoverability.

If the DB is going to keep growing to a certain size, I would not shrink it, just for it to get to that size again, it's a wasted effort, plus you have to allocate all those extents again, and that's overhead.

I usually do

Full (user and system) - nightly with dbcc checkdb catalog, logins, dts jobs, backup devices, etc.
Differential - 3X a day
TLog - Every 1/2 to 1 hr
nightly idx defrag dbcc indexdefrag.
weekly reindex. dbcc reindex.

Backup practices ...

Isn't it better to backup databases and transaction logs to a different
drive than the data files? Both for reliability (if the drive dies you're
hurting since the backup is there as well) and effeciency (less contention
on the data drive). If you're backing up everything to the same drive as the
data, can this cause processor spikes? We do backup to tape as well, but
this is not as fast to recover from when there are a zillion users.
As you probably guessed, some pre-existing maintence plans are set up this
way and I'm thinking I should push to get them changed. But this may require
additional hardware so somebody has to write a check. You know the drill.
Thanks,
Bob Castleman
DBA Poseur"Bob Castleman" <nomail@.here> wrote in message
news:e$madNqaFHA.3684@.TK2MSFTNGP12.phx.gbl...
> Isn't it better to backup databases and transaction logs to a different
> drive than the data files? Both for reliability (if the drive dies you're
> hurting since the backup is there as well) and effeciency (less contention
> on the data drive). If you're backing up everything to the same drive as
the
> data, can this cause processor spikes? We do backup to tape as well, but
> this is not as fast to recover from when there are a zillion users.
> As you probably guessed, some pre-existing maintence plans are set up this
> way and I'm thinking I should push to get them changed. But this may
require
> additional hardware so somebody has to write a check. You know the drill.
> Thanks,
> Bob Castleman
> DBA Poseur
>
Actually Bob, you should always do your backups to the same local drives as
the active databases are on. In fact, I'm not sure why you are even
bothering with doing backups at all... <wink>
You are correct, a good backup (and recovery) strategy should be
implemented. Backing up to the same hard disks is dependent on a lot of
different factors.
1. How much activity is going on during the backup process?
2. How big are the backups that we are talking about?
3. How much network traffic do you have already (if you wanted to push your
backups to a UNC name on a different computer).
That said, having your backups in contention with the active databases on
the same physical drives is almost never a good idea (for the reasons you
listed).
Rick Sawtell|||
> Actually Bob, you should always do your backups to the same local drives
> as
> the active databases are on. In fact, I'm not sure why you are even
> bothering with doing backups at all... <wink>
Facetiously sarcastic. Gotta luv it!

> 1. How much activity is going on during the backup process?
Log backup during the production day every four hours spike the processor
pretty hard. Full backups at night are not an issues, yet.

> 2. How big are the backups that we are talking about?
Several hundred databases. Transaction logs every four hours total about a
gig. Full nightly backups are about 100 gig.

> 3. How much network traffic do you have already (if you wanted to push
> your
> backups to a UNC name on a different computer).
>
None right now. Backups are over a fiber chanel to the RAID and so far
network traffic to the database servers is very low.|||"Bob Castleman" <nomail@.here> wrote in message
news:uKsEHrqaFHA.220@.TK2MSFTNGP10.phx.gbl...
>
> Log backup during the production day every four hours spike the processor
> pretty hard. Full backups at night are not an issues, yet.
>
Well, if you can't get the new hardware, how about doing TLog dumps more
often. You still have the pay the piper for the CPU time, but if they were
every hour instead of every 4 hours, your spikes should be shorter-lived.

> None right now. Backups are over a fiber chanel to the RAID and so far
> network traffic to the database servers is very low.
>
Very nice!!!
Rick Sawtell

Backup of SAN databases

I'm looking for some "best practices" help from the SAN gurus out there.
We're looking to move some of our databases to an EMC SAN. Currently we run
full backups every night and tran logs every 15 minutes, which are then used
by logshipping to restore to warm standby servers.
After moving to the SAN and a clustered environment, the logshipping system
will be dropped. Since the only other reason for backups is DR, there's some
dispute as to whether SQL backups will even be necessary going forward.
We're being told that the SnapView software we're getting with the SAN will
be able to create clones of the production LUNs, from which we can then back
up to tape or restore back to live if/when necessary.
As a long-time DBA it goes against my grain to turn off SQL backups and put
the databases in simple recovery mode <g>. Is that generally how things are
done on a SAN? I should add that I fully understand the "point-in-time"
issue, that is if we take a new clone every night of the live LUNs we may
lose up to 24 hours of transactions if we have to re-sync back. Other than
this issue, are there good or bad sides to SnapView as our primary SQL
backup strategy?
Thanks
Randy Rabin
Hi
No, no, no.
We have a massive SAN environment comprising of EMC and Hitachi, and even
though we use EMC's SRDF to have data moved to DR in real time, we still do
Full backups daily and Log dumps every 15 minutes. I trust EMC to store the
data, but once EMC wants to be the only form of backup I have, I start to
worry. The also have bugs. SnapView and SRDF will faithfully transfer the
corruption that your DB could have to the backup copy as it knows no better.
If you run simple recovery mode, how can you do point in time restores? Most
DB outages are caused by human error like dropping tables, or deleting too
many rows from a table. What it an application error destroys data and then
you get asked, we need a restore up to 10:28 this morning?
Clones of LUNs are not feasible to be run every 15 minutes. Put the dumps on
different LUNS to the data and log. Even an EMC engineer can mess up a
configuration and then you have no good backup.
A SAN is a storage mechanism and not a replacement for good backups.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Randy Rabin" <randyr@.channeladvisor.com> wrote in message
news:e2B9Pp$WFHA.2080@.TK2MSFTNGP15.phx.gbl...
> I'm looking for some "best practices" help from the SAN gurus out there.
> We're looking to move some of our databases to an EMC SAN. Currently we
> run
> full backups every night and tran logs every 15 minutes, which are then
> used
> by logshipping to restore to warm standby servers.
> After moving to the SAN and a clustered environment, the logshipping
> system
> will be dropped. Since the only other reason for backups is DR, there's
> some
> dispute as to whether SQL backups will even be necessary going forward.
> We're being told that the SnapView software we're getting with the SAN
> will
> be able to create clones of the production LUNs, from which we can then
> back
> up to tape or restore back to live if/when necessary.
> As a long-time DBA it goes against my grain to turn off SQL backups and
> put
> the databases in simple recovery mode <g>. Is that generally how things
> are
> done on a SAN? I should add that I fully understand the "point-in-time"
> issue, that is if we take a new clone every night of the live LUNs we may
> lose up to 24 hours of transactions if we have to re-sync back. Other than
> this issue, are there good or bad sides to SnapView as our primary SQL
> backup strategy?
> Thanks
> Randy Rabin
>

Backup Maintenance Plan - Best Practices for SQL 2000

I was once told that I was not to use the GUI to setup a maintenance plan.What the person said was that I needed to setup a different plan to do each tab of the GUI instead of going through the tabs and making sure the times do not overlap.

Has anyone ever heard of this or is this an old wise tale told by only one person?

I am using version 8 of SQL on XP machines with 8 connections to that database and need to back up the database, clean it up and everything the GUI has, so teach me the correct ways and tell me why, thanks!

It's basically a tradeoff of ease of use vs level of control, and that last increment of performance.

The GUI will produce a workable maintenance plan to do what you tell it to.

It will not be optimized to your particular situation.

So, by taking more control and scripting the actions you can arrive at a configuration which is more tuned to your environment, and therefore more efficient.

On the other hand, you can use the GUI and be done with it.

You need to make those tradeoffs for your own situation.

Backup Maintenance Plan - Best Practices

Hi All,

I'm about to embark on creating a maintenance plan to back up all databases on one of our SQL 2005 servers. I am looking for some advice on best practices for doing this.

I have it in my mind that i want to be taking a full database backup once a week, with differential backups on a daily basis and transactional backups performed every 2 to 4 hours.

Do i need to create three maintenance plans for this, i.e. 1 for full, 1 for differential, and 1 for transactional?

If i want to only keep the backups from the last week, is this done by setting up a maintenance cleanup task in the full backup plan to clear all bak files that are a week old?
If so i'll also probably require one to remove the trn files also.

When using the backup command from the context menu in SSMS there is an option to name the backup set. How does this work when using maintenance plans as i haven't been able to find this option whilst trying out some of the features?

I'm sure to have more questions on this subject, but any help on the above queries would be most appreciated.

TIA,

GrantNo. As long as you are running SP2, you can do all of this using subtasks. So, you can create a single maintenance plan and then add subtasks for the full, differential, and tran log. Each of the subtasks can have their own schedule.