December 31, 2007 at 1:23 pm
I just started a DBA Job and I am getting ready to make some recomendations for changing our Backup strategies and I was hoping to get some advice.
This is a mostly SQL Server 2000 Enterprise edition shop (all patched up to sp4) on WIN2k Servers.
They are running weekly 'TO DISK' Full Backups on all system and User Databases to a UNC path.
I just today got them to give me access to the Network Share that the Backups are getting written to - looks to be able 200gb worth of 'TO DISK' Backups using Maintenance Plans in EM.
They are also doing nightly 'TO Tape' Backups and then TLog backups to tape every 15 mins on all Databases in Full Recovery mode. I THINK they are using a Veritas SQL Agent to do the 'TO TAPE' backups but I can't be sure yet.
I would like to reverse the whole scenerio and do the nightly full and every 15 minute tlog backups to disk and then get the full backups offsite on tape everynight by backing up the network share that the backups are written to and then do weekly to tape backups if they feel they absolutely need to.
Does anyone have any suggestions about this?
December 31, 2007 at 1:36 pm
I hate tape agents. There have been numerous restore issues documented with these agents. It might be better with later vesrions, but I highly recommend not using these agents for SQL backups.
I also don't recommend backing up to UNC paths. Any hiccups in the network will cause the backup to fail.
Instead I'd back up to a local drive, then copy the files to a remote server and grab them to tape from there. Or grab them from the local disk to tape.
The backup scheme depends on your environments. I've done daily fulls with logs every xx minutes (5-15). You could sprinkle in a diff here and there to reduce the backup window. In larger databases, I've done a weekly full and daily diffs (1 or more) with logs added in there.
The scheme depends on your tolerance for recovery time. I'd also be sure that you keep the last full and what you need to recover since that on disk at all times.
December 31, 2007 at 1:43 pm
Thanks for the input. I don't have any experience with Tape agents but I had read that they can be somewhat unreliable. In my last shop of was doing all my backups to the local machines and then a nightly FULL to a Network Share (over rUNC) so that I was sure that they were getting to tape and getting offsite.. It's kinda scary that I'm responsible for the data but a whole other department is responsible for backing it up.. this morning I asked about testing a tape restore and i got the "deer in the headlights" blank-stare....
January 2, 2008 at 4:56 am
In order to plan a backup strategy you need to know the recovery SLAs. If there are none, then make them and get them approved.
If your business customers have different expectations of how long it will take to recover their data than you do, sooner or later there will be problems. There is no point in having a backup strategy that is more rigourous than the business and legal requirements need. Likewise, if your business wants very fast recovery then you have a mandate to propose buying the softwaer and hardware to meet those needs.
When you get down to planning what this means to individual backup jobs, you need to consider industry best practice. Otherwise if it goes wrong you will be exposed, regardless of management sign-offs. Steve's recommendation of backing up to local disks is definitely best practice, because it is about the only way you can get a backup you can reliably recover from. (Remember, the only point in taking a backup is so you can make a recovery!) Once you have the backups on local disk or local LUN, you can work with your Windows Support people to get those included in your site management processes.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 2, 2008 at 7:59 am
From a SQL Server 2000 patch perspective, I would recommend you document the current version of each database server.
The current SQL Server 2000 patch is 2000 SP4+916287/914384/898709/915065/915340 (version: 8.00.2187)
A cumulative hotfix package is available for SQL Server 2000 Service Pack 4 build 2187
http://support.microsoft.com/kb/916287/
SQL Server 2000 SP 4 is 8.00.2039.
--
To add to Steve and Ed's comments, I agree that you need to keep at least the previous and current day's BAK and TRN files locally, so that you don't have to deal with copying data back to the local database server in the event of a restore. Copying those files to the "backup" server is your safety net from a file perspective.
Definitely look into the SLA's and get a feel for how quickly folk expect databases to be restored. You will need to be able to perform restore operations to give everyone timing information to give management how much work will be needed to meet of exceed the SLA standards.
If you got a "deer in the headlights" look when in inquired about a restore from tape, then you have a lot of work ahead of you. As you build your backup/recovery process make sure to put in writing that you cannot guarantee the validity of a backup of a given server until you **personally** can restore that data. It is in your best interests to explain/show management the risks of backups without restoring as a part or routine operations. The time consuming part is working with all the members of the organization in building that infrastructure and workload into the current scheme of things.
We feel your pain!
Keep Us Posted.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply