Fully implement the SQL Server image Function

After one day of tossing the SQL Server image, I finally got some results. Now I have shared some of the problems I have encountered on the Internet. Now try to share all the problems encountered.

I did not configure the results in the domain environment. Maybe it is the reason for the domain user. Because I did it in the production environment, changing the domain user requires restarting the SQL Server, so this method is abandoned, only certificates can be used.

Environment:

HOST: 192.168.10.2 (Code)

Image: 192.168.10.1 (codenamed B for convenience)

(I have not witnessed the server with limited conditions .) Both servers use SQL Server 2005

First configure the host

Run the following SQL statement on the host:

 
 

  
  
Create master key encryption by password = 'Password ';
  
  
GO
  
  
-- Create a certificate for the database instance on 10.2
  
  
Create certificate As_A_cert
  
  
With subject = 'as _ A_cert ',
  
  
START_DATE = '2014/1/123 ',
  
  
EXPIRY_DATE = '2017/123 ';
  
  
GO
  
  
-- Use the certificate created above to create an image endpoint for the database instance on 10.2
  
  
Create endpoint Endpoint_As
  
  
STATE = STARTED
  
  
As tcp (
  
  
LISTENER_PORT = 5022,
  
  
LISTENER_IP = ALL
  
  
)
  
  
FOR database_grouping ing (
  
  
AUTHENTICATION = CERTIFICATE As_A_cert,
  
  
ENCRYPTION = required algorithm RC4,
  
  
ROLE = ALL
  
  
);
  
  
GO
 
 

Note: Pay attention to setting the Image Port of the database. 5022.

 
 

  
  
-- Back up the certificate on 10.2 and copy it to 10.1
  
  
Backup certificate As_A_cert to file = 'd: \ As_A_cert.cer ';
  
  
GO
 
 

Note: Back up Certificate A and copy certificate A to image server B.

Configure the backup storage

 
 

  
  
USE master;
  
  
Create master key encryption by password = 'Password ';
  
  
GO
  
  
-- Create a certificate for the database instance on 10.1 B
  
  
Create certificate As_ B _cert
  
  
With subject = 'as _ B _cert ',
  
  
START_DATE = '2014/1/123 ',
  
  
EXPIRY_DATE = '2017/123 ';
  
  
GO
  
  
-- Use the certificate created above on 10.1 B to create an image endpoint for the database instance
  
  
Create endpoint Endpoint_As
  
  
STATE = STARTED
  
  
As tcp (
  
  
Listener_ports = 5022
  
  
, LISTENER_IP = ALL
  
  
)
  
  
FOR database_grouping ing (
  
  
AUTHENTICATION = CERTIFICATE As_ B _cert
  
  
, ENCRYPTION = REQUIRED ALGORITHM AES
  
  
, ROLE = ALL
  
  
);
  
  
GO
  
  
-- Back up the certificate on 10.1 B and copy it to 10.2.
  
  
Backup certificate As_ B _cert to file = 'd: \ As_ B _cert.cer ';
  
  
GO
 
 

Copy the backed up certificate B to server.

Create an account for image Login

Run

 
 

  
  
-- Exchange certificates,
  
  
-- Synchronize Login
  
  
Create login B _login WITH PASSWORD = 'Password ';
  
  
 
  
  
Create user B _user for login B _login;
  
  
 
  
  
Create certificate As_ B _cert AUTHORIZATION B _user from file = 'd: \ As_ B _cert.cer ';
  
  
 
  
  
Grant connect on endpoint: Endpoint_Bs TO [B _login];
 
 

Execute on B

 
 

  
  
-- Exchange certificates,
  
  
-- Synchronize Login
  
  
Create login A_login with password = 'Password ';
  
  
 
  
  
Create user A_user for login A_login;
  
  
 
  
  
Create certificate As_A_cert AUTHORIZATION A_user from file = 'd: \ As_A_cert.cer ';
  
  
 
  
  
Grant connect on endpoint: Endpoint_As TO [A_login];
 
 

Remember that port 5022 of the two servers is not occupied and the two servers can be connected.

There will be no problem in subsequent steps, and the image is half done.

NextFull backupTest Database on server

 
 

  
  
-- Complete backup of the host
  
  
USE master;
  
  
Alter database Test set recovery full;
  
  
GO
  
  
Backup database Test
  
  
To disk = 'd: \ SQLServerBackups \ Test. bak'
  
  
With format;
  
  
GO
  
  
Backup log Test to disk = 'd: \ SQLServerBackups \ Test. bak ';
  
  
GO
  
  
-- Copy the backup file to B.
 
 

