SQL Server uses logs to restore the database

SQL Server uses logs to restore the database

A full backup + the latest log backup is required.

 

* -- Description:
Report data changes in the primary database to the standby database in a timely manner.
The data of the standby database can be used for query at any time, but cannot be updated (the standby database is read-only ).
--*/

-- First, create a database for demonstration (master database)

1. Create Database db_test
2. On
3. (Name = db_test_data,
4. Filename = 'C:/db_test.mdf ')
5. Log On
6. (Name = db_test_log,
7. Filename = 'C:/db_test.ldf ')
8. Go

-- Back up the database

1. Backup database db_test to disk = 'C:/test_data.bak 'with format
2. Go

-- Restore the database to a backup database (demonstrate synchronization between the master database and the backup database)

1. Restore database db_test_bak from disk = 'C:/test_data.bak'
2. With replace, standby = 'C:/db_test_bak.ldf'
3. , Move 'db _ test_data 'to 'C:/db_test_data.mdf'
4. , Move 'db _ test_log 'to 'C:/db_test_log.ldf'
5. Go

-- Start the SQL Agent service

1. Exec master.. xp_cmdshell 'net start sqlserveragent', no_output
2. Go

-- Create jobs for synchronization between master server data training and slave server database

1. Declare @ jogid uniqueidentifier
2. Exec MSDB .. sp_add_job
3. @ Job_id = @ jogid output,
4. @ Job_name = n' Data Synchronization Process'

-- Create synchronization process steps

1. Exec MSDB .. sp_add_jobstep
2. @ Job_id = @ jogid,
3. @ Step_name = n' data synchronization ',
4. @ Subsystem = 'tsql ',
5. @ Command = N'

-- Log backup in the primary database

1. Backup log db_test to disk = ''c:/test_log.bak ''with format

-- Restore the log backup of the master database in the backup database (the latest changes in the application master database)
-- In practice, the restoration jobs of the backup and backup databases of the primary database should be created on the primary server and the backup server respectively, and the backup files should be stored in the shared directory that can be accessed by both the primary server and the backup server.

