A functioning SQL Server application is actually a collection of databases. In addition to the data itself, it includes the transaction log and the system databases. All of this must be protected if the application is to be restored smoothly. The following are some SQL Server backup and recovery tips and best practices.
Unlike many programs, SQL Server allows backups while users are active and transactions are being processed. This means you can back up while the system is in use. But since a SQL Server backup sucks up resources, especially I/O, it is best to perform full backups during periods when the system is lightly loaded.
Shorten your data backup period
If overall performance is suffering due to long backup times, there are several things you can do to shorten the backup period. One way to shorten backups is to use backup data compression. Another way to shorten the backup time is to back up your database to disk. However, if you back up to disk, do not back up to the same disk used to store the database or the transaction log. That not only impacts performance, it can affect recoverability if the disk fails.
Mix and-match backup methods
SQL Server offers three basic backup methods: Full, differential and transaction. These are backup options built into SQL Server and you don't need a separate backup app. A full backup backs up everything. It is the most complete, takes the longest and uses the most resources. A differential backup only backs up what has changed since the last full backup. This makes for faster backups, but slower restores since the dat...
To continue reading for free, register below or login
To read more you must become a member of SearchDataBackup.com
abase must be reconstructed. A transaction log backup simply backs up the transaction log since the last transition log backup. It is quite fast, but reconstructing the database from a chain of transaction log backups is the slowest method of recovery.
In addition to these three methods of backing up the entire database, SQL Server also allows you to back up files or file groups individually. This can be useful for protecting important files or for backing up very large databases.
Choosing the backup method that's best for your company depends on the nature of the database being protected, specifically how frequently it changes, how large it is and how important it is to the enterprise. Some databases are not too large and change relatively infrequently. They can be backed up with full backups done on a daily or even weekly schedule. Others, especially transactional databases that are mission critical, should receive full backups as often as feasible
Back up the transaction log frequently
Next to the database itself, the transaction log is the most critical data in a SQL Server database. The transaction log covers all activity and can be used to do point-in-time (PIT) restorations. The advantage of the transaction log is that it can be backed up frequently, keeping it very up-to-date. It also has PIT or near-PIT backups between more conventional backups. Note that a transactional backup only backs up to the last transactional backup. That can mean having to restore a chain of transactional backups to get a full restore. The power of a transaction log backup comes in its ability to restore up to the minute, or nearly so.
The transaction log should be backed up several times a day. Many organizations back it up every 10 minutes or so if they have an active database such as a transactional database.
Backing up SQL Server system databases
The other vital component of a SQL Server application is the system databases, including msdb and master. These contain vital information like system configuration and are needed in the event of a complete restore. These change less frequently and should be backed up at least weekly, or daily in an active (where data transactions occur frequently) environment. In the case of the master database, backups should be made at least whenever there is a change in server or database level configuration settings or any change in the details of logon accounts. You you can back up the system databases while the application is running.
Back up the system partition at least with every configuration change
The system partition isn't technically part of SQL Server, but un-backed up system partition can render a database difficult to recover. It's important to keep an up-to-date backup of the system partition, which means backing up at least every change in the system configuration; regular backups are even better.
Practice database security
Make sure your database is properly secured. For example, when using the file system for backups, you should only grant file and folder access to the SQL service account and the DBA.
Don't use the password option in the backup statement to password-protect the backup. It is deprecated and slated for removal from future versions of SQL Server.
Use the checksum option of the Backup command when making backups and test your backups regularly using the Restore Verifyonly command.
Finally, make sure your security patches are up-to-date and properly installed -- not just on SQL server, but on the underlying operating system and hardware.
About this author: Rick Cook specializes in writing about issues related to storage and storage management.