Make sure to complete the backup.

Completely restore the database on server B

There are many problems here. One by one.

If we directly execute the following SQL statement.

 
 

  
  
RESTORE DATABASE Test  
  
  
    FROM DISK = 'D:\Back\Test.bak'  
  
  
    WITH NORECOVERY  
  
  
GO  
  
  
RESTORE LOG Test  
  
  
    FROM DISK = 'D:\Back\Test_log.bak'  
  
  
    WITH FILE=1, NORECOVERY  
  
  
GO 
 
 

May report:

Message 3154, level 16, status 4, 1st rows
The database backup in the backup set is different from the existing 'test' database.
Message 3013, level 16, state 1, 1st rows

It may be because the backup set names of the two databases are different. The reason for finding the backup set for half a day is unsuccessful. Therefore, use the sp_addumpdevice method below.

Use sp_addumpdevice to create a restoration device. This ensures that the backup file belongs to the database.

 
 

  
  
exec sp_addumpdevice 'disk','Test_backup', 
  
  
        'E:\backup\Test.bak' 
  
  
exec sp_addumpdevice 'disk','Test_log_backup', 
  
  
        'E:\backup\Test_log.bak' 
  
  
go 
 
 

After successful execution, we will executeRecovery completed

 
 

  
  
RESTORE DATABASE Test  
  
  
  FROM Test_backup 
  
  
  WITH DBO_ONLY,  
  
  
    NORECOVERY,STATS; 
  
  
go 
  
  
RESTORE LOG Test  
  
  
  FROM Test_log_backup 
  
  
  WITH file=1, 
  
  
    NORECOVERY; 
  
  
GO 
 
 

If the database has been backed up multiple times, multiple backup sets will be generated. Therefore, the file here cannot be specified as 1.

This error may be:

Message 4326, level 16, state 1, 1st rows
Logs in this backup set are terminated at LSN 36000000014300001, which is too early to be applied to the database.

. You can restore a new log backup that contains LSN 36000000018400001.

You can use this statement to query the backup set of the backup file.

Restore headeronly from disk = 'e: \ backup \ Test_log.bak'

Find the last sequence number and specify it to the file.

Note that NORECOVERY must be specified for the first full recovery.

So far, all preparations have been completed. We have enabled the image.

Run

 
 

  
  
ALTER DATABASE Test SET PARTNER = 'TCP://192.168.10.2:5022'; 
 
 

Run the command on the host.

 
 

  
  
ALTER DATABASE Test SET PARTNER = 'TCP://192.168.10.2:5022'; 
 
 

In this way, the images of the two servers are synchronized.

Delete image:

 
 

  
  
ALTER DATABASE Test SET PARTNER OFF 
 
 

If a problem occurs on the host, run

 
 

  
  
USE MASTER  
  
  
 
  
  
Go  
  
  
 
  
  
ALTER DATABASE Test SET PARTNER FAILOVER  
  
  
 
  
  
Go 
 
 

Summary:

If there is a problem with the process during image creation, you must delete the deleted items when you need to re-execute them.

 
 

  
  
-- Query Images
  
  
Select * from sys. endpoints
  
  
-- Delete a port
  
  
Drop endpoint Endpoint_As
  
  
-- Query the certificate
  
  
Select * from sys. tricric_keys
  
  
-- Delete the certificate. Delete the certificate before deleting the primary key.
  
  
Drop certificate As_A_cert
  
  
-- Delete a primary key
  
  
DROP MASTER KEY
  
  
-- Delete an image
  
  
Alter database <dbname> set partner off
  
  
-- Delete Login Name
  
  
Drop login <login_name>
 
 

Sp_addumpdevice syntax

 
 

  
  
Sp_addumpdevice [@ devtype =] 'device _ type'
  
  
 
  
  
, [@ Logicalname =] 'logical _ name'
  
  
 
  
  
, [@ Physicalname =] 'physical _ name'
  
  
 
  
  
]
  
  
The parameters include:
  
  
@ Devtype: device type. The supported values are disk and tape, where disk is the disk file and tape is
  
  
Any tape device supported by windows.
  
  
@ Logicalname: Logical name of the backup device.
  
  
@ Physicalname: Physical name and path of the Backup Device