Here's how I've verified the automated backups that are available.
1, open Enterprise Manager-> Management->sql Server Agent
2, create a new job, the name of the job to take, for example: Data backup, the owner Select SA, of course, you can also choose other users, provided that the user has the right to execute the job;
3. Click the Step tab to enter the Step panel. New step, the step name can be casually filled out, such as Step 1, type and database default, do not need to be modified. The following statement is written to the command:
Backup DB [database name] to DISK = N ' f:\\databack\\firenews backups ' with Noinit, nounload, NAME = n ' firenews backup ', Noskip, STATS = Ten, Noformat
Note: You need to modify the place, database name, disk= (here need to fill in the path and the name of your database backup) name= can be easily filled in.
4, click on the Dispatch tab, into the dispatch panel, new schedule, name casually fill in, choose Recurring, point change you want to choose the task of arbitrary scheduling. such as every day, every 2 days, every week, monthly. Set yourself up according to your needs; (Note: There is a startup option behind the dispatch name that must be checked)
5, make sure, do not forget one thing, in the work you have just established the right button to start work, if your work is no problem, will be prompted to perform successfully, and have a corresponding backup file on your disk appear;
6, another important problem is that your SQL Server Agent server has been started.
If we need to generate a new backup based on the daily date, so that we can distinguish between backup files. At this point, we need to modify the SQL statement just now.
Reference Example:
1, automatic backup, fixed backup name, each will append backup
Copy Code code as follows:
Backup database firenews to disk=n ' f:\\databack\\firenews backups ' with Noinit,nounload,name=n ' firenews backup ', noskip,stats=10 , Noformat
Backup database Fireweb to disk=n ' F:\\backup\\data\\fireweb\\fireweb backups ' with Noinit,nounload,name=n ' fireweb backup ', Noskip,stats=10,noformat
2, automatic backup, time naming, full backup
Copy Code code as follows:
declare @filename nvarchar set @filename = ' F:\\databack\\data ' +convert (char (8), GETDATE (), 112) + '. bak ' Print @ FileName backup DATABASE [firenews] to DISK = @filename with Noinit, nounload, NAME = N ' firenews backup ', noskip, STATS = 10, Noformat
declare @filename nvarchar set @filename = ' F:\\backup\\data\\fireweb\\fireweb ' +convert (char (8), GETDATE (), 112) + '. bak ' Print @filename backup DATABASE [Fireweb] to DISK = @filename with Noinit, nounload, NAME = N ' fireweb backup ', Noskip, STATS = ten, Noformat
Note:
1, the backup of the database stored folder must already exist;
2, N must be capitalized;
3, the value of name can be written casually;
4, Firenews,fireweb for the database name;
5, when the new job prompts: Failed to find the stored procedure master.dbo.xp_regread, the table name master database does not have this stored procedure (may be done when the security processing deleted), you need to register. The method is: Find the database--
master--Extended stored procedure, right-click "New Extended Stored Procedure" (Name: xp_regread, path directly fill: Xpstar.dll)
The following are the complete methods for backing up the database:
Back up the entire database:
Copy Code code as follows:
BACKUP DATABASE {database_name | @database_name_var}
to < Backup_de Vice > [,... n]
[with
[BLOCKSIZE = {BLOCKSIZE | @blocksize_variable}]
[[,] DESCRIPTION = {' Text ' | @text_variable}]
[[,] differential]
[[,] expiredate = {date | @date_var}
| Retaindays = {days | @days_var}]
[[,] PASSWORD = {PASSWORD | @password_variable}]
[[,] FORMAT | Noformat]
[[,] {INIT | NOINIT}]
[[,] mediadescription = {' Text ' | @text_variable}]
[[,] MEDIANAME = {Media_name | @media_ Name_variable}]
[[,] Mediapassword = {Mediapassword | @mediapassword_variable}]
[[,] NAME = {Backu P_set_name | @backup_set_name_var}]
[[,] {noskip | SKIP}]
[[,] {norewind | Rewind}]
[[,] {nounload | UNLOAD}]
[[,] restart]
[[,] STATS [= percentage]]
]
Parameters
DATABASE
Specify a full database backup. If you specify a list of files and filegroups, only those files and filegroups that are specified are backed up.
Explains that when a full database backup or differential database backup is performed, Microsoft®sql Server™ backs up enough transaction logs to generate a consistent database that will be used when the database is restored. Only full database backups can be used on the master database.
{database_name | @database_name_var}
Specifies a database from which to back up the transaction log, a partial database, or a complete database. If supplied as a variable (@database_name_var), the name can be specified as a variable of a string constant (@database_name_var = database name) or a string data type (except ntext or text data type).
< backup_device >
Specifies the logical or physical backup device to use when backing up operations. Can be one or more of the following forms:
{Logical_backup_device_name} | {@logical_backup_device_name_var}
is the logical name of the backup device created by sp_addumpdevice, and the database is backed up to the device, whose name must comply with the rules for identifiers. If it is supplied as a variable (@logical_backup_device_name_var), the backup device name can be specified as a string constant (@logical_backup_device_name_var = logical Backup Device name) or a variable of the string data type (except the ntext or text data type).
{DISK | TAPE} =
' Physical_backup_device_name ' | @physical_backup_device_name_var
Allows you to create a backup on a specified disk or tape device. The specified physical device does not have to exist before the BACKUP statement is executed. If there is a physical device and the INIT option is not specified in the BACKUP statement, the backup is appended to the device.
When you specify to DISK or to TAPE, enter the full path and file name. For example, DISK = ' C:\Program files\microsoft SQL Server\mssql\backup\mybackup.dat ' or TAPE = ' \\.\tape0 '.
Note for backups to disk, if you enter a relative pathname, the backup files are stored in the default backup directory. The directory is set up and stored in the BackupDirectory registry key value under the Key_local_machine\software\microsoft\mssqlserver\mssqlserver directory.
If you are using a network server with a uniform naming convention (UNC) name or a redirected drive letter, specify the device type of the disk.
When you specify multiple files, you can mix logical file names (or variables) and physical file names (or variables). However, all devices must be of the same type (disk, tape, or pipe).
Windows 98 does not support backing up to disk.
is a placeholder that indicates that multiple backup devices can be specified. The maximum number of backup devices is 64.
BLOCKSIZE = {BLOCKSIZE | @blocksize_variable}
Specifies the size of the physical block with the number of bytes. On Windows NT Systems, the default setting is the device's default block size. In general, this parameter is not required when SQL Server chooses a block size that is appropriate for the device. On Windows 2000-based computers, the default setting is 65,536 (in kilobytes KB, which is the maximum size supported by SQL Server).
For disks, BACKUP automatically determines the appropriate block size for the disk device.
Note If you want to store the resulting backup set in a CD-ROM and then recover from the CD-ROM, set BLOCKSIZE to 2048.
The default BLOCKSIZE for tapes is 65,536 (in kilobytes). Explicitly declaring a block size overrides the block size selected by SQL Server.
DESCRIPTION = {' Text ' | @text_variable}
Specifies Free-form text that describes the backup set. The string can be up to 255 characters long.
Differential
Specifies that database or file backups should be consistent with the database or file portions that have changed since the last full backup. A differential backup typically takes up less space than a full backup. Use this option to no longer have to back up all the individual logs that were backed up when the last full backup was made. For more information, see Differential database backups and file differential backups.
Explains that SQL Server backs up enough transaction logs for a full database or differential backup to generate a consistent database when the database is restored.
ExpireDate = {date | @date_var}
Specifies the date when the backup set expires and is allowed to be overridden. If the date is provided as a variable (@date_var), you can specify the date as a string constant (@date_var = date), string data type variable (except ntext or text data type), smalldatetime, or datetime variable. And the date must conform to the configured system DateTime format.
Retaindays = {days | @days_var}
Specifies how many geniuses must go through to override the backup media set. If specified with a variable (@days_var), the variable must be an integral type.
Important if expiredate or retaindays are not specified, the validity period will depend on the sp_configure media retention configuration settings. These options only prevent SQL Server from overwriting files. You can erase the tape in other ways, and the disk file may be deleted through the operating system. For more information about expiration validation, see SKIP and FORMAT in this topic.
PASSWORD = {PASSWORD | @password_variable}
Set a password for the backup set. PASSWORD is a string. If you define a password for the backup set, you must provide this password to perform any restore operations on the backup set.
The critical backup set password prevents unauthorized access to the contents of the backup set through the SQL Server 2000 tool, but does not prevent overwriting the backup set.
For more information about using passwords, see the Permissions section.
FORMAT
Specifies that the media header should be written to all volumes for this backup operation. Any existing media headers are overridden. The FORMAT option invalidates the entire media content and ignores any existing content.
Be cautious about important use FORMAT. Formatting a backup device or media will make the entire media set unavailable. For example, if you initialize a single tape in an existing striped backup set, the entire backup sets become unavailable.
By specifying FORMAT, the backup operation implies SKIP and INIT, and none of these are explicitly described.
Noformat
Specifies that the media header should not be written to all volumes for the backup operation, and do not overwrite the backup device unless INIT is specified.
Init
Specifies that all backup sets should be overridden, but the media headers are reserved. If INIT is specified, all existing backup set data on that device is overwritten.
Do not overwrite backup media when you encounter one of the following situations:
The backup settings on the media are not all expired. For more information, see ExpireDate and Retaindays options.
The backup set name does not match the name on the backup media if the BACKUP statement gives the name of the Snapup set. For more information, see the NAME clause.
Replace these checks with the SKIP option. For more information about interacting with SKIP, Noskip, INIT, and Noinit, see the comments section.
Description if the backup media is password protected, SQL Server will not write to the media unless the media password is provided. Skip option does not override this check. Password-protected media can only be overridden by formatting. For more information, see FORMAT Options.
Noinit
Indicates that the backup set is appended to the specified disk or tape device to retain the existing backup set. Noinit is the default setting.
The restore command's FILE option is used to select the appropriate backup set at restore time. For more information, see RESTORE.
If you have defined media passwords for your media set, you must provide a password.
MediaDescription = {text | @text_variable}
Indicates the free-form text description of the media set, up to 255 characters.
MediaDescription = {text | @text_variable}
Indicates the media name for the entire backup media set, up to 128 characters. If MEDIANAME is specified, it must match a previously specified media name that already exists on the backup volume. If you do not specify a MEDIANAME, or SKIP option is specified, the media name will not be validated for inspection.
Mediapassword = {Mediapassword | @mediapassword_variable}
Set a password for the media set. Mediapassword is a string.
If a password is defined for the media set, this password must be supplied when the backup set is created on the media set. In addition, you must provide a media password when performing any restore operations from this media set. Password-protected media can only be overridden by formatting. For more information, see FORMAT Options.
For more information about using passwords, see the Permissions section.
NAME = {Backup_set_name | @backup_set_var}
Specifies the name of the backup set. The name can be up to 128 characters long. If NAME is not specified, it will be empty.
NORECOVERY
Used only with BACKUP LOG. Back up the tail of the log and put the database in the state of being restored. NORECOVERY is useful when you failover to a secondary database or save the tail of the log before the RESTORE operation.
STANDBY = Undo_file_name
Used only with BACKUP LOG. Back up the tail of the log and leave the database in read-only or Standby mode. The Undo file name specifies the store that holds the rollback change, and if the RESTORE LOG operation is subsequently applied, you must undo the rollback changes.
If the specified undo file name does not exist, SQL Server creates the file. If the file already exists, SQL Server overrides it. For more information, see Using an alternate server.
Norewind
Specifies that SQL Server keeps the tape open after the backup operation completes. Norewind meaning is nounload. SQL Server retains ownership of the tape drive until the backup or RESTORE command uses rewind.
If the tape is inadvertently turned on, the quickest way to release the tape is to use the following RESTORE command:
RESTORE labelonly from TAPE = <name> with rewind to find the list of tapes being opened by querying the Sysopentapes table in the master database.
Rewind
Specifies that SQL Server will release tapes and rewind. If both Norewind and rewind are not specified, the default setting is rewind.
Noskip
Instructs the BACKUP statement to check the expiration date of all backup sets on the media before they can be overridden.
SKIP
Disables backup set expiration and name checking, which are typically performed by the backup statement to prevent overwriting the backup set. For more information, see the comments section.
Nounload
Specifies that the tape is not automatically unloaded from the tape drive after the backup. The setting is always nounload until the specified UNLOAD. This option is for tape devices only.
UNLOAD
Specifies that tapes are automatically rewind and unloaded after the backup completes. When a new user session is started, its default setting is UNLOAD. This setting is persisted until the user specifies Nounload. This option is for tape devices only.
Restart
Specifies that SQL Server restarts an interrupted backup operation. Because the restart option restarts the operation when the backup operation is interrupted, it saves time. To restart a specific backup operation, repeat the entire BACKUP statement and add the restart option. You do not have to use the restart option, but it saves you time.
Important This option is used only for backup to tape media and for backups that span multiple tape volumes. There will never be a restart operation on the first volume of the backup.
STATS [= percentage]
Displays a message whenever another percentage ends, which is used to measure progress. If you omit Percentage,sql Server, a message is displayed for each completed 10%.
<file_or_filegroup>
Specifies the logical name of a file or filegroup that is contained in a database backup. You can specify multiple files or filegroups.
FILE = {logical_file_name | @logical_file_name_var}
Name one or more files that are included in a database backup.
FILEGROUP = {Logical_filegroup_name | @logical_filegroup_name_var}
Name one or more filegroups that are included in the database backup.
Describes backing up a file when the size and performance requirements of the database make a full database backup impractical. To back up the transaction log separately, use BACKUP log.
Important You must provide a separate backup of the transaction log by using BACKUP log to restore the database using file and filegroup backups. For more information about file backups, see Backup using File backups.
If the recovery model is simple, file and filegroup backups are not allowed.
is a placeholder that indicates that multiple files and filegroups can be specified. There is no limit to the maximum number of files or filegroups.
LOG
Specifies that only the transaction log is backed up. The log is backed up from the last successfully executed log to the end of the current log. Once the log is backed up, the space that replication or active transactions no longer needs may be truncated.
Note If the backup log does not appear to truncate most of the logs, it is possible to have an old open transaction in the log. You can use DBCC sqlperf (logspace) to observe log space. For more information, see Transaction Log Backups.
No_log | Truncate_only
Deleting the Inactive log section without backing up the replication log and truncating the log. This option frees up space. Because log backups are not saved, it is not necessary to specify a backup device. No_log and truncate_only are synonymous.
When you use No_log or truncate_only to back up the log, the changes recorded in the log are not recoverable. For recovery, perform the BACKUP DATABASE now.
No_truncate
Allows backing up logs when a database is corrupted.
Comments
You can append a database or log backup to any disk or tape device so that the database and its transaction logs can be stored in a physical location.
When the database is in use, SQL Server uses an online backup procedure to back up the database. The following list includes actions that cannot be made when a database or transaction log backup occurs:
Allows file management operations during backup operations, such as ALTER DATABASE statements with the ADD file or REMOVE file option, and INSERT, UPDATE, or DELETE statements.
Shrink the database or file. This includes automatic shrink operations.
If you start a backup while these operations are in progress, the backup will terminate. If an attempt is made to perform these operations while a backup is in progress, the operation will fail.
As long as the operating system supports collation of the database, backup operations can be performed between different platforms, even if the platforms use different processor types.
