alternatives to SQL Backup
-
Hi, all
I have a SQL Server 7.0 in a server with little free space. Some time ago, we did SQL backups to disk (in a compressed folder) and then the dump file was included in an ArcServe backup (2 copies of it).
Recently, we found in the worst way that we couldn't restore that database backup !! It seemed to require more space to decompress the file when I ran the RESTORE command.
. So, now we are running SQL Backups directly to a tape (weird, but actually they are faster than to disk). The problem is that we are using 4 tapes each day (2 x ArcServe + 2 x SQL) since we cannot use the same tape, can we ? This is more expensive and takes more time.
So they want me to change it. If I had to choose, I would say "buy another disk"
but thay want me to consider these alternatives:- Would you recommend using an agent for ArcServer in order to include the database backup on the same tape ? I've been reading some post and it doesn't seem a good idea.
- It is possible to use a cold-backup strategy (copy the datafiles with SQL server down, of course) ? Is there any good reason for NOT to use it ? Could I use that to restore a database in another server ? (I think not)
I would appreciate any advice.
-
I can't say that Arcserv hasn't got the bugs worked out of their SQL agent, but we were using it on a large database, and discovered in a disaster recovery test, that we couldn't restore the backup. We haven't used Arcserv's SQL agent since.
As to cold backups, we've done that too. But you have to bring down SQL to do it. In most cases, nothing is gained. If its all I could do, then yeah, I'd do it. Yes, you can attach the database files on a different server. Be aware, that you will have to fix the database users. You have to do that with SQL backups too, though.
I agree with you, more disk space seems imperative. But you might also look at Peter Yeoh's MiniSQLBackup or SQL Litespeed. I haven't actually tested MiniSQLBackup, but I have tested Litespeed, and I'm very impressed. Backup sizes can be reduced by as much as 90%, depending on the database size. Backup time is reduced as well.
Steve
-
In our backup stratergy, we backup to disk, and allow the tape backup utility to backup the disk backups. I wouldn't recommend ever getting SQL Server to backup directly to tape, mainly because we like to have our systems up as much as possible. (Have you seen SQL Server halt and prompt for a tape yet?).
Backing up directly tape is probably faster if your disk backups were being written to the same disk/raid set as the database files are on. The drives are probably being thrashed trying to read and write at the same time. If you backup to a different drive/raid set you should see that the speed is close to / better than tape.
I've been testing out SQL Litespeed vs MiniSQLBackup for the last week. In a number of our large databases they both seem as capable to increase the speed and reduce the size of backups compared to SQL Server native backups. (MiniSQLBackup however requires you to backup to two or more files to get the same threading and speed as SQL Litespeed). I would recommend having a look at them both, but I've got to say that I find MiniSQLBackup's pricing to be a lot more reasonable.
But mpererias, I would always recommend testing and testing and testing out any backup routine you plan to implement. Test it before doing it. Test you can restore from the backup. Test it a few days/weeks/months later, and continue to keep testing it regularly. Thats the only way you'll know that you can restore when you need to.
Julian Kuiters
juliankuiters.id.au -
Thank you both for your comments !
I wouldn't recommend ever getting SQL Server to backup directly to tape, mainly because we like to have our systems up as much as possible. (Have you seen SQL Server halt and prompt for a tape yet?).
I've never have a problem like that when I do the backups directly to tape (the backup is made manually). I agree that it would be safer using the disk, but is the backup to tape really too risky ?
-
Thank you both for your comments !
I wouldn't recommend ever getting SQL Server to backup directly to tape, mainly because we like to have our systems up as much as possible. (Have you seen SQL Server halt and prompt for a tape yet?).
I've never have a problem like that when I do the backups directly to tape (the backup is made manually). I agree that it would be safer using the disk, but is the backup to tape really too risky ?
-
I always backup to disk and keep 2 days on disk, that way Im covered if the nightly disk to tape job fails. Belt and suspenders approach. Its also practical, usually if I need to a restore its from the day before, faster if I have it on disk.
-
I back up to disk as well (and have been since v4.21). Usually, 3 days full backups on line and 2 days of transaction logs online as a minimum rule (5 days full and 3 transaction are the max). This way if backups to tape fail one evening, the data is still there for the next day for an incremental recovery should it be needed. As for dumping to disk and speed, have you tried striping your backups ? Yes, it may mean that you will have to code your own SQL or proc and not use the GUI interface for SQL Maintanance Plans. I've had good results (60%-70% less time) using 4-6 stripes and backing up to different arrays/disks on a v7.0 server (however it will take some benchmarking to determine the optimal mix for your particular site). Also backup to another array or disk other than where the database data resides improves performance as well. All in all it's back to basics, how the server is set up.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I wouldn't recommend ever getting SQL Server to backup directly to tape, mainly because we like to have our systems up as much as possible. (Have you seen SQL Server halt and prompt for a tape yet?).
Many years ago in a previous company I encountered a problem with a suppliers website. They were supplying part of our websites content and they backed up from SQL directly to tape. One day they finally tripped over the limit for their tape backups, and started requiring two tapes to a backup. SQL Server then (may not be the case now) would stop all transactions and send out a notice to new connections to insert the next tape. This not only looked really bad when it appeared on the website, but ceased business until they got the next tape in the drive.
I guess also I still think of tape as a somewhat unreliable storage medium. Even though tape quality is constantly improving, there is always that chance that in an extreme emergency the tape may be corrupted, for whatever reason. If you have a backup on disk, then you have at least a 2nd level of protection.
Julian Kuiters
juliankuiters.id.au -
Backing up to tape isn't ever risky. Restoring from tape is. And when it's risky, so is your reputation, job, etc.
I store one day on disk, the rest on tape. We used to (in larger environments) have 2 copies of each tape. One offsite, one on. Might be overkill, but I never had issues restoring things. Might have issues on tape, or might not be on disk, but the chances of 2 tapes being bad were low.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply