A thought on what is the best way to take quick backup of a big database?

There is no direct answer to this and this is a debated issue. For a very big database’s backup strategy one of the very basic factors is the way how backup strategy is designed. I am considering discussing the backup strategy is out of scope in this post.

Assuming you have a good strategy, the question stands is “which technology is to use for backing up the databases?” While native SQL backup is very good, one of the major bottleneck I found (till SQL 2005) is the time and space consumed by the SQL Native backups. For a very large database, this can really make a difference.

So as per me, best option is using some third party tool to take backup of big database if time and space taken by native technology is unacceptable. Perhaps the most preferred technology used in industry is Litespeed. Litespeed surely saves huge space and time for backup and it offers a good compression ratio. However it cost money. So I recommend to first deciding if Litespeed is really needed for the database. If there is good business reason to spend money, then using Litespeed (or another third party tool like Redgate etc) is a very good option for backing up big database.

On the other hand, native backup of SQL Server is also a good option. Native backup will take almost equal space of data file (It does not support compression) which will take good space and time. However, the best advantage is to eliminate dependency on third party tool licenses for this approach.

Best news from Microsoft is from SQL 2008 native backup started supporting compression. So probably sometime in future we all can eliminate spending money on any third party tool and can always conclude that using native backup technology is always best.

Comments