May 22, 2009 at 7:06 am
Quick question,
My IT director has asked if it's okay to just run daily backups of the Data folder to save tape space.
Currently he's saving both the Data and Backup folders to tape every night. By not saving the backup folder he saves a ton of space. We have a maintenance plan that backups the dbs daily and we save about 3 weeks worth on the hard drive. Does this sound okay? What is common practice out there?
EDIT: I forgot to mention that we are doing FULL backups
May 22, 2009 at 7:28 am
You have to save Backup files either Full, Log or Diff backups. If ur backups r being done in default, Backup folder by your maintenance plan then it makes sense to save those folder.
I havent heared of anyone saving there data folder. Data folder by default, has your Data file and log files (usually .mdf, .ldf or .ndf). In an event of an crash, u will need backup files, these Data folder files in case of I/O crash will be corrupted and really pain or impossible to recover for ur Database to operate in proper way.
Just take regular Backups, and store those backups in a different location other than where ur server is.
May 22, 2009 at 7:29 am
Also, test ur backups regulalrly by restoring them so u r not in shock on the day of crash.
May 22, 2009 at 8:17 am
What if your hard drive crashes (or your RAID array)? You need to get your .BAK files to tape, or the network.
The other option is you use backup software that has a SQL agent that can backup databases that are in use
Tape space is cheap!
May 22, 2009 at 8:54 am
Personally, I never backup the data folder. Unless you shutdown the SQL server, the files are in use. As stated by David, there are some applications that allow you to backup open files, but I would still consider that risky. NetBackup has a SQL agent that does a "SQL" backup to tape. It is the corporate solution here and was in place before I arrived, one I've never agreed with because of how long it takes to restore (the tape(s) have to recatalog, etc.). I have the network guys restore a database fairly frequently so I can test that the "corporate solution" indeed works. Having said all that, I still do native backups to disk for quicker restores, and simply for my own sanity. Do your database backups to the backup folder, utilizing your strategy - whatever that may be, and simply sweep that folder to tape, ignoring the data folder.
Edit:
David (5/22/2009)
Tape space is cheap!
I'm sure that's why we're using it here! 😉
-- You can't be late until you show up.
May 22, 2009 at 8:59 am
Thanks for everyones help!
May 22, 2009 at 9:21 am
You know, I was never a fan of open file SQL agents, until in my last job the RAID array died (twice!) and the parity was corrupted (or so I was told) corrupting all data.
In 8 hours the Windows administrators were able to put the server back exactly as it was including the databases as of last backup! Better than installing SQL Server again, service packs and restoring all databases etc (assuming you decide not to invoke DR)
May 23, 2009 at 8:12 am
hi,
better not taking backups on the same disk where the server is , if it is on the other network place, it will be esier to refresh the backup, when the crashes happens suddenly, if you take on to the tape, check the backups regulary whether those are restoring properly.
Goodluck
KingManjunath
May 23, 2009 at 8:44 am
It makes zero sense to back up the data folder and backup folder. It's the same data, and arguably the data folder is useless since a backup not made through SQL Server APIs is not necessarily transactionally consistent.
Agents have been flaky for me, plus it's an extra DR step to get the agent software working. Use native tools or third party tools that can easily produce a native backup. Litespeed , SQL Backup, Hyperbac, all have a utility to produce a native backup.
Putting disk backups on the same disk as your data files is being negligent. They need to be on another disk in case you lose your disk or array.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 27, 2009 at 10:45 am
David (5/22/2009)
You know, I was never a fan of open file SQL agents, until in my last job the RAID array died (twice!) and the parity was corrupted (or so I was told) corrupting all data.In 8 hours the Windows administrators were able to put the server back exactly as it was including the databases as of last backup! Better than installing SQL Server again, service packs and restoring all databases etc (assuming you decide not to invoke DR)
bare metal restore option for Veritas?
i played with IDR back in early 2002 but it was flaky back then
May 27, 2009 at 10:49 am
tosscrosby (5/22/2009)
Personally, I never backup the data folder. Unless you shutdown the SQL server, the files are in use. As stated by David, there are some applications that allow you to backup open files, but I would still consider that risky. NetBackup has a SQL agent that does a "SQL" backup to tape. It is the corporate solution here and was in place before I arrived, one I've never agreed with because of how long it takes to restore (the tape(s) have to recatalog, etc.). I have the network guys restore a database fairly frequently so I can test that the "corporate solution" indeed works. Having said all that, I still do native backups to disk for quicker restores, and simply for my own sanity. Do your database backups to the backup folder, utilizing your strategy - whatever that may be, and simply sweep that folder to tape, ignoring the data folder.Edit:
David (5/22/2009)
Tape space is cheap!I'm sure that's why we're using it here! 😉
depends on the speed of your tape drives
i'm migrating everything over to our new LTO-4 library from DLT. the rated speed is 700GB per hour per drive and 1.6TB compressed data per tape. so far i have it at just under 300GB per hour on one NIC and one of my tapes holds 2.5 TB. I thought it was a mistake until someone from the netbackup forums said that tape makers advertise the lowest capacity and it's not uncommon to see better than advertised compression.
we moved a server and it took around 3 -4 hours to restore around 1.5TB of data to the new hardware.
May 27, 2009 at 11:36 am
A good question to ask your IT Director would be what type of recovery time is required for the database(s)?
I would agree with Steve though that backing up just the data folder is not going to give you a dependable recovery method. If the backup takes a snapshot of a log file while a transaction is writing to it you will most likely have a corrupt log file when you try to restore it. I have come across this using Symantec System Recovery with Backup Exec. It works in restoring the OS and SQL instance but could not restore the user database. I had to depend on the SQL backup I took of it.
You should consider how the application and users interact with the database and what type of recovery they require. If your backups are being taken when there is no activity on the databases, backing up the data folder might work for you, but then again it might not.
If you are doing full backups that backup file should be close to the same size as the database itself (mdf + ldf). So backing up the backup folder would be the same as backing up the data folder, and would give you a more dependable recovery method.
On a side note: SQL Server 2008 backup options give you the opportunity to do compression that works very well in consolidating space for your backups.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply