Optimize SQL Server backup and restore methods natively with these best practices. We've tallied our top seven most viewed tips of 2007 on the topic of backup and restore. Whether you're looking for commands to a speedier restore, steps to take before deploying database mirroring or an overview of disaster recovery features in SQL Server 2005, you'll find it here. If there's anything you'd like to see covered on this topic in 2008, let us know.
#1 -SQL Server backup and restore commands to limit downtime
Whatever SQL Server backup and restore solution you choose, you must be sure to back up your data in an acceptable amount of time and restore it in an acceptable amount of time. The last thing you want to do is tell your customers, "We are in the middle of a two-terabyte database restore. We will be online again in 16 hours." SQL Server MVP Hilary Cotter shares some best practices for using backup and restore commands.
#2 -Mirrored backup and restore commands in SQL Server 2005
Mirrored backups in SQL Server provide an extra layer of data protection. Learn how mirrored backup commands work with media sets ("media family") to minimize data loss. You'll also see how to restore a missing mirrored backup set from a different media family.
#3 -Best practices for SQL Server backup maintenance
Improve SQL Server performance by removing backup history from the msdb database and reducing its size. Database architect Denny Cherry shares some backup commands for optimal SQL Server maintenance.
#4 -Database mirroring factors to consider before setup
Before setting up database mirroring in SQL Server 2005, consider a few pre-deployment factors. They range from the service pack and edition of SQL Server 2005, to environmental factors like your network bandwidth, the type of workload and whether you should use a witness or not. Here are some database and server needs along with network requirements that will guide you toward optimum SQL Server availability.
#5 -SQL Server filegroups for backup and restore
SQL Server has long given database administrators the option of splitting up databases into different files and filegroups. When Microsoft introduced partitioning in SQL Server 2005, it expanded the options for using filegroups. Additionally, you can now do online database restores with the SQL Server 2005 engine. So with all these options available, how should you go about optimizing your filegroups for backup and restore? Here's a look at filegroups and how to set up a back up and restore strategy when they are in use.
#6 -Disaster recovery features in SQL Server 2005
Database administrators must have a reliable backup and recovery plan for their SQL Server databases. After five years in development, see how SQL Server 2005 provides greater backup and recovery options. In this tip, you'll get an overview of features including mirrored backup media sets, log shipping, replication and database mirroring.
#7 -SQL Server bulk-logged recovery
Minimal space requirements for transaction logs and best performance for bulk operations are among the advantages to the bulk-logged recovery model in SQL Server. However, one disadvantage is that if there is a bulk-logged transaction in your backup file, you cannot do a point-in-time recovery. In this tip, Edgewood Solutions' Greg Robidoux gives you the scoop on the pros and cons of bulk-logged recoveries in SQL Server.
