Automatic and timed backup of sqlserver Databases

The following is an automatic backup method that I have confirmed is available.

1. Choose Enterprise Manager> Manage> SQL Server proxy.

2. Create a new job and take the job name as needed. For example, for data backup, select SA as the owner. Of course, you can select another user, provided that the user has the permission to execute the job;

3. Click the step tab to go to the step panel. Create a step. You can enter the step name as needed, such as step 1. The default type and database do not need to be modified. Write the following statement in the command:
Backup database [database name] to disk = n' F: \ databack \ firenews backup 'with noinit, nounload, name = n' firenews backup', noskip, stats = 10, noformat

Note: The Database Name, disk = (here you need to enter the path and the name of your database backup) can be entered at will.

4. Click the scheduling tab to go to the scheduling panel. Create a scheduling task and enter the name as needed. Click "show" and select "Schedule" for the task to be executed. such as every day, every 2 days, every week, every month, etc. set it as needed. (Note: There is a startup option next to the scheduling name, which must be checked)

5. Do not forget one thing after you confirm it. Right-click the job you just created and start the job. If there is no problem with your job, a message indicating that the job is successfully executed will be displayed, corresponding backup files appear on your disk;

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 differentiate the backup file, we need to modify the SQL statement just now.

Reference instance:

1. Automatic Backup and fixed backup name. Backup will be appended each time

Backup database firenews to disk = n'f: \ databack \ firenews backup 'with noinit, nounload, name = n' firenews backup', noskip, stats = 10, noformat

Backup database fireweb to disk = n' F: \ backup \ data \ fireweb backup 'with noinit, nounload, name = n' fireweb backup', noskip, stats = 10, noformat

2. automatic backup, named after time, complete backup
Declare @ filename nvarchar (100) 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 (100) 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 = 10, noformat

Note:
1. the folder stored in the backup database must already exist;
2. N must be capitalized;
3. The value of name can be written at will;
4. firenews. fireweb indicates the database name;
5. When creating a job, the system prompts: The Stored Procedure Master could not be found. DBO. xp_regread: The table name master database does not have this stored procedure (it may have been deleted during Security Processing). You must register it yourself. The method is: Find the database --

Master -- extended stored procedure, right-click "create extended stored procedure" (Name: xp_regread, path: xpstar. dll)

The following describes how to back up a complete database:

Back up the entire database:

Backup database {Database_name|@Database_name_var}
To <backup_device> [,...N]
[
[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}]
[[,] Mediadegion={'Text'|@Text_variable}]
[[,] Medianame={Media_name|@Media_name_variable}]
[[,] Mediapassword={Mediapassword|@Mediapassword_variable}]
[[,] Name={Backup_set_name|@Backup_set_name_var}]
[[,] {Noskip | skip}]
[[,] {Norewind | rewind}]
[[,] {Nounload | unload}]
[[,] Restart]
[[,] Stats [=Percentage]
]

Parameters

Database

Specifies a complete database backup. If a list of files and file groups is specified, only the specified files and file groups are backed up.

DescriptionMicrosoft SQL Server backs up sufficient transaction logs for full or differential database backup to generate a database that will be consistent when the database is restored. InMasterOnly full database backup can be used on the database.

{Database_name|@Database_name_var}

