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

All-sql-about_bak_restore (1):

# full backup

Backup database record to disk ='E:\ backup\ record.bak' with retaindays=9,format,init,compression

# differential backup

Backup database record to disk='E:\ backup\ record.bak' with differential,retaindays=9,noformat,noinit,compression

# Log backup

Backup log record to disk ='E:\ backup\ record.bak' with retaindays=9,compression

Before [restore] runs, change the database [multi-user] mode to [single-user] mode:

Right-click the database "Properties"-"options"-"status"-"restrict access"-change "MULTI_USER" to "SINGLE_USER" to determine

Execute the following command:

# backup tail log (used before full recovery of the database to make the database in a restored state)

Use master;backup log record to disk ='E:\ backup\ log.bak' with norecovery,format,init,compression

# View backup information in backup media

Restore headeronly from disk='E:\ backup\ record.bak'

# list the file information contained in the backup set

Restore filelistonly from disk='E:\ backup\ record.bak'

# restore backup sets one by one, and restore the database to a specified point in time

Restore database test from disk ='E:\ backup\ record.bak' with file=1,norecovery

Restore database test from disk ='E:\ backup\ record.bak' with file=2,norecovery,stopat='2017-01-12 05VG 39RH 31'

Restore log test from disk ='E:\ backup\ record.bak' with file=3,norecovery,stopat='2017-01-12 05VG 39RH 31'

Restore database record with recovery

# backup tail log (used before full recovery of the database to make the database in a restored state)

Backup log record to disk ='E:\ backup\ log.bak' with norecovery,format,init,compression

-- empty backup records

Use msdb

# clear backup records before the specified point in time

Exec sp_delete_backuphistory '2016-1-11'

# empty the backup record of the specified database

Exec sp_delete_database_backuphistory 'TEST'

All-sql-about_bak_restore (2):

# full backup

Backup database record to disk ='E:\ backup\ record.bak' with retaindays=9,format,init,compression

# differential backup

Backup database record to disk='E:\ backup\ record.bak' with differential,retaindays=9,noformat,noinit,compression

# Log backup

Backup log record to disk ='E:\ backup\ record.bak' with retaindays=9,compression

Before [restore] runs, change the database [multi-user] mode to [single-user] mode:

Right-click the database "Properties"-"options"-"status"-"restrict access"-change "MULTI_USER" to "SINGLE_USER" to determine

Execute the following command:

# backup tail log (used before full recovery of the database to make the database in a restored state)

Use master;backup log record to disk ='E:\ backup\ log.bak' with norecovery,format,init,compression

# View backup information in backup media

Restore headeronly from disk='E:\ backup\ record.bak'

# list the file information contained in the backup set

Restore filelistonly from disk='E:\ backup\ record.bak'

# restore backup sets one by one, and restore the database to a specified point in time

Restore database test from disk ='E:\ backup\ record.bak' with file=1,norecovery

Restore database test from disk ='E:\ backup\ record.bak' with file=2,norecovery,stopat='2017-01-12 05VG 39RH 31'

Restore log test from disk ='E:\ backup\ record.bak' with file=3,norecovery,stopat='2017-01-12 05VG 39RH 31'

Restore database record with recovery

# backup tail log (used before full recovery of the database to make the database in a restored state)

Backup log record to disk ='E:\ backup\ log.bak' with norecovery,format,init,compression

-- empty backup records

Use msdb

# clear backup records before the specified point in time

Exec sp_delete_backuphistory '2016-1-11'

# empty the backup record of the specified database

Exec sp_delete_database_backuphistory 'TEST'

All-sql-about_bak_restore (3):

BACKUP DATABASE [timeRecord] TO DISK = NumC:\ Program Files\ Microsoft SQL Server\ MSSQL12.FANTASY\ MSSQL\ Backup\ timeRecord.bak' WITH RETAINDAYS = 9, NOFORMAT, NOINIT, NAME = NumbertimeRecord-full database backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM

GO

BACKUP DATABASE [timeRecord] TO DISK = NumC:\ Program Files\ Microsoft SQL Server\ MSSQL12.FANTASY\ MSSQL\ Backup\ timeRecord_diff.bak' WITH DIFFERENTIAL, RETAINDAYS = 9, NOFORMAT, NOINIT, NAME = NationtimeRecord-differential database backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

GO

BACKUP LOG [timeRecord] TO DISK ='C:\ Program Files\ Microsoft SQL Server\ MSSQL12.FANTASY\ MSSQL\ Backup\ timeRecord_log.bak' WITH DESCRIPTION = 'log backup', RETAINDAYS = 7, FORMAT, INIT, NAME = 'timeRecord- log backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

GO

C:\ Program Files\ Microsoft SQL Server\ MSSQL10_50.MSSQLSERVER\ MSSQL\ Backup

= =

BACKUP DATABASE [timeRecord] TO DISK = NumC:\ Program Files\ Microsoft SQL Server\ MSSQL12.FANTASY\ MSSQL\ Backup\ timeRecord_diff.bak'

WITH RETAINDAYS = 9, NOFORMAT, NOINIT, COMPRESSION, DIFFERENTIAL

GO

=

# full backup

Backup database record to disk ='E:\ backup\ record.bak' with retaindays=9,format,init,compression

# differential backup

Backup database record to disk='E:\ backup\ record.bak' with differential,retaindays=9,noformat,noinit,compression

# Log backup

Backup log record to disk ='E:\ backup\ record.bak' with retaindays=9,compression

# View backup information in backup media

Restore headeronly from disk='E:\ backup\ record.bak'

# list the file information contained in the backup set

Restore filelistonly from disk='E:\ backup\ record.bak'

= =

The 12th:

Backup database zhaotest to disk ='C:\ mssql\ backup\ zhaotest.all.1.bak'

With retaindays=9,format,init,compression

Backup database zhaotest to disk='C:\ mssql\ backup\ zhaotest.all.2.bak'

With differential,retaindays=9,noformat,noinit,compression

Backup log zhaotest to disk ='C:\ mssql\ backup\ zhaotest.all.3.bak'

With retaindays=9,compression

=

Backup database zhaotest to disk ='C:\ mssql\ backup\ zhaotest.bak'

With retaindays=9,format,init,compression

Backup database zhaotest to disk='C:\ mssql\ backup\ zhaotest.all.2.bak'

With differential,retaindays=9,noformat,noinit,compression

Backup log zhaotest to disk ='C:\ mssql\ backup\ zhaotest.all.4.bak'

With retaindays=9,compression

Restore database zhaotest from disk= 'C:\ mssql\ backup\ zhaotest.all.3.bak'

With file=1,norecovery

Restore database zhaotest from disk='C:\ MSSQL\ Backup\ zhaotest.all.2.bak'

With file=2,norecovery,stopat='2017-01-12 121V 231'

Restore log zhaotest from disk='C:\ MSSQL\ Backup\ zhaotest.all.3.bak'

With file=3,norecovery,stopat='2017-01-12 19FrV 231R 21'

Restore database zhaotest with recovery

All-sql-about_bak_restore (4):

Experiment 1 SQLserver full backup, differential backup, log backup and restore to a specified point in time

Example:

Backup (requires that the data be updated to the database before each backup)

Restore to a specified point in time

It is required to take a screenshot of the operation steps and post them below (at least include the steps of successful backup and recovery). If the recovery encounters the problem that the database is occupied, please refer to the appendix to solve the problem.

Appendix:

The following problems were encountered in restoring the sql server database

The problem is mainly caused by the fact that there are other processes connected to it when you restore, resulting in a lack of monopoly.

The solution is as follows:

Steps

USE Master

ALTER DATABASE database name SET OFFLINE WITH ROLLBACK IMMEDIATE

If the restore still does not work at this time. It may be that a new process is connected immediately after deleting the process, resulting in failure all the time. The application keeps linking to the database.

At this point, it can be restored under a single user.

Setting method: select the database to be restored-- > Properties-- > options-- > restrict access

The value is changed from MULTI_USER to SINGLE_USER, and the database is displayed in single-user mode

This is the mode of GUI, and the method of statement is relatively simple.

USE MASTER

Go

ALTER DATABASE database name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

You must have a super user to set up a single-user database

Or use the

Delete the processes attached to it, roll back the uncommitted transactions, and then restore.

1. Query the database ID to be restored

Select * from master..sysdatabases where name =''

two。 The process of getting the database

Select * from sys.sysprocesses a where a.dbid =''

3. Kill the process connected to it

Kill @ spid

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.