Shulou(Shulou.com)06/01 Report--

SqlServer backup and recovery

Use chenjch

Select * into T1 from sys.objects

Select COUNT (*) from T1;-54

-complete database

BACKUP DATABASE chenjch

TO DISK='F:\ backup\ sqlserver\ chenjch_full.bak'

GO

192 pages have been processed for the database 'chenjch', file' chenjch' (located on file 1).

Six pages have been processed for the database 'chenjch', file' chenjch_log' (located on file 1).

BACKUP DATABASE successfully processed 198 pages and took 0.175 seconds (8.816 MB/ seconds).

-Database recovery

Insert into T1 select * from T1

Select COUNT (*) from T1;-108

Restore filelistonly from disk='F:\ backup\ sqlserver\ chenjch_full.bak'

RESTORE DATABASE chenjch_0617

FROM DISK ='F:\ backup\ sqlserver\ chenjch_full.bak'

WITH

MOVE 'chenjch' TO' F:\ backup\ sqlserver\ data\ chenjch_0617.mdf'

MOVE 'chenjch_log' TO' F:\ backup\ sqlserver\ data\ chenjch_0617_log.LDF'

192 pages have been processed for the database 'chenjch_0617', file' chenjch' (located on file 1).

Six pages have been processed for the database 'chenjch_0617', file' chenjch_log' (located on file 1).

RESTORE DATABASE successfully processed 198 pages and took 27.674 seconds (0.055 MB/ seconds).

Use chenjch_0617

Select COUNT (*) from T1;-54

Restore the database only through full recovery, and restore the data at the time of backup by default

-Database log backup

BACKUP LOG chenjch TO DISK='F:\ backup\ sqlserver\ chenjch_log.bak'

22 pages have been processed for the database 'chenjch', file' chenjch_log' (located on file 1).

BACKUP LOG successfully processed 22 pages and took 0.085 seconds (2.022 MB/ seconds).

-Database + log recovery

RESTORE DATABASE chenjch_0617_001

FROM DISK ='F:\ backup\ sqlserver\ chenjch_full.bak'

WITH NORECOVERY

MOVE 'chenjch' TO' F:\ backup\ sqlserver\ data\ chenjch_0617_001.mdf'

MOVE 'chenjch_log' TO' F:\ backup\ sqlserver\ data\ chenjch_0617_001_log.LDF'

RESTORE LOG chenjch_0617_001 from disk='F:\ backup\ sqlserver\ chenjch_log.bak'

WITH RECOVERY

192 pages have been processed for the database 'chenjch_0617_001', file' chenjch' (located on file 1).

Six pages have been processed for the database 'chenjch_0617_001', file' chenjch_log' (located on file 1).

RESTORE DATABASE successfully processed 198 pages and took 27.525 seconds (0.056 MB/ seconds).

0 pages have been processed for the database 'chenjch_0617_001', file' chenjch' (located on file 1).

22 pages have been processed for the database 'chenjch_0617_001', file' chenjch_log' (located on file 1).

RESTORE LOG successfully processed 22 pages and took 0.085 seconds (2.022 MB/ seconds).

Select COUNT (*) from T1;-108

Complete database + log recovery, you can restore the database to the latest state

-recovery based on point in time

Use chenjch

Select * into T1 from sys.objects

Select COUNT (*) from T1;-54

BACKUP DATABASE chenjch

TO DISK='F:\ backup\ sqlserver\ chenjch_full001d.bak'

GO

192 pages have been processed for the database 'chenjch', file' chenjch' (located on file 1).

Two pages have been processed for the database 'chenjch', file' chenjch_log' (located on file 1).

BACKUP DATABASE successfully processed 194 pages and took 0.174 seconds (8.688 MB/ seconds).

-delete data for the first time

Delete t1 where TYPE='S'

-- time to save the deleted table

SELECT dt=GETDATE () INTO a

Select * from a;-2018-06-18 14 14 1715 56.387

Select COUNT (*) from T1;-9

-delete data for the second time

Delete t1

-- time to save the deleted table

SELECT dt=GETDATE () INTO b

Select * from b;-2018-06-18 14 21 purl 53.940

Select COUNT (*) from T1;-0

The test restores the data to the moment the data was deleted for the first time

BACKUP LOG chenjch TO DISK='F:\ backup\ sqlserver\ chenjch_log001d.bak'