1. Restore log db_test_bak from disk = ''c:/test_log.bak ''with standby = ''c:/test_log.ldf ''',
2. @ Retry_attempts = 5,
3. @ Retry_interval = 5

-- Create Scheduling (executed every minute)

1. Exec MSDB .. sp_add_jobschedule
2. @ Job_id = @ jogid,
3. @ Name = n' schedule ',
4. @ Freq_type = 4,
5. @ Freq_interval = 1,
6. @ Freq_subday_type = 0x4,
7. @ Freq_subday_interval = 1,
8. @ Freq_recurrence_factor = 1

-- Add the target server

1. Exec MSDB. DBO. sp_add_jobserver
2. @ Job_id = @ jogid,
3. @ SERVER_NAME = n' (local )'
4. Go

-- Through the above processing, the synchronization relationship between the master database and the slave database has been set.

Next, we will test whether synchronization can be implemented.

 

-- Create a test table in the primary database

1. Create Table db_test.dbo.tb_test (id int)
2. Go

-- Wait 1 minute 30 seconds (because the synchronization interval is set to 1 minute, so the latency is required to see the effect)

1. Waitfor delay '00: 01: 30'
2. Go

-- Query the slave database to see if the synchronization is successful.

1. Select * From db_test_bak.dbo.tb_test

/* -- Result:
ID
-----------

 

(The number of affected rows is 0)
--*/

-- Test successful
Go

-- Delete all tests

1. Drop database db_test, db_test_bak
2. Exec MSDB .. sp_delete_job @ job_name = n' Data Synchronization Process'
3. Go

/* ===================================================== ===================================== */

 

/* -- Server File Server handling instructions
This method is used to synchronize databases. When the primary database is unavailable (for example, the primary database is damaged or shut down for repair)
You can use either of the following methods to make the standby database available.
--*/

-- 1. If the primary database is damaged and the latest logs cannot be backed up, you can directly use the following statement to make the standby database readable and writable (loss of all data after the last log restoration ).
-- Restore log db_test_bak with recovery

-- 2. If the primary database can back up the latest logs, you can use the following statement.
-- First back up the latest transaction logs of the primary database
-- Backup log db_test to disk = ''c:/test_log.bak ''with format
-- Restore the latest transaction logs in the slave database and make the slave database readable and writable (upgraded to the master database)
-- Restore log db_test_bak from disk = 'C:/test_log.bak'

 

 

Simply put:
1. You must use the specified Windows user to log on to your SQL Service, instead of using the "local SYSTEM account"
2. users logging on to the SQL service must have all permissions on the shared directory.
3. If your computer is not added to the domain, you must also ensure that the login users set for the SQL service on the source and target servers are the same (the user names and passwords are the same)

 

Network Backup is mainly about permission settings. refer to the following Backup File Sharing directory permission settings to solve directory sharing permissions.

The following assumes the permission settings for the shared directory on which the database on server a is backed up to server B (the two servers should be in the LAN and allow shared directory access )::

1. Machine A and machine B create a Windows user with the same name. Set the user group to "Administrators" and set the same password as the valid user accessing the backup folder. The operation is as follows:
My computer
-- Control Panel
-- Management Tools
-- Computer Management
-- Users and groups
-- Right-click the user
-- Create a user
-- Create a Windows login user affiliated to the Administrator Group

2. Create a new shared directory on the B server as the directory for storing backup files:
My computer -- D:/creates a directory named Bak
-- Right-click the newly created directory
-- Property -- share
-- Select "share this folder"
-- Use the "permission" button to set specific user permissions to ensure that the user created in step 1 has all permissions on the folder
-- OK

3. Set the start user of the MSSQLServer and SQLServerAgent services
Start -- program -- management tool -- service
-- Right-click MSSQLServer
-- Property -- login -- select "this account"
-- Enter or select the Windows logon user name created in step 1.
-- Enter the user's password in "password"
-- OK
-- Set SQLServerAgent in the same way

4. Map the Bak directory of machine B on machine

5. Run the following statement in the analyzer to check whether the statement is successful:
Exec master.. xp_mongoshell 'Drive letter mapped to dir'

6. Prepare a backup plan on server

Note: to create a new user, the startup account of the MSSQLServer service must have the same name and password as the valid access to the shared directory, in this way, you can pass the verification (so you can use another valid user instead, only the user name and password must be the same and have sufficient permissions)

 

 

 

Let's look at this example.

SQL code -- example of how to restore data to a specified time point
-- Create a Test Database
Create Database DB
Go

-- Back up the database
Backup database dB to disk = 'C:/DB. Bak' with format
Go

-- Create a test table
Create Table dB. DBO. tb_test (id int)

-- Delay 1 second, and then perform subsequent operations (this is because the SQL server has a time precision of up to 3% seconds. If there is no delay, the Restoration Operation to the time point may fail)
Waitfor delay '00: 00: 01'
Go

-- Assume that the table dB. DBO. tb_test is deleted by mistake.
Drop table dB. DBO. tb_test

-- Save the time when the table was deleted
Select dt = getdate () #
Go

-- After the delete operation, the database. DBO. tb_test table cannot be deleted.

-- The following shows how to restore the accidentally deleted table dB. DBO. tb_test

-- First, back up transaction logs (transaction logs can be restored to the specified time point)
Backup log dB to disk = 'C:/db_log.bak 'with format
Go

-- Next, we need to restore the full backup first (the restoration log must be performed on the basis of the full backup)
Restore database DB from disk = 'C:/DB. Bak' with replace, norecovery
Go

-- Restore the transaction log to before the delete operation (the time here corresponds to the deletion time above, and is earlier than the deletion time
Declare @ DT datetime
Select @ dt = dateadd (MS,-20, DT) from # -- get a time earlier than the time when the table was deleted
Restore log DB from disk = 'C:/db_log.bak 'with recovery, stopat = @ dt
Go

-- Check whether the table is restored.
Select * from DB. DBO. tb_test

/* -- Result:
ID
-----------

(The number of affected rows is 0)
--*/

-- Test successful
Go

-- Delete the test environment.
DROP DATABASE DB
Drop table #

How can I use the log restoration function?