January 5, 2012 at 9:23 am
That brings up an interesting question -- how many DBAs have their backups go straight to tape? In my professional career I have never had a backup infrastructure where the SQL Server was backing up straight to tape; we always do backup to disk first and then a subsequent backup to tape. That way the backups are locally available for other purposes. Isn't the idea of setting up a backup process to primarily center around tape options a little bit archaic in itself? I mean, you want to keep the tape options in the system, but I would think that file-based options would be more in line with the typical default structures. And to use a "media set" to drive backup issues assumes that you are using tape, IMO.
January 5, 2012 at 9:37 am
Got it wrong because I figured there wasn't a good reason why you couldn't have both compressed and uncompressed in same set. Learned something new today, I guess.
January 5, 2012 at 9:49 am
Interesting. I wouldn't think it would matter since all editions can restore compressed backups, but apparently the header stores this.
Since I use separate media sets for all backups, not an issue for me.
January 5, 2012 at 9:51 am
jeff.mason (1/5/2012)
That brings up an interesting question -- how many DBAs have their backups go straight to tape? In my professional career I have never had a backup infrastructure where the SQL Server was backing up straight to tape; we always do backup to disk first and then a subsequent backup to tape. That way the backups are locally available for other purposes. Isn't the idea of setting up a backup process to primarily center around tape options a little bit archaic in itself? I mean, you want to keep the tape options in the system, but I would think that file-based options would be more in line with the typical default structures. And to use a "media set" to drive backup issues assumes that you are using tape, IMO.
It's a scale issue. Ten years ago backing up a 2TB db would be expensive on disk, so you went to tape if you wanted regular backups.
Now, not so sure there's a direct to tape reason.
January 5, 2012 at 10:33 am
sknox (1/5/2012)My guess is that it was inherited from tape systems, as the compression option for tape was historically implemented in firmware because back in the day general purpose CPUs weren't fast enough or were too busy doing other tasks to compress the data as well.
A reasonable guess. I don't have a business reason for knowing. Just curiosity. Thanks.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
January 5, 2012 at 10:51 am
Steve Jones - SSC Editor (1/5/2012)
jeff.mason (1/5/2012)
That brings up an interesting question -- how many DBAs have their backups go straight to tape? In my professional career I have never had a backup infrastructure where the SQL Server was backing up straight to tape; we always do backup to disk first and then a subsequent backup to tape. That way the backups are locally available for other purposes. Isn't the idea of setting up a backup process to primarily center around tape options a little bit archaic in itself? I mean, you want to keep the tape options in the system, but I would think that file-based options would be more in line with the typical default structures. And to use a "media set" to drive backup issues assumes that you are using tape, IMO.It's a scale issue. Ten years ago backing up a 2TB db would be expensive on disk, so you went to tape if you wanted regular backups. Now, not so sure there's a direct to tape reason.
It's been a few years since I have seen direct-to-tape, but one memorable place had a very flash tape jukebox thing that had better write performance than the (also pretty reasonable) EMC SAN.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 5, 2012 at 11:13 am
Steve Jones - SSC Editor (1/5/2012)
jeff.mason (1/5/2012)
That brings up an interesting question -- how many DBAs have their backups go straight to tape? In my professional career I have never had a backup infrastructure where the SQL Server was backing up straight to tape; we always do backup to disk first and then a subsequent backup to tape. That way the backups are locally available for other purposes. Isn't the idea of setting up a backup process to primarily center around tape options a little bit archaic in itself? I mean, you want to keep the tape options in the system, but I would think that file-based options would be more in line with the typical default structures. And to use a "media set" to drive backup issues assumes that you are using tape, IMO.It's a scale issue. Ten years ago backing up a 2TB db would be expensive on disk, so you went to tape if you wanted regular backups.
Now, not so sure there's a direct to tape reason.
Yes, I never did a 1-2TB database until my current job, and they are all on SQL 2008 and compress to about 300-600 GB, and backup disk space is cheap. Before this job, I never managed a DB more than 250 GB. So I could see scale as an issue before, but the database would have to be huge for that to still matter today.
January 5, 2012 at 11:37 am
Steve Jones - SSC Editor (1/5/2012)
jeff.mason (1/5/2012)
That brings up an interesting question -- how many DBAs have their backups go straight to tape? In my professional career I have never had a backup infrastructure where the SQL Server was backing up straight to tape; we always do backup to disk first and then a subsequent backup to tape. That way the backups are locally available for other purposes. Isn't the idea of setting up a backup process to primarily center around tape options a little bit archaic in itself? I mean, you want to keep the tape options in the system, but I would think that file-based options would be more in line with the typical default structures. And to use a "media set" to drive backup issues assumes that you are using tape, IMO.It's a scale issue. Ten years ago backing up a 2TB db would be expensive on disk, so you went to tape if you wanted regular backups.
Now, not so sure there's a direct to tape reason.
If you cannot do network backups, you can use tape as a medium that can be reasonably protected against fire. One of my past clients does tape backups. When the backup is finished, the drive spits out the tape on a stainless steel slide that goes through the wall into the adjacent room. Tape drops into a canister that is certified to withstand fire for two hours.
January 5, 2012 at 3:11 pm
tks for the question - not something I have direct experience with but remember reading/hearing that along the way - cheers
January 6, 2012 at 10:24 am
Nice question. Thanks.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
January 6, 2012 at 2:33 pm
Thank you for the question.
A related question of you allow me (I didn't want to create a topic):
What are the main advantages of using a backup compression over using a normal backup file with 7zip or WinRar? I ask this because my company uses the latter.
Or is it just the problem of having to first decompress a huge backup file somewhere before having it restored? Has anyone seen any benefits of using native compression?
I wonder how widely used this somewhat new feature is.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
January 7, 2012 at 3:23 am
codebyo (1/6/2012)
What are the main advantages of using a backup compression over using a normal backup file with 7zip or WinRar?
Native backup compression is a single-step process; normal backup followed by compressing with a 3rd party tool is a two-step process. Advantages for native compression are:
1. Backups will probably be faster. Speed of backup is usually I/O bound; compressed backup needs less writes, so should finish faster. How much? Depends - test it!
2. It's easier, since it's a single step process. Just backup and restore as normal, no second step after backup required, no extra step before restore required.
3. Uses less disk space. The two-step process requires the disk space for the full uncompressed backup PLUS the disk space for the compressed version (while compressing and while decompressing before a restore).
4. Avoid dependency on an extra vendor (plus extra licensing cost).
Advantage of the normal compression followed by seperate compression step are:
1. Dedicated compression tool might achieve better compression ratio (I did not test this, hence the word "might").
2. Backup compression requires at least Standard Edition (for SQL Server 2008 R2). If you only need Workgroup or Express Edition otherwise, the price of a licence for a third party compression tool is probably lower than the price of upgrading to Standard Edition.
January 7, 2012 at 10:02 am
Thank you, Hugo. I'll keep that all in mind and I will do some tests in my development server.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
January 24, 2012 at 2:27 am
Thanks for the question.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 31, 2012 at 8:31 am
"Can a compressed backup and sn uncompressed backup of a particular database co-exist in a single media set? "
What is an "sn uncompressed backup"?
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply