32. Backup SQL Server
Backup terminology
system error
SQL Server record file
Backup method
Execute backup
Schedule backup
Improve backup
Summary of this chapter
Backing up the database is one of the most important tasks of DBA. Backup and careful restore programs can restore data when system failure. DBA's responsibility is to keep the system's execution status and restore all services as soon as possible. The system stopped operations will cause many inconveniences, sometimes more expensive, so it has achieved the acquisition database backup in the system to stop operation. Some technologies will help the system failure, such as clustering and fault-tolerant disambarate, but it is more than a good plan and reliable backup.
Because the theme of backup, restore, and restore the database is important, we will be discussed in two chapters. This chapter will learn SQL Server transaction record files, and several ways to back up the database. In Chapter 33, learn how to restore the database, how SQL Server replies, and how to establish a reply plan.
Backup terminology allows us to review some terms before starting to discuss backup technology. In this chapter, you will learn instances on backup, restore, and reply. Backup and Restore Backup (Backup) and Recover operations complement each other, that is, saving the data in the database to be used later, the backup and restore jobs performed by the similar job system. During the backup process, the information is copied from the database and saves to another location. The operation system backup and database backup is different. The difference is that the job system backup can save individual files, and the database backup saves the entire database. Usually the database is available for many users, but many job system files belong to each user. Therefore, the database backup simultaneously backs up all the information of all users. Since SQL Server sets the maximum value of the usage time, the backup process will be executed regardless of whether the user is being accessed. During the restore process, the backup data is copied back to the database. This is two different operations without confusing restore and reply (reply). Unlike the backup process, the restore process cannot be performed when SQL Server is being executed. In addition, the data sheet cannot be restored individually. If a user lost some of the data in the database, it is difficult to restore, because the restore operation will restore the entire database or some information, and the data from all data from the database is quite difficult. Reply reply (recovery) The ability to rescue the system failure of the system failure is the ability to re-(reply) transactions. Each time you change the database, SQL Server will not immediately write to the disk. If the system is immediately written to the disk each time, a large system (such as the system in the bank) may work fairly, because it is necessary to wait for each transaction to complete the write. This can cause the transaction delay.
Since there is a delay when the change is written to the disk, the system may make the database in a loss, and some database changes may not be written on the disk, or the change written to the disk may not is recognized. In order to maintain the integrity of the system, SQL Server records all changes in the transaction record file. (The transaction record file will be discussed in detail in the "transaction record file" section of this chapter.) After the system fails, restart SQL Server, which uses the transaction record file to make all the recognized but not written to the discharge, and return to failure There is no approved transaction. In this way, the correctness of the data can be guaranteed.
During the reply, SQL Server must prepare multiple types of transactions, including the following:
The transaction only inquiry does not need to reply. SQL Server reads the data page from the disk, and re-use changes from the disk, SQL Server reads the data page from the disk, and then use changes to the disk. The transaction of the data in the database has changed, and the SQL Server determines that the change has been written to the disk on the reply, and does not require other interventions. The transaction of the data in the database has been changed, and the SQL Server will use the transaction record file in the reply, restore the data library to the status of the database to the transaction execution. When SQL Server is restarted from the system failure, the reply mechanism will be turned on automatically. Reply mechanism Use the transaction record file to determine which transactions need to reply and which does not need to reply. Many transactions do not need to reply, but SQL Server must still read the transaction record file to make a decision. SQL Server starts reading transaction record files from the final checkpoint. (The checkpoint will be discussed behind this chapter.)
Since the transaction record file is important for the returns of the transaction, it is usually saved in the RAID-1 (mirror) disk area. (RAID was discussed in Chapter 5.)
In the event of a system failure, you need to use a backup file restore database (such as damage to a disk), the transaction record file and the transaction record file backup are used to reply the database to the fault point. Therefore, restore and reply operations are usually common jobs. When a power failure occurs, you may only need to reply.
Description
Transactions by S QL Server Rehabilitation is equal to the ROLLBACK instructions. This transaction will be canceled, and all the information will be restored to its original state. When a transaction is re-performed, the data that has been recorded in the database but not written to the disk will be reset, so the data file will return to the status of the fault time. That is to say, re-enabling the status of the database to the time point of the fault, reacting the recognized transaction, eliminating all unidentified transactions.
System failure If you are using a Microsoft Cluster service, or a magnetometer RAID fault tolerance, you may be suspected whether you really need to back up, the answer is "need". Because the system's faulty mode is not the same, fault tolerance and error reply may only correct some cases. In this section, we will discuss some potential failure reasons and how to avoid malfunctions. Some system failures may not be serious, while others may be very serious. To learn about the importance of backup, you have to learn about three main fault types: hard body failure, soft faults, and human errors. Hard fault hardware failures may be the most common type of trouble. Since the current computer hardware is more stable than before, the frequency of this type of fault is slowly reduced, but after a period of use, the wear of the component is inevitable. Typical hard body faults include the following: CPU, memory or bus bar failure These faults usually cause system damage. After replacing the faulty component and restart the system, SQL Server will automatically perform the database reply. The database itself is complete, so it is not necessary to restore, and only the lost transaction is required. Disk failure If the RAID fault tolerance is used, this type of fault may not affect the status of the database, so it is only necessary to fix the RAID array. If there is no RAID fault tolerance, or the RAID array has failed, the only choice is to restore the database from the backup repository, and then use the transaction file backup reply library. Systemal faults or permanent server losses If the system is damaged in fire or disaster, all systems will be reset. Hardware needs to be re-assembled, the database needs to be restored from the backup, then the reply repository is backed up using the data and transaction record files. Software faults are not common, but the mollusk fault is usually more serious than the hard body failure. Since most of the software has built a function that minimizes the hard body failure, the hard body has lost this protection, so that the system is more susceptible to the impact of the fault. The transaction record file is an example with such a software function to help reply from a hard body failure. Typical software faults include the following: Working system failure If the fault occurs in the I / O subsystem, the data on the disk will be destroyed. If the database is not destroyed, you only need to reply to the information. Of course, if the database is destroyed, the only choice is to restore the database through backup. RDBMS failure SQL Server itself may fail. If this failure causes damage, then the database restore and reply will be made through the backup. If there is no damage, you can return the system to the failure using automatic reply. Application fault applications may also be faulty, resulting in data damage. If this type of failure is damaged, it also needs to be restored by backup. Conversely, there is no need to restore, and the automatic reply will cause the system to return to the fault. You may also need to make a taller to the application manufacturer to avoid failures. Note Some companies will use SQL Server's beta. In fact, Beta is just used to evaluate and test, and cannot be used in a formal execution environment. Sometimes the beta version contains software errors, including the functionality that has not been tested completely. You should use the product version of Microsoft SQL Server 2000, which is fully tested, and is available for official environments.
The third important error type for human error is human error. Artificial errors may occur inadvertently at any time. The damage caused by artificial errors can be light, trouble is that this type of error is easily ignored for a few days or even a few weeks, making it more difficult to reply. And the user build a good interaction (including a good communication pipe), you can quickly reply to the error caused by the user. Users will not be afraid to immediately report errors, and of course, the sooner, the better the error. The following faults may be caused by human error: the data library server loss artificial error can cause the server fault to fail to turn off the power, do not turn off the SQL Server to turn off the server. When the SQL Server is restarted, the reply will be automatically executed, of course, this is to spend some time. However, since the database on the disk is not injured, it is not necessary to restore. Lost information may be caused by accidentally deleting data files. Restore and reply operations must be performed to return the database to the status of the fault point. If the information sheet is lost or broken, if you accidentally delete the information sheet, or because some factors are misuse data, you can use the backup and return the data sheet to its original state. Since the lost single-information sheet or a group of information cannot be restored from the backup, the reply of this type of fault may be quite complicated. We will discuss examples of responses to this type of failure in Chapter 33. SQL Server record files To learn how backup and restore operations are working with the database to reply to the common job, you must first understand the mode of operation of the SQL Server record file. This section will provide an overview of SQL Server records and checkpoints and will tell you how to back up your transaction record file. Transaction log transaction logs are used to record all transactions and these transactions make changes to the database. This record allows the reply to be done. When a transaction is recognized, just the recognition record has not been written to the transaction record file, the approved operation is not completed. Since changes in the database do not need to be written immediately, this record file is just a method of replying to the transaction in a system failure event.
Note If a data file must be reduced by the backup, all transactions that have occurred in this data file must be revoked once, in order to reply the database to the fault. Since the transaction record file is the key to this process, it is necessary to perform a transaction record backup. You must save all transaction record materials from the last backup to now, so you can reply the database. If you are only interested in the restore of the previous backup, you can skip the transaction record backup, but the current transaction record file will not respond to the transaction that occurred in the backup.
The change in delay writer is performed on the database is the data in the SQL Server Express Area first. The information in the changing the fast-tap is mainly for improvement efficiency, because waiting for I / O operations to be quite time. These changes will eventually write a disk, but this process is executed on the background, while the user doesn't know. Since the modified page is stored in the quick-strobe area, it will cost a significant amount before writing a disk by SQL Server, which is written to the disk (called Dirty Patch). This execution is called lazywriter thread. The delay writer is used to use the recently used page list, which means that the least use of the least use will be placed in the list of writes to the disk, but just The data used is placed in the list, and it is not least written in the disc. Those pages that are often modified (so always constantly moving to the end) may never be written in the disc. This will increase the reply time because many transaction records may be read to apply all changes to the information. For example, in a large-scale system with more than 1 GB of RAM, there is a lot of changes to the database, and the reply process will take several hours. In addition to delay writing, checkpoints are written to write the Dirty page into the disk. (We will discuss checkpoints in detail in this section.) Sequence Record Because transaction record files are history of transactions, the I / O operation of transaction record files is mainly written, and generally in time order. In the transaction restoration event, the transaction record file will be read, and the time order of I / O will be destroyed. Since the recovery is quite rare (because the system crashes are rare, and the user does not recover the transaction), the I / O mode in the transaction record file is quite stable. You can enhance the transaction record in its own disk or RAID array to enhance I / O performance, just as discussed in Chapters 4 and 5. You should use the RAID to protect transaction record files because these transaction records are important for database replies. The transaction record file size may grow significantly based on the number of changes to the database. Because the transaction record file is a limited set of files, it will eventually be filled, so it must be reduced regularly. When completing the record file backup, the record file will be automatically deleted, which will be discussed later in this chapter. Note If you do not have a backup file file, you can still delete this record file, just set the database's database option Trunc.log on chkpt to true. However, you cannot back up your transaction record files later. This setting will make the database cannot be replied, so it is not recommended.
Using the transaction record file Reply In a system failure event without damage in the database file, the current transaction record file can be used to reply the database because it only needs to reply to the transaction that has not been written to the disk. The number of pages that must be replied is depends on the number of DIRTY PAGE in the database, determined by the checkpoint interval. The checkpoint will write the Dirty Page into the disk and reduce the time to perform the reply. The checkpoint and checkpoint interval will be discussed in detail in the
SET Recovery Bulk_logged
ALTER DATABASE Northwind
Set Recovery Full
ALTER DATABASE Northwind
Set Recovery Simple
When using Bulked_Logged Reply mode, a large number of operations raised by this chapter will not have records (there will be some exception scenarios will be described later), while other operations still have records. If you select Full Reply mode, then all the operations will be recorded. When performing SIMPLE replies mode, the information will only be replied to the last backup.
Note Because the reply mode defines the fault argument of your system, it must be cautious when using Bulk_logged reply mode. If you use this mode, you can increase the performance of a lot of operation, but the failure event will increase the reply time.
Select INTO SELECT INTO statements are used to create new information tables in the database. Because Select INTO statements cannot be used to select information in existing objects, it cannot be used to update the information, which can only be used to establish information. The process of establishing the information is often repeatedly executed, so the SELECT INTO statement is suitable for performing a non-recording job. A large number of replications and BCPs must meet the following requirements in order to make a large number of replication and BCP operations can be used to perform non-recording operations: Database Option Select Into / Bulkcopy must be set in True. When a large amount of data is copied, the target data sheet must be empty to make an index. You cannot copy the target data sheet because the items needed to copy data when copying the transaction record files during transaction replication. The TabLock prompt must be specified to force the lock information table. These restrictions make a large number of replication operations saving the space of the transaction record file, but also make the execution speed become fast. However, when the database must be restored from the backup, these non-recorded jobs have to be restarted. CREATE INDEX CREATE INDEX statement makes it easy to perform a non-record job because the index can be re-established as needed. Reconstruction index is not difficult. However, if the data sheet is too large, the process may spend a time, and occupy resources. Text operation text operations can be used to perform non-record jobs that WRITETEXT and UPDATETEXT are written. To make these operations do not leave a record, you can only use the bulk_logged reply option just mentioned. Checkpoint is actions used to synchronize the status of the entity profile and the database to reduce the necessary reply time when the system fault event occurs. The time required to reply to the database depends on the number of time the time since the last checkpoint and the number of DIRTY PAGE in the buffer snapspace. Therefore, reducing the interval of the checkpoint can reduce the reply time, but increase the system load: The checkpoint process will result in a large number of system resources. The checkpoint occurs at the following time: When using the checkpoint state, when using the shutdown to turn off the SQL Server, use the service administrator to turn off the SQL Server, and the SQL Server automatically generates a checkpoint. Checkpoint operation checkpoint procedure Executes several operations, including the following: Write all DIRTY PAGEs at this time when checkpoint begins to write to the disk in the disk, but not written to the disk. . Write the unfinished transaction list to the transaction record file telling SQL Server which transactions do when checkpoints occur. If the system fails, the reply process will reply to the transaction. Write all DIRTY record files to the disk to ensure that the record file buffer will be updated into the disk. Save the checkpoint record to the database due to the transaction record file being backed up and deleted, so you need to retain a checkpoint record outside the transaction record. Set the checkpoint interval checkpoint interval is defined by the SQL Server setting options. This configuration option is set throughout the SQL Server system, not set for a single database, but checkpoints will be executed in each database. This configuration option specifies the number of minutes when SQL Server is used to reply to each repository when the system is faulty. The preset value is 0, indicating that SQL Server determines the checkpoint interval, usually less than one minute. This preset value may cause excessive checkpoints in a system with a large number of memory and a large number of insertions and update behavior. In this case, you can set this configuration option to a larger value. If your user can tolerate the system failure, spend longer reply time (for example, 30 minutes), then your system's transaction efficiency will increase. You should set this configuration option based on your ability to control the power of downtime and failure. The checkpoint interval also depends on the number of records in the transaction record file. It is not determined by the system time or record file size.
The more records in the transaction record file, the shorter the checkpoint interval. The more changes made, the more records inserted into the transaction record file, causing the SQL Server setting checkpoints must often be changed frequently. If there is little change in the database, the transaction record file will only contain little records, and the checkpoint interval will grow long. The restore interval configuration option can be changed through two ways: using Enterprise Manager or use T-SQL. To set up a recovery interval from Enterprise Manager, press the right button on the left pane to set this configuration option, select the content from the flash function to enter the SQL Server property window. Select the database settings page sign, as shown in Figure 32-1, specify the required recovery interval in the restore interval selection square, and units are minutes. Figure 32-1 SQL Server Property Window
To set up a recovery interval with T-SQL, use the sp_configure pre-depreciation, as shown below:
SP_CONFIGURE "Recovery INTERVAL", 1
Go
You will see the output below:
DBCC EXECUTION completed. If DBCC Printed Error Messages,
Contact Your System Administrator.
Configuration option Changed. Run The Reconfigure Statement
To Install.
This change will not take effect before executing the Reconfigure command. If you confirm this, type the following T-SQL statement:
Reconfigure
Go
The RECONFIGURE command issues a signal to SQL Server to accept the set changes. No need to change the SQL Server to change the restore interval configuration options. To make sure your setting does take effect, use the following T-SQL statement:
SP_CONFIGURE "Recovery INTERVAL"
Go
The output looks like this:
Name minimum maximum config_value run_value
------------------------- ----------- - - ---------
Reconvery Interval (min) 0 32767 1 1
It can be seen that the restore interval configuration option has been set.
Note that the recovery interval configuration option is advanced option, only after careful planning. Increasing the recovery interval setting will increase the time required to perform the log library.
The backup method has several ways to back up the database: full backup, difference backup, transaction record file backup, archive group backup and data file backup. Each method has its own way of operation and function. Full Backup (Full Backup) backup database, profile group, or all information in the profile file. Difference backup (Differential Backup) only backs up information that has been changed since the last backup. Transaction Log Backup is used to back up and delete transaction record files. (As we see, the backup transaction record file is a very important DBA task, because transaction record information is used by the file group backup (File Group BACKUP) and data file backup (DataFile Backup) Back up the specific archive group or data file in the database. All SQL Server backups are performed on a specific database. To complete the backup, you should back up all the databases in the system and their transaction record files. Don't forget to back up the Master database. Remember, there is no good backup, you can't restore your information when there is a fault event. Fully backup already mentioned, full backup will back up a complete database. All archives groups and data files as part of the database will be backed up. If you have multiple databases, you should back up all databases. For backup small-scale database, full backup may be the most common technology. According to the size of the database, the entire process may be considerable. Therefore, if time becomes a problem, you may consider performing a differential backup or archive group backup, which will be discussed later. Once you start backing up, there is no way to pause it, the backup process will continue to complete the backup throughout the database. We will discuss the execution of full backup after "Executing Backup> section later. Differential backup Differences Backups only back up information that has been modified since the last backup. Since they only back up a part of the information, the difference backup is fast than the full backup, and the space occupies less. However, the reduction of differential backup is more difficult than the complete backup, and it takes more time. Restore differential backups require recently complete backup restoration, all differences are generated since the last complete backup. Transaction Record By Backup Transaction Record file backup enables you to back up your transaction record file. As mentioned earlier in this chapter, these backups are important for the database reply. Archives Group Backup Archive Group Backup The files related to a single file in the database are backed up. This process is similar to a full backup, it will back up all the information in the data file, and do not consider the time for the last backup. You can use the archive group backup to back up and specific department or work groups with associated archives groups using the archive group backup. If your system is divided into all independent departments and access their own archive group, you can separately back up the information of each department in different schedules. Data Archives Backup Data Archive Backup Enables you to back up a single file in the archive group. This type of backup type and the ability of SQL Server 2000 restore a single data file. If you don't have enough time to back up the entire archive group every night, the data file backup will be very easy to allow you to loop back up the information file. When the disk failure event occurs, some information file is lost or destroyed, you only need to restore this specific information file. However, the longer the data file backup time, the longer the time spent on the restore process. Perform a backup You can use Enterprise Manager, T-SQL instructions, or Create Database Backup Wizard to perform a backup. The Create Database Backup Wizard method is the simplest in these methods, but Enterprise Manager is also easy to use. On the other hand, the T-SQL instruction can be placed into the SQL instruction file so that it can be reused. The most important way you need should use. The backup operation itself can be directed to the entity device or logic device.
Physical Device is an object such as a tape drive or a disk machine. The physical device assigns a name through the job system, and you must use these names to access these devices. Since these pre-allocated names are difficult to remember, you may want to create an alias or user custom name for the physical device. Such an alias is called a logical device. These logical devices exist only in SQL Server and can only be used by SQL SEVER backup, so it can also be considered a logical backup device. If you want to back up your data to a logical device, you must pre-establish this device. Let's take a look at how to establish a logical backup device before we discuss different methods of backed up. We will use a logical backup device as an example of this section. (See the system administrator to increase the details of the physical device in the system.) Establishing a logical backup device logic backup device has two methods: using Enterprise Manager or T-SQL. We will discuss these two techniques in this section. The use of multiple backup devices can improve performance. (Tips for backup performance will be provided in this chapter.) The method of backup performance will be provided.) Establishing a backup device to establish a backup device To build a backup device using Enterprise Manager is as follows: Expand the SQL Server group folder in the left pane of Enterprise Manager, Server data clips and management data clips.
Press the mouse button on the backup, then select the new backup device from the fast display function, enter the backup device properties window, as shown in Figure 32-2.
Figure 32-2 "Backup Device Properties" Window
As long as you type a descriptive name of the backup device in the name text block, the file name text is automatically filled. To change the file name path, you can type a new path in the file name text, or click the Browse button to open the backup device position dialog. In this example, the name of the backup device is backup_dev_1. If you want to add a tape device, press the View Content button to see the backup collection current in the tape device. Once these steps have been completed, this device is already available. In the process of learning how to establish a backup process, we must learn how to use the backup device. Note that if you don't have any tape devices connected to your system, you cannot use the tape device name option. Establish a backup device using T-SQL To create a backup device using T-SQL, use the pre-stored program sp_addump-device. The syntax of sp_addumpDevice is as follows:
Sp_addumpDevice device_type, logical_name, physical_name
The device_type configuration option can be DISK, TAPE or PIPE, indicating a disk, a tape drive, and a connection coordination vendor software to a backup system. The logical_name configuration option is the name you assigned to this device; this name can be referenced in Backup and restore statements. The physical_name configuration option is the device or file name of the system allocation. For example, to create a logical device called a Backup_DEV_2 disk file, use the following syntax:
sp_addumpdevice 'disk', 'backup_dev_2',
'C: /msql7/backup/backup_dev_2.bak'
Establishing a remote backup device To back up the database to the remote system, you must first use the system preparation program sp_addumpDevice to establish a backup device. You cannot establish a backup device with Enterprise Manager on the remote server. In order to specify the remote system, you must specify the full name of the Universal Naming Convention (UNC) like the entity name, as shown in the following example: sp_addumpdevice 'disk', 'NetBackup1',
'//ptc4/c //backup/netbackup1.bck'
Once this backup device is established, you can use Enterprise Manager or T-SQL statements to back up the data.
Note Back up the data to the remote system, you must first install SQL Server to execute. If you do under the localsystem account, the localsystem account does not have the right to access the remote system, making the backup failed.
By multiple network backup materials, you can also perform backups through multiple network interface cards. After several LAN program sections, you can overcome network bandwidth issues that may limit performance. If you just back up the data into a system through two LAN program, you can specify the IP address on the UNC location, as shown below:
sp_addumpdevice 'Disk', 'NetBackup1',
'//100.100.100.1/c $/backup/netbackup1.bck'
sp_addumpDevice 'Disk', 'NetBackup2',
'//100.100.200.1/c $/backup/netbackup2.bck'
This backup device is created, you can use Enterprise Manager or T-SQL instructions to back up the data inside. After you have established one or more backup devices using Enterprise Manager, you can start performing backup. We will first discuss Enterprise Manager methods. In order to avoid revitalization, some transaction record files and database backup methods will be discussed together, in which specific options that can be identifiable will be specifically described. Perform a backup To perform a backup using Enterprise Manager, follow these steps:
To call the SQL Server Backup tool, use one of the techniques below:
Expand a server in the left pane of Enterprise Manager and expand the management folder. The right button is selected on the backup and select the backup information table from the quick-out function table. Expand a server in the left pane of Enterprise Manager, then select the right button on the database, and select all your work from the quick function table, then select Backup Data Sheet. Expand a server in the left pane of Enterprise Manager, then click the database folder. Tap the right button on a database in the right pane and select all the work from the quick function table, then select the backup database. The SQL Server backup window will appear, as shown in Figure 32-3.
Figure 32-3 General Tap of SQL Server Backup Window
Select the database you want to back up in the top area of the dialog box in the database drop-down list. (If you use the third technology in the first step, the name of the database has been selected.) The name of the backup will be automatically generated according to the name of the database, you can type a backup name in the name text block. Write the name automatically generated. You can also type a backup description in the description text block. This instructions may be important when you try to restore the database. For example, if you have created this backup immediately before deleting a data sheet, the annotations in this backup description will be very useful. If you perform a backup before loading a new information, include this information in your description. In the backup area of this dialog, you must specify the type of execution backup. The available options will vary depending on the selected database. For example, the Northwind library presets the Truncate Log on checkpoint option, setting this option, allows the backup process unable to use the Transaction Log and File and File group options. These backup options are as follows: Database - Complete Backup Perform a full database backup; all information in the database will be backed up. Database - Difference Backup Execute a differential database backup; all information that will be backed up from the last backup. Transaction record files perform a transaction record file backup; it will simultaneously delete this record file. Archives and archive groups Back up a single archive group or file; you can specify a file group or file in your backup. Only one of the backup types can only be selected. To perform a full database backup and transaction record backup, you must perform twice. In the destination area, you must specify that the backup is to the tape or on a disk. Click new to increase, you can add more logical or entity backup devices. At this time, the selection of backup destination is displayed, as shown in Figure 32-4. In this dialogue, you can specify a file name or select the backup device from the Backup Device drop-down list. Click OK to return to the general tab. In the example of Figures 32-3, two devices are listed in the backup TO list. To delete a device, click Remove after selecting the device. Click on the content to view the contents of the device.
Figure 32-4 Selecting the backup destination dialog
If a backup device has previously used it, you can use information.
Name This name is selected by the user who has executed backup. The server has executed the name of the servo of the backup. The name of the database has executed the backup database. Type backup type (complete, difference, archive group, file). Date and time of date backup execution. Expiration time This backup specified expiration time. The total size of the scale backup collection. Description of the backup.
Remember, multiple backups can often be performed in the same backup device. In the OverWrite area in the SQL Server Backup dialog, you can select overwrite media (such as tape, disk) or an appended media. A disk device is usually used. But if you are using a tape, you need to use the old tape, you need to delete your previous information. Although you can select OverWrite EXisting Media in the dialogue to override those information, but better habits are clearing those information before performing backups. This preventive measures help ensure that a tape or disk device does not accidentally overwritten. In the scheduling area, you can choose to schedule backups later. Scheduling backup is especially useful for transaction record files, which must be performed at a certain time to avoid transaction record files being filled. If you want to schedule a backup, select the schedule, then click the ... button to display the editing schedule, as shown in Figure 32-5. In the Name text block, provide a name for each schedule. The name of the schedule allows you to build multiple schedules (perhaps each backup a schedule). In the Scheduling type area, you can specify whether to start the backup when the SQL Server agent is started, whether you start the backup as long as the CPU is idle, and whether the backup is only executed or execute it. If you choose to perform a backup, you can use the date to select the backup execution date and use the time to select the block to select time. Figure 32-5 Editing Screen Dialogue
To set the schedule of loop backup, click Repeat and change. At this time, the editing execution job scheduling dialog is displayed, as shown in Figure 32-6. This dialogue provides you with excellent schedule flexibility. In the daily, weekly and monthly options, you can arrange the frequency and duration of each work.
Figure 32-6 Editing the execution job schedule dialog
Click OK to return to the editing schedule, click OK to return to the SQL Server backup dialog box, then click the options page sign, as shown in Figure 32-7. In this tab, you can specify whether you should confirm the backup media when the backup is complete, and specify whether you want to mark and how to log this media. The options available are as follows:
Confirm that the backup confirmation backup media is readable when confirming. Only confirm the integrity of the media; do not confirm whether the information has been backed up during the process. After backup, exit the tape After the backup is completed, the tape is exited from the tape device. This option is useful when multiple applications or users are accessing tape devices. This option avoids your tape to be overwritten by others. Remove the items that are not used in the transaction record file to delete the transaction record file after the backup. Check Media Set Name and Backup Set Expiration Specifies the media to be checked and not overwritten before expiration. Check if the media set name and whether the backup set overdue allows the setting of the media to be set. Backup and overdue time allow for specifying a logging for the media.
Figure 32-7 Options page for SQL Server Backup Dialog
After completing the settings of these options, click OK to start performing the set backup. Manage Backup To view, delete, and modify your backup work, follow these steps:
In the left pane of Enterprise Manager, expand a server folder, expand the management folder, expand the SQL Server Agent program folder, and click on the job. The work that has been scheduled will be listed in the right pane of Enterprise Manager, as shown in Figure 32-8.
Figure 32-8 Enterprise Manager job display
To delete a job, just press the right button on the job name and select Delete from the quick function table. To view or modify a job, press the right button on the work name and select the content from the quick function table to display the properties window. Execute your modification, then click to use and determine. Use the T-SQL instruction to back up just start using the T-SQL backup database than using Enterprise Manager, but if you belong to the DBA type that likes to use the instruction file, then this technology should be what you like. . The TSQL Backup instruction also provides options that are more than the backup program in Enterprise Manager. In this section we will discuss the syntax and options of the Backup instruction. There are actually two T-SQL backup instructions, which are based in accordance with the type of backup you want to perform. These instructions are as follows: Backup Database is used to back up the entire database, archive or archive group. Backup log is used to back up the transaction record file. Because these two instructions share the same option, we put them together to discuss them. The backup statement syntax for performing a backup for full database backup is as follows: Backup Database Database_name
To backup_device
[With options]
This statement requires only the database name, file name, or archive group name and backup device name. Multiple file names or archive group names may be included, separated by commas in the middle. The statement of the transaction record file backup is as follows:
Backup log database_name
{
[With {no_log | truncate_only)]
}
|
{
To backup_device
}
[With options]
This statement requires the database name, with no_log or with trun-cate_only option, or the backup device name, and then you can join any option you need. NO_LOG and TRUNCATEONLY options are synonymous, and only record files are deleted without using backup copies.
Note In the system failure event, you will not be able to reply to the fault point if you use the Backup Log statement, because no record file will be saved. It is not recommended to use these two options to use them to take yourself.
In all three backup instructions, Database_name is the database name you want to perform a backup. The Backup_Device configuration option can be a logical backup device name or an entity device name. If specified as an entity device, it is necessary to add preammers DISK =, TAPE = or PIPE = to the device name according to the device type. You can specify a device or a comma-separated set of devices, as shown in two examples:
Backup_dev_1, backup_dev_2, backup_dev_3
TAPE = '//./tape0', tape = '//./tape1', TAPE = '//./tape2'
Option Table 32-1 lists the options available for Backup instructions. These exceptions will be made to this exception if an option can only be used on the database backup or record file backup.
Table 32-1 Backup Directive Options
Option Description BlockSize entity block size, unit is a bit group. Description Specifies the text description of the backup collection. It is useful for the correct backup collection of the restore. DiffERENTIAL specifies a difference backup. This option is only useful in using a full database backup. Expiredate = date | retaindays = daysexpiredate option Specifies the date that the backup collection expires (can be overwritten). The retaindays option specifies the number of days before the backup collection expires. Password = password Specifies the password of the backup. Provides a larger security for backup itself. Format | NOFORMATFORMAT option Specifies that the media title will be rewritten, so it will cause the original data in the media to be invalid. NOFORMAT specifies that the media title will not be rewritten. The init | noinitinit option specifies that the backup set is in the first archive of the media and saves the media title, but overrides all the information in the media. That is, init overrides anything on the tape. The NOINIT option specifies that the backup is attached to the media. If you want to reuse the tape, you will need to use this option. MediaDescription = text Sets the description of the media collection. Medianame = media_name Specifies the name of the media. MediaPassword = password Name = backup_set_name Setting Backup Set Name Noskip | Skipnoskip Option Specifies that the expiration date of the backup collection in the media is specified before the backup collection is overwritten. The SKIP option does not check the expiration date. NO_TRUNCATE Specifies that the transaction record file is not deleted after the backup. This option is only useful in the record file backup. The NOUNLOAD | UNLOADNOUNLOAD option specifies that the media is not unloaded (for example, without exiting the tape) after the backup is completed. The unload option specifies that the media is uninstalled after completing the backup. Restart indicates that SQL Server restarts the interrupted backup. Stats [= percentage] displays a message after completing the percentage of the specified backup. This option will be useful if you want to view the operation. Please confirm that you specify that backup is attached to the media, or you should overwrite the media; the option you choose will affect the number of information written in the tape. If you are going to back up the information to a tape device that has been used, it does not clear this tape, nor does it specify whether to overwrite the tape, you will find that the space of the tape is quickly running out. In an additional mode, the backup program will only use the available space of the tape end.
Real world uses Backup here to have examples of using the Backup T-SQL instruction. The following example is a backup information file for an Example database:
Backup Database EXAMPLE
To backup_dev_1, backup_dev_2
WITH
Description = "DB backup of example",
Stats = 5
Go
The backup device is backup_dev_1 and backup_dev_2, and the statistics are displayed as 5% of the backup. Note that the backup instructions are provided in the previous example. If you test this example on a small database, such as NorthWind, you see the statistics that you see are not 5% increment, you might see, such as 7%, 16%, etc. This difference occurs because the backup program is read and written to 5% of the entire backup, and that large increment is displayed. For larger data collection, the increment of the write will be smaller than 5%, so it will be displayed in the preset. The following statement will back up the transaction record file of the Example database:
Backup log esample
To backup_dev_3, backup_dev_4
WITH
Description = "db backup of example", stats = 25
Go
The backup device is Backup_DEV_3 and backup_dev_4, and the statistics will display 25% interval. The output shows the percentage of completed operations and backup results. You will be notified how many pages have been notified, and how long the backup is spent, and the speed of the backup (MB / sec).
Manage Backup Because the T-SQL instruction backup is not executed under Enterprise Manger, you cannot execute under the SQL Server Agent, so you cannot scraten a job through the Backup instruction. However, you can use the SQL Server schedule to schedule a T-SQL Backup instruction. Once this work is scheduled, you can manage this work with the Manage Enterprise Manager backup. Use the establishment of a database backup wizard now let us enter the third method of implementing a backup: use the establishment of a database backup wizard. Perform a backup To use the Database Backup Spirit to perform a backup, follow these steps:
In Enterprise Manager, click on the database you want to establish, then select the wizard from the tool function table, display the Select Elf Dialog. In the Select Elf Dialog, the management data clip is deployed, select the backup wizard, then click OK. At this time, you will have a Welcome to backup wizard screen, as shown in Figure 32-9.
Figure 32-9 Welcome to the database backup wizard screen
Click Next to enter the database screen to choose to back up, as shown in Figure 32-10. In this picture, specify the database that will be backed up. Figure 32-10 shows the selected northwind database.
Figure 32-10 Select the database picture to be backed up
Click Next to enter the name and description screen of the input backup, as shown in Figure 32-11. Provides the name and description of the backup collection, type name in the text block of the name, type the description in the description text block. If you have a lot of backup, it is recommended to type some instructions.
Figure 32-11 Enter the name and description screen of the backup
Click Next to select the backup type screen, as shown in Figure 32-12. Select the type you want to perform a backup: Backup of the entire database, a differential data library backup or transaction record file backup. Figure 32-12 shows that the selected is backup the entire database.
Figure 32-12 Select the backup type screen
Click Next to enter the selection backup destination and action screen, as shown in Figure 32-13. In the selection backup device area, specify whether to back up the data to a tape, a file, or a specific backup device, if necessary, type the file name or device name in the appropriate text block. In the attribute area, specify whether to overwrite or attach backup media, whether or not to pop up the tape after the backup (if you use the tape), and whether the backup integrity should be confirmed. Confirm that the integrity of the backup is a good idea because the bad tape may cause the entire backup failure. SQL Server reads tape and confirms that all data is readable to confirm the integrity of the backup.
Note The establishment of the library backup wizard only allows you to select a backup device, which will completely affect the backup performance, we see it in the
Figure 32-13 Select the backup destination and action screen
Click Next to enter the backup confirmation and the scheduling screen, as shown in Figure 32-14. Here is an option to confirm the media to mark and set the expiration date, such as the description in
Click Next to enter the completion of the establishment database backup wizard screen, as shown in Figure 32-15. Check the information in the text, and click Finish to perform this backup.
Figure 32-15 Complete the establishment of a database backup wizard screen
Managing Backup If you use the Database Backup Elf, you can only perform backup or establish a regular backup job. If a job is established, Enterprise Manager or T-SQL must be used to manage jobs. Manage jobs in this chapter
Real World Planning Backup This has several examples of backup plan, which may help you develop your own backup schedule:
Small systems in the 5-by-8 environment This type of system usually allows full backup per night. The transaction record file is only backed up once a day depending on the size and completion of the transaction record file. The medium-sized system in the 7-by-24 environment performs a large number of backup downtime in the medium-sized system in the 7-by-24 environment. However, if your system is this size, you can perform a full backup at the weekend. The backup schedule is as follows: Monday Different Database Backup Tuesday Division Database Backup Wednesday Different Database Backup Thursday Different Database Backup Friday Data Library Backup Six Data Library Backup Sunday Full Database Backup Daily Base Required transaction record file backup 7-BY-24 environment large-scale system large-scale system may not allow full backups to complete within a day. The compromised method is to complete the full backup to several days (in this example, from Saturday and Sunday): Monday Database Backup On Tuesday Different Database Backup Wednesday Database Backup Thursday Database Backup Friday Data Library Backup Saturday Full Archive Group Backup Sunday Full Profile Group Backup The information is backed up every day. This information is to provide you with how to schedule backup. Since each system and system requirements are different, only you can decide the best scheduled backup.