Eighteen pages have been processed for the database 'chenjch', file' chenjch_log' (located on file 1).

BACKUP LOG successfully processed 18 pages and took 0.036 seconds (3.797 MB/ seconds).

Use master

RESTORE DATABASE chenjch FROM DISK='F:\ backup\ sqlserver\ chenjch_full001c.bak'

WITH REPLACE,NORECOVERY

192 pages have been processed for the database 'chenjch', file' chenjch' (located on file 1).

Two pages have been processed for the database 'chenjch', file' chenjch_log' (located on file 1).

RESTORE DATABASE successfully processed 194 pages and took 27.487 seconds (0.054 MB/ seconds).

RESTORE LOG chenjch FROM DISK='F:\ backup\ sqlserver\ chenjch_log001d.bak'

WITH RECOVERY,STOPAT='2018-06-18 14-14-18-18-18-18-18-14-18-18-14-18-18-18-18-18-14-18-18-14-18-18-18-14-18-18-18-14-18-18-18-14-18-18-14-18-18-14-18-18-14-18-18-14-18-18-14-14-18-18-14-18-18-14-18-18-14-18-18-14-14-18-18-14-18-18-14-14-18-18-18-14-14-18-18-18-18-14-14-18-18-18-18-14-14-18-18-18-18-14-14-18-18-18-14-18-18-18

0 pages have been processed for the database 'chenjch', file' chenjch' (located on file 1).

Eighteen pages have been processed for the database 'chenjch', file' chenjch_log' (located on file 1).

RESTORE LOG successfully processed 18 pages and took 0.086 seconds (1.589 MB/ seconds).

Use chenjch

Select count (*) from T1;-9 SQLSERVER WITH options are as follows:

Https://docs.microsoft.com/zh-cn/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017

WITH option

One: specify the options to use for backup operations.

(1) CREDENTIAL

Scope of application: SQL Server (SQL Server 2012 (11.x) SP1 CU2 to SQL Server 2017) and SQL database managed instances.

Used only when creating backups to the Windows Azure Blob storage service.

(2) DIFFERENTIAL

Applicable scope: SQL Server.

Can only be used with BACKUP DATABASE, specifying that a database backup or file backup should contain only the database or file portion that has changed since the last full backup.

Differential backups generally take up less space than full backups.

Use this option to eliminate the need for backups for all individual log backups performed since the last full backup.

By default, BACKUP DATABASE creates a full backup.

(3) ENCRYPTION

Used to specify that the backup will be encrypted.

You can specify the encryption algorithm used to encrypt the backup, or you can specify NO_ENCRYPTION to unencrypt the backup.

Encryption is recommended to help secure backup files.

The list of algorithms that can be specified is as follows:

AES_128,AES_192,AES_256,TRIPLE_DES_3KEY,NO_ENCRYPTION

Two: backup set option

These options operate on the backup set created by this backup operation.

(1) COPY_ONLY

Scope of application:

SQL Server and SQL database managed instances.

Specifies that the backup is replication only, which does not affect the normal backup order.

Replication-only backups are created independently of regular backups that are scheduled on a regular basis.

Replicating backups only does not affect the overall backup and restore process of the database.

Copy-only backups should be used when backups are made for special purposes, such as backing up logs before online file restore.

Typically, replication log backups are deleted after they are used only once.

"when used with BACKUP DATABASE, a full backup created by the COPY_ONLY option cannot be used as a differential base."

Differential bitmaps are not updated, so differential backups behave as if only replication backups do not exist.

Subsequent differential backups use the latest regular full backups as their benchmark.

If you use DIFFERENTIAL with COPY_ONLY, ignore COPY_ONLY and create a differential backup.

When used with BACKUP LOG, the COPY_ONLY option creates a copy log backup only, which does not truncate the transaction log.

Replicating only log backups has no effect on the log chain, so other log backups behave as if only replication backups do not exist.

(2) {COMPRESSION | NO_COMPRESSION}

For SQL Server 2008 Enterprise and later only

Specifies whether to perform backup compression on this backup, overriding the server-level default settings.

During installation, the default behavior is no backup compression.

However, this default setting can be changed by setting the backup compression default server configuration option.

For information about viewing the current value of this option, see View or change the Server Properties Panel (SQL Server).

COMPRESSION

Explicitly enable backup compression.