A database is specified to back up transaction logs, some databases, or complete databases from the database. If (@Database_name_var), You can specify this name as a String constant (@Database_name_var = Database Name) Or string data type (NtextOrTextExcept for the data type.

<Backup_device>

Specifies the logical or physical backup device used for backup. It can be one or more of the following forms:

{Logical_backup_device_name} | {@Logical_backup_device_name_var} Is composedSp_addumpdeviceThe logical name of the created backup device. The database will be backed up to the device. The name must comply with the identifier rules. If you use it as a variable (@Logical_backup_device_name_var), You can specify the name of the backup device as a String constant (@Logical_backup_device_name_var=Logical backup device name) or string data type (NtextOrTextExcept for the data type.
{Disk | TAPE}='Physical_backup_device_name'|@Physical_backup_device_name_varAllows you to create backups on a specified disk or tape device. You do not need to have a specified physical device before executing the backup statement. If a physical device exists and the init option is not specified in the backup statement, the backup is appended to the device.

If you specify to disk or to tape, enter the complete path and file name. For example, disk = 'C: \ Program Files \ Microsoft SQL Server \ MSSQL \ backup \ mybackup. dat 'or tape =' \. \ tape0 '.

DescriptionIf you enter a relative path name for backup to a disk, the backup file is stored in the default backup directory. This directory is set during installation and stored in the backupdirectory registry key value under the KEY_LOCAL_MACHINE \ Software \ Microsoft \ MSSQLServer directory.

If you are using a network server with a unified naming rule (UNC) Name or a re-directed drive letter, specify the disk device type.

When multiple files are specified, the logical file name (or variable) and physical file name (or variable) can be mixed ). However, all devices must be of the same type (disk, tape, or pipe ).

Windows 98 does not support backup to a disk.

N

Yes indicates that you can specify placeholders for multiple backup devices. The maximum number of backup devices is 64.

Blocksize={Blocksize|@Blocksize_variable}

Specify the size of a physical block by number of nodes. In Windows NT, the default block size is set to the default block size of the device. Generally, this parameter is not required when SQL Server selects a block size suitable for the device. On Windows 2000-based computers, the default value is 65,536 (64 KB, the maximum size supported by SQL Server ).

For disks, backup automatically determines the appropriate block size of the disk device.

DescriptionIf you want to store the result backup set to the CD-ROM and then recover from the CD-ROM, set blocksize to 2048.

The default blocksize of a tape is 65,536 (64 KB ). The block size is explicitly declared to replace the block size selected by SQL Server.

Description={'Text'|@Text_variable}

Specifies the free text that describes the backup set. The string can contain a maximum of 255 characters.

Differential

The specified database backup or file backup should be consistent with the database or file part changed after the last full backup. Differential backup generally occupies less space than full backup. You do not need to back up all the individual logs backed up during the last full backup. For more information, see and.

DescriptionSQL Server backs up sufficient transaction logs during full or differential database backup to generate a consistent database when restoring the database.

Expiredate={Date|@Date_var}

Specifies the date on which the backup set expires and is allowed to be overwritten. If you use this date as a variable (@Date_var), You can specify the date as a String constant (@Date_var= Date), string data type variable (NtextOrTextExcept for data types ),SmalldatetimeOrDatetimeVariable, and the date must comply with the configured SystemDatetimeFormat.

Retaindays={Days|@Days_var}

Specifies how many days after which the backup media set can be rewritten. Assume that (@Days_var). The variable must be an integer.

ImportantIf expiredate or retaindays is not specified, the validity period depends onSp_configureOfMedia retentionConfiguration settings. These options only prevent SQL Server from overwriting files. You can use other methods to erase tapes, but the operating system can delete disk files. For more information about expired verification, see the Skip and format in this topic.

Password={Password|@Password_variable}

Set a password for the backup set. Password is a string. If a password is defined for the backup set, you must provide the password to restore the backup set.

ImportantThe backup set password prevents unauthorized access to the backup set content through SQL Server 2000, but does not prevent the backup set from being overwritten.

For more information about using passwords, see "Permissions.

Format

Specify that the Media Header should be written to all volumes used for this backup operation. Any existing media header is overwritten. The format option invalidates the entire media content and ignores any existing content.

ImportantExercise caution when using 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 set becomes unavailable.

By specifying the format, the backup operation implies skip and init.

Noformat

The specified Media Header should not be written to all the volumes used for the backup operation, and the backup device should not be overwritten unless init is specified.

Init

Specify that all backup sets should be overwritten, but the media header should be retained. If init is specified, all existing backup set data on that device will be overwritten.

Backup media is not overwritten in the following situations:

• None of the Backup Settings on the media have expired. For more information, see the expiredate and retaindays options.

• If the backup set name is provided in the backup statement, the backup set name does not match the name on the backup media. For more information, see the name clause.

Use the Skip option to replace these checks. For more information about the interaction between Skip, noskip, init, and noinit, see the annotations section.

DescriptionIf the backup media has password protection, SQL server will not write the media unless the media password is provided. The Skip option does not replace this check. Password-protected media can be overwritten only after formatting. For more information, see format options.

Noinit

Indicates that the backup set will be appended to the specified disk or tape device to retain the existing backup set. Noinit is the default setting.

The file option of the restore command is used to select an appropriate backup set during restoration. For more information, see restore.

If you define a media password for a media set, you must provide a password.

Mediadegion={Text|@Text_variable}

Specifies the free-format text description of a media set, which can contain a maximum of 255 characters.

Mediadegion={Text|@Text_variable}

Specifies the media name for the entire backup media set, which can contain a maximum of 128 characters. If medianame is specified, it must match the previously specified media name, which already exists in the backup volume. If medianame is not specified or the Skip option is specified, the media name is not verified.

Mediapassword={Mediapassword|@Mediapassword_variable}

Set a password for the media set. Mediapassword is a string.

If a password is defined for a media set, the password must be provided when a backup set is created for the media set. In addition, you must provide a media password when performing any restoration operations on the media set. Password-protected media can be overwritten only after formatting. For more information, see format options.

For more information about using passwords, see "Permissions.

Name={Backup_set_name|@Backup_set_var}

Specifies the name of the Backup set. The name can contain up to 128 characters. If no name is specified, it is null.

Norecovery

Used only with backup log. Back up the tail of the log and make the database in the restoring State. Norecovery is useful when a fault is transferred to a secondary database or when the log tail is saved before the restore operation.

Standby=Undo_file_name

Used only with backup log. Back up the tail of the log and make the database in read-only or standby mode. The undo file name specifies the storage that can accommodate rollback changes. If you subsequently apply the restore log operation, you must undo these rollback changes.

If the specified file name does not exist, SQL Server creates the file. If the file already exists, SQL Server overwrites it. For more information, see.

Norewind

Specify that SQL Server keeps the tape open after the backup operation is complete. Norewind means nounload. SQL Server retains the ownership of the tape drive until the backup or restore command uses rewind.

If you accidentally turn the tape on, the fastest way to release the tape is to use the following restore command:

Restore labelonly from tape = <Name> With rewind

QueryMasterIn the databaseSysopentapesTable to find the list of opened tapes.

Rewind

Specify that SQL Server releases tape and rewind. If neither norewind nor rewind is specified, the default value is rewind.

Noskip

Indicates that the backup statement checks the expiration date before it can override all backup sets on the media.

Skip

Disable backup set expiration and name check. These checks are generally executed by the backup statement to prevent the backup set from being overwritten. For more information, see the Notes section.

Nounload

Specifies that the tape is not automatically detached from the tape drive after the backup. Always set to nounload until the specified unload is specified. This option is only used for tape devices.

Unload

Specifies that the tape is automatically replayed and detached after the backup is complete. When a new user starts a session, it is set to unload by default. This setting is maintained until the User specifies nounload. This option is only used for tape devices.

Restart

Specify that SQL server restarts an interrupted backup operation. Because the restart option restarts the backup 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 can save time.

ImportantThis option is only used to direct the backup of tape media and back up across multiple tape volumes. There will never be a restart operation on the first volume of backup.

Stats [=Percentage]

Every time anotherPercentageA message is displayed at the end, which is used to measure the progress. If omittedPercentage, SQL Server displays a message every 10 percentage points after completion.

<File_or_filegroup>

Specifies the logical name of the file or file group contained in the database backup. You can specify multiple files or file groups.

File={Logical_file_name|@Logical_file_name_var}

Name one or more files contained in the database backup.

Filegroup={Logical_filegroup_name|@Logical_filegroup_name_var}

Name one or more file groups in the database backup.

DescriptionWhen the database size and performance requirements make it impractical to back up a complete database, back up a file. To back up transaction logs separately, use backup log.

ImportantYou must use backup log to provide separate backup of transaction logs before you can use file and file group backup to restore the database. For more information about file backup, see backup.

If the recovery model is simple, file and File Group Backup are not allowed.

N

Is a placeholder, indicating that multiple files and file groups can be specified. There is no limit on the maximum number of files or file groups.

Log

Specifies that only transaction logs are backed up. This log is backed up from the last successfully executed log to the end of the current log. Once the log is backed up, it may cut off the space that is no longer needed by the replication or activity transaction.

DescriptionIf the backup log does not seem to have truncated most of the logs, there may be an old open transaction in the log. You can use DBCC sqlperf (logspace) to observe the log space. For more information, see.

No_log | truncate_only

If you do not need to back up and copy logs, you can delete the inactive logs and truncate the logs. This option releases space. Because log backup is not saved, you do not need to specify a backup device. No_log and truncate_only are synonymous.

Changes recorded in the log cannot be recovered after logs are backed up using no_log or truncate_only. To recover the database, execute backup database immediately.

No_truncate

Logs can be backed up when the database is damaged.

Note

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 process to back up the database. The following list includes operations that cannot be performed during database or transaction log backup:

• File Management is allowed during backup operations, such as the alter database statement with the Add file or remove file option and the insert, update, or delete statement.

• Shrink a database or file. This includes automatic contraction.

If the backup is started while these operations are in progress, the backup will be terminated. If you attempt to perform these operations while backing up, the Operation will fail.

As long as the operating system supports database sorting rules, backup operations can be performed between different platforms, even if these platforms use different processor types.