In this article we look at set of practical tips which can help you in backing up large SQL databases.
SQL Server databases in many organizations contain recent as well as data that is years old. Such databases tend to grow up into very large and heavy files, as over the years, the content only keeps on increasing. As the databases become heavier, the backing up process becomes lengthier and more time consuming. This is one of the biggest reasons along with chances of damaged sql which encourage companies to mandate policies of maintaining databases for limited periods. Although small databases are easier to manage, there might be instances where you may need information from an old database. To avoid being helpless in those situations, by not having old data, here are a few tips to help you easily backup large data files, so that you can store older databases for certain situations.
This is a technique of backing up large SQL databases by using multiple other small techniques, meaning this cannot be done in isolation, and requires you to perform multiple small operations, which are:
- Infrequent Backups – This might not sound as a wise advise, but keep your company policies in mind and backup as infrequently as the policies allow. Full backups do not need to be done regularly; you can do them once a month probably, and then opt for differential backups.
- Make Minimum Changes – You should avoid making lots of changes in the databases, the greater the number of changes you make, the more frequently you will have to back them up. Daily defragmentation of indexes or rebuilding jobs increases the size of differential backups, do it only when it’s a solution for a performance problem.
- Use data Compression – By compressing data you minimize the amount of data that needs to be written on disk, thus compressing backups and making them quicker.
Working around with SAN Snapshots
SAN Snapshots can backup large databases in seconds, but your backups will continue to be vulnerable even after they are on the storage area network. You can move out data from SAN by hooking a storage device to the same network and directly copying files from one to another. One drawback of using this feature is that the backups might take longer than conventional backups, and it might even be that this feature was not there in your licensing price, purchasing it now will be very costly.
Use Large Servers for Large Backups
You can have an idea about the potential size of your database at the time of installing SQL Server. If you believe your databases will go into terabytes, build a server that is strong enough to take the backups and that too quickly. Backing large databases need to be thought about when beginning to install the application that is going to hold these large databases. The speed of backups is more important than speed of queries, or you might create a server strong enough to provide quick results but slow while backing up databases.
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair mdb db and sql recovery software products. For more information visit www.datanumen.com