NO_COMPRESSION

Explicitly disable backup compression.

(3) DESCRIPTION = {'text' | @ text_variable} *

Specifies free-form text that describes the backup set. The string can be up to 255 characters long.

(4) 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.

(5) {EXPIREDATE = 'date' | RETAINDAYS = days}

Specifies the date on which the backup set for this backup is allowed to be overwritten.

If you use both options, RETAINDAYS takes precedence over EXPIREDATE.

If neither of these options is specified, the expiration date is determined by the mediaretention configuration settings.

For more information, see the maximum number of worker threads automatically configured by the combination of the server configuration options (SQL Server) version.

(6) RETAINDAYS = {days | @ days_var}

Specify how many geniuses must pass to overwrite this backup media set. If provided as a variable (@ days_var), it must be specified as an integer.

Three: media set option

These options operate on the media set as a whole.

(1) {NOINIT | INIT}

Controls whether backup operations are appended or overwritten to existing backup sets in the backup media. The default is the latest backup set (NOINIT) appended to the media.

NOINIT

Indicates that the backup set will be appended to the specified media set to retain the existing backup set. If a media password is defined for the media set, you must provide a password. NOINIT is the default setting.

INIT

Specifies that all backup sets should be overwritten, but the media headers are retained.

"if INIT is specified, all existing backup sets on the device are overwritten, if conditions permit."

By default, BACKUP checks for the following conditions, and if any of them exist, the backup media is not overwritten:

None of the backup sets have expired. For more information, see EXPIREDATE and RETAINDAYS options

If the backup set name is given in the BACKUP statement, the backup set name does not match the name on the backup media. For more information, see the NAME options described earlier in this section.

(2) {NOSKIP | SKIP}

Controls whether backup operations check the expiration date and time of backup sets in the media before they are overwritten.

NOSKIP

Instructs the BACKUP statement to check the expiration date of all backup sets on the media before they can be overwritten. This is the default behavior.

SKIP

Disables the expiration and name checks of backup sets, which are typically performed by BACKUP statements to prevent backup sets from being overwritten.

(3) {NOFORMAT | FORMAT}

Specifies whether media headers should be written on the volume used for this backup operation to overwrite any existing media headers and backup sets.

NOFORMAT

Specifies that the backup operation retains the existing media headers and backup sets on the media volume used for this backup operation. This is the default behavior.

FORMAT

Specifies that a new media set is created. FORMAT causes the backup operation to write a new media header on all media volumes used for the backup operation.

The existing contents of the volume become invalid because any existing media headers and backup sets are overwritten.

important

Please use FORMAT carefully. Any volume that formats the media set will make the entire media set unavailable. For example, if you initialize a single tape in an existing stripe media set, the entire media set becomes unavailable.

Specifying FORMAT means that SKIP;SKIP does not need to be explicitly declared.

(4) MEDIADESCRIPTION = {text | @ text_variable}

Specifies a free-form text description of the media set, up to 255 characters.

(5) MEDIANAME = {media_name | @ media_name_variable}

Specifies the media name for the entire backup media set.

The media name cannot be more than 128characters long, and if MEDIANAME is specified, it must match the previously specified media name that already exists on the backup volume.

If this option is not specified or the SKIP option is specified, no verification check is performed on the media name.

(6) BLOCKSIZE = {blocksize | @ blocksize_variable}

Specifies the size of the physical block in bytes.

Supported sizes are 512, 1024, 2048, 4096, 8192, 16384, 32768 and 65536 (64 KB) bytes.

The default is 65536 for tape devices and 512 for other cases.

In general, this option is not required because BACKUP automatically selects the appropriate block size for the device. Explicitly declaring block size overrides automatic block size selection.

"if you want to make a backup that is scheduled for replication and restore on CD-ROM, specify BLOCKSIZE=2048."

Typically, this option affects performance only when writing to a tape device.

Four: data transfer options

BUFFERCOUNT = {buffercount | @ buffercount_variable}

Specifies the total number of Ithumb O buffers used for backup operations. You can specify any positive integer; however, a large number of buffers can cause a "out of memory" error due to insufficient virtual address space in the Sqlservr.exe process.

The total space used by the buffer is determined by the following: buffercount/maxtransfersize

(2) MAXTRANSFERSIZE = {maxtransfersize | @ * maxtransfersize_variable*}

