A well-designed backup and restore strategy balances the business requirements for maximum data availability and minimum data loss, while considering the cost of maintaining and storing backups. A backup and restore strategy contains a backup portion and a restore portion. The backup part of the strategy defines the type and frequency of backups, the nature, and speed of the hardware that is required for them, how backups are to be tested, and where and how backup media is to be stored including security considerations.
The restore part of the strategy defines who is responsible for performing restores, how restores should be performed to meet your goals for database availability and minimizing data loss, and how restores are tested.
Designing an effective backup and restore strategy requires careful planning, implementation, and testing. Testing is required: you do not have a backup strategy until you have successfully restored backups in all the combinations that are included in your restore strategy and have tested the restored database for physical consistency.
You must consider a variety of factors. These include:. The goals of your organization regarding your production databases, especially the requirements for availability and protection of data from loss or damage. The nature of each database: its size, its usage patterns, the nature of its content, the requirements for its data, and so on. Constraints on resources, such as: hardware, personnel, space for storing backup media, the physical security of the stored media, and so on.
Ensure that you place your database backups on a separate physical location or device from the database files. When your physical drive that stores your databases malfunctions or crashes, recoverability depends on the ability to access the separate drive or remote device that stored the backups in order to perform a restore. Keep in mind that you could create several logical volumes or partitions from a same physical disk drive.
Carefully study the disk partition and logical volume layouts before choosing a storage location for the backups. Backup and restore operations occur within the context of a recovery model.
A recovery model is a database property that controls how the transaction log is managed. Thus, the recovery model of a database determines what types of backups and restore scenarios are supported for the database, and what the size of the transaction log backups would be.
Typically, a database uses either the simple recovery model or the full recovery model. The full recovery model can be augmented by switching to the bulk-logged recovery model before bulk operations. The best choice of recovery model for the database depends on your business requirements. To avoid transaction log management and simplify backup and restore, use the simple recovery model.
To minimize work-loss exposure, at the cost of administrative overhead, use the full recovery model. To minimize impact on log size during bulk-logged operations while at the same time allowing for recoverability of those operations, use bulk-logged recovery model. After you have selected a recovery model that meets your business requirements for a specific database, you have to plan and implement a corresponding backup strategy.
The optimal backup strategy depends on a variety of factors, of which the following are especially significant:. If there is a predictable off-peak period, we recommend that you schedule full database backups for that period. Under the simple recovery model, consider scheduling differential backups between full database backups.
A differential backup captures only the changes since the last full database backup. The directory structure or drive mapping used by the database on the original computer not exist on the other computer. For example, perhaps the backup contains a file that would be restored to drive E by default, but the destination computer lacks a drive E.
You are reusing a database name that exists on the restore destination and any of its files is named the same as a database file in the backup set, one of the following occurs:. If the existing database file can be overwritten, it will be overwritten this would not affect a file that belongs to a different database name.
To avoid errors and unpleasant consequences, before the restore operation, you can use the backupfile history table to find out the database and log files in the backup you plan to restore. If the files within the database backup cannot be restored onto the destination computer, it is necessary to move the files to a new location while they are being restored. For example:. You want to restore a database from backups created in the default location of the earlier version.
It may be necessary to restore some of the database files in the backup to a different drive because of capacity considerations. This is a common occurrence because most computers within an organization do not have the same number and size of disk drives or identical software configurations. It may be necessary to create a copy of an existing database on the same computer for testing purposes. In this case, the database files for the original database already exist, so different file names must be specified when the database copy is created during the restore operation.
For more information, see "To restore files and filegroups to a new location," later in this topic. The name of the database can be changed as it is restored to the destination computer, without having to restore the database first and then change the name manually.
For example, it may be necessary to change the database name from Sales to SalesCopy to indicate that this is a copy of a database. The database name explicitly supplied when you restore a database is used automatically as the new database name. Because the database name does not already exist, a new one is created by using the files in the backup.
If we do not run it following our backup step, such as running our backups in SQL Server Job Agent, but running our Zip and Copy in Task Scheduler, we will want to make sure the time window covers a possible backup that runs longer than expected, such as using a time window of up to 23 hours.
While this tip covers ways in which we can have multiple copies of compressed backups, we want to still follow strong backup best practices in T-SQL by always checking integrity on our database and validating our backup file after the backup.
Our environment may require other options specified during the backup process such as encrypting backups, compressing them with T-SQL, adding checksums, etc. You should always follow the best practices in relationship to your environment, which takes into account the version of the tool s you use.
This inherently means somethings will and will not apply to you. Since backup compression has been built into SQL Server since the version, it is difficult to understand why someone would still be using some other compression. In fact, long ago we used to use a solution like this when we were running SQL , but we changed when we moved to Aside from simplifying the compression step, the backups and restores - for those who regularly do that to a test environment go much faster.
However, in my experience the compression provided by SQL Server is not very good at all. I dont think so, you will save much space after compressing when you already take the backup with compression. CreationTime -gt Get-Date. FileSystem [System. Related Articles. Backup to multiple files for faster and smaller SQL Server files.
Simple script to backup all SQL Server databases. Using passwords with SQL Server database backup files. Changing the default SQL Server backup folder.
0コメント