Fully implement the SQL Server image Function

I have not configured any 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 SQLServer, 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 SQLServer2005.

First configure the host

Run the following SQL statement on the host:

Copy codeThe Code is as follows:
-- Create a master key for a host database
USE master;
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
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
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
Copy codeThe Code is as follows:
-- 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.

Next, we will completely back up the Test database on server.
Copy codeThe Code is as follows:
-- 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.

Complete welcome database on server B

There are many problems here. One by one.

If we directly execute the following SQL statement.

Copy codeThe Code is as follows:
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
[Code]
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 restored device. This ensures that the backup file is changed to the database.
[Code]
Exec sp_addumpdevice 'disk', 'test _ backup ',
'E: \ backup \ Test. Bak'
Exec sp_addumpdevice 'disk', 'test _ log_backup ',
'E: \ backup \ Test_log.bak'
Go

After the restoration is successful, we can perform the restoration.
Copy codeThe Code is as follows:
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 assign it to the file.
Note that NORECOVERY must be specified for the first full recovery.
So far, all preparations have been completed. We have started to execute the image.
Run
Alter database Test set partner = 'tcp: // 192.168.10.2: 8080 ';
Run the command on the host.
Alter database Test set partner = 'tcp: // 192.168.10.2: 8080 ';
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
Copy codeThe Code is as follows:
USE MASTER
Go
Alter database Test SET PARTNER FAILOVER
Go

Summary:

If you go abroad during the image creation process, you must delete the deleted items when performing the re-execution.

-- 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

Copy codeThe Code is as follows:
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

Refer:
Http://msdn.microsoft.com/zh-cn/library/ms187495 (v = SQL .90). aspx
Http://msdn.microsoft.com/zh-cn/library/ms187014.aspx
Http://msdn.microsoft.com/zh-cn/library/ms186289.aspx