Specifies the maximum transmission unit (bytes) to be used between the SQL Server and the backup media.

Possible values are multiples of 65536 bytes (64 KB), up to 4194304 bytes (4 MB).

Five: error management options

Use these options to determine whether the backup checksum is enabled for the backup operation and whether the backup operation stops when an error is encountered.

(1) {NO_CHECKSUM | CHECKSUM}

Controls whether backup checksum is enabled.

NO_CHECKSUM

Explicitly disables the generation of backup checksums (and the validation of page checksums). This is the default behavior.

CHECKSUM

If this option is enabled and available, the specified backup operation verifies the checksum and page imperfections of each page and generates a checksum of the entire backup.

Using backup checksums may affect workloads and backup throughput.

(3) {STOP_ON_ERROR | CONTINUE_AFTER_ERROR}

Controls whether the backup operation stops or continues after a page checksum error is encountered.

STOP_ON_ERROR

If the page checksum is not validated, it indicates that BACKUP failed. This is the default behavior.

CONTINUE_AFTER_ERROR

Instructs BACKUP to continue execution, regardless of whether an error such as an invalid checksum or page tearing is encountered.

If you cannot use the NO_TRUNCATE option to back up the tail of the log when the database is corrupted, you can try to perform a tail-log backup by specifying CONTINUE_AFTER_ERROR instead of NO_TRUNCATE.

Six: compatibility options

RESTART

It doesn't work since SQL Server 2008. This version accepts this option to be compatible with older versions of SQL Server.

Seven: monitoring option

STATS [= percentage]

A message is displayed whenever another percentage is complete and is used to measure progress. If the percentage is omitted, SQL Server displays a message for every 10% of completion.

The STATS option reports the percentage of completion until the threshold for the next interval is reported.

This is an approximation of the specified percentage; for example, when STATS=10, this option may display 43% if the completion progress is 40%.

For larger backup sets, this is not a problem because the percentage complete changes very slowly between completed Imax O calls.

Eight: tape option

Scope of application: SQL Server

These options are available only for TAPE devices. If you are using a non-tape device, these options are ignored.

{REWIND | NOREWIND}

REWIND scope of application: SQL Server. Specifies that the SQL Server releases and rewinds the tape. REWIND is the default setting.

NOREWIND scope of application: SQL Server.

Specifies that SQL Server keeps the tape open after the backup operation. You can use this option to help improve performance when performing multiple backup operations on tape.

NOREWIND contains NOUNLOAD, and these options are not compatible in a single BACKUP statement.

{UNLOAD | NOUNLOAD}

Scope of application: SQL Server

UNLOAD scope of application: SQL Server

Specifies that the tape is automatically rewound and unmounted after the backup is complete. UNLOAD is the default at the beginning of the session.

NOUNLOAD scope: SQL Server, which specifies that the tape continues to load in the tape drive after the BACKUP operation.

Nine: log-specific options

Scope of application: SQL Server

These options are used only with BACKUP LOG.

(1) {NORECOVERY | STANDBY = undo_file_name}

NORECOVERY scope of application: SQL Server

Back up the tail of the log and put the database in the RESTORING state. NORECOVERY is useful when failing over to a secondary database or saving the tail of the log before performing a RESTORE operation.

To perform maximum log backups (skip log truncation) and automatically place the database in the RESTORING state, use both the NO_TRUNCATE and NORECOVERY options.

(2) STANDBY = standby_file_name

Scope of application: SQL Server*

Back up the tail of the log and make the database read-only and STANDBY.

Write the STANDBY clause to alternate data (perform a rollback, but with a further restore option).

Using the STANDBY option is equivalent to BACKUP LOG WITH NORECOVERY followed by RESTORE WITH STANDBY.

(4) NO_TRUNCATE

Scope of application: SQL Server

Specifies that the log is not truncated and causes the database engine to attempt to perform a backup, regardless of the state of the database.

As a result, backups performed with NO_TRUNCATE may have incomplete metadata. This option allows you to back up the log if the database is corrupted.

NO_TRUNCATE

Scope of application: SQL Server

Specifies that the log is not truncated and causes the database engine to attempt to perform a backup, regardless of the state of the database.

As a result, backups performed with NO_TRUNCATE may have incomplete metadata. This option allows you to back up the log if the database is corrupted.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.