1. 简介
当公司需要更换数据库时，数据迁移是非常重要的环节。本文章将介绍MSSQL数据库迁移中的25个必须知道的关键点，帮助您更好地完成数据迁移工作。
2. 关键点
2.1 数据库备份
MSSQL数据库备份是迁移的重要准备工作。备份可以保证数据的完整性，并避免数据丢失。在备份中应注意以下几点：
备份应在非高峰期进行，以避免备份过程中对系统性能的影响。
备份文件应存储在安全可靠的地方。如果备份文件丢失，迁移工作将无法继续进行。
备份文件应根据时间顺序进行命名和存储，以便后续查找和使用。
以下是数据库备份命令的示例代码：
USE master;
GO
BACKUP DATABASE AdventureWorks2012
TO DISK = 'D:\Backups\AdventureWorks2012.bak';
GO
2.2 目标数据库
在数据库迁移工作中，目标数据库是非常重要的一环。以下是一些关于目标数据库的注意事项：
目标数据库应支持与当前数据库相同的数据类型和结构。
目标数据库中应创建与当前数据库相同的用户和角色。
在目标服务器上运行的数据库引擎的版本应与当前服务器上的版本兼容。
以下是创建目标数据库的示例代码：
CREATE DATABASE [AdventureWorks2012_Copy]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'AdventureWorks2012_Copy', FILENAME = N'D:\Data\AdventureWorks2012_Copy.mdf' , SIZE = 3221225472KB , FILEGROWTH = 262144KB )
LOG ON
( NAME = N'AdventureWorks2012_Copy_log', FILENAME = N'L:\Logs\AdventureWorks2012_Copy_log.ldf' , SIZE = 5242880KB , FILEGROWTH = 262144KB )
GO
2.3 数据库恢复模型
数据库恢复模型指定数据库故障后如何恢复。在迁移数据库时应根据需要选择合适的恢复模型。以下是一些常见的恢复模型：
简单恢复模型：数据只能从最近的完整或差异备份中恢复。此模型适合不需要频繁备份的数据库。
完全恢复模型：除了完整或差异备份，还需要进行日志备份以进行点到点的恢复。此模型适合对数据完整性要求高且需要频繁备份的数据库。
大容量恢复模型：类似于简单恢复模型，但支持更大的事务日志。此模型适合需要更多日志备份的数据库。
以下示例代码展示如何更改现有数据库的恢复模型：
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL
GO
2.4 数据转移
数据转移是将数据从源数据库复制到目标数据库的过程。以下是一些关于数据转移的注意事项：
数据转移应在非高峰期进行，以避免对系统性能的影响。
在进行数据转移之前，应确保数据类型和结构在源数据库和目标数据库之间是一致的。
在进行数据转移之后，应对数据完整性进行验证。
以下示例代码展示了将表从源数据库转移到目标数据库的过程：
USE [AdventureWorks2012_Copy]
GO
CREATE TABLE [HumanResources].[Employee](
[BusinessEntityID] [int] NOT NULL,
[NationalIDNumber] [nvarchar](15) NOT NULL,
[LoginID] [nvarchar](256) NOT NULL,
[BirthDate] [date] NOT NULL,
[MaritalStatus] [nchar](1) NOT NULL,
[Gender] [nchar](1) NOT NULL,
[HireDate] [date] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_Employee_BusinessEntityID] PRIMARY KEY CLUSTERED
(
[BusinessEntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO AdventureWorks2012_Copy.HumanResources.Employee SELECT * FROM AdventureWorks2012.HumanResources.Employee
GO
2.5 数据同步
数据同步是在源数据库和目标数据库之间保持数据同步的过程。以下是一些关于数据同步的注意事项：
在进行数据同步之前，应在源数据库和目标数据库之间创建连接。
应定期检查数据同步是否成功，并解决同步失败的情况。
以下示例代码展示如何在源数据库和目标数据库之间创建连接和同步数据：
--在源数据库中创建连接事务代理
USE master;
GO
EXEC sys.sp_addlinkedserver
@server = N'ServerName\InstanceName',
@srvproduct=N'SQL Server' ;
--创建连接字符串
EXEC sys.sp_addlinkedsrvlogin
@rmtsrvname=N'ServerName\InstanceName',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'UserName',
@rmtpassword='password'
GO
--在目标数据库中同步数据
INSERT INTO AdventureWorks2012_Copy.HumanResources.Employee
SELECT *
FROM OPENQUERY([ServerName\InstanceName], 'SELECT * FROM AdventureWorks2012.HumanResources.Employee') AS q
GO
2.6 约束和索引
约束和索引是数据库中非常重要的组成部分。在迁移数据时，这些元素也需要注意。以下是一些约束和索引方面的注意事项：
在迁移过程中，应将源数据库中的所有约束和索引复制到目标数据库中。
应定期检查约束和索引是否有效并解决无效情况。
在迁移过程中，应注意索引的大小和性能问题。
以下示例代码展示如何创建一个索引：
CREATE NONCLUSTERED INDEX IX_Employee_LoginID
ON HumanResources.Employee (LoginID);
2.7 触发器和存储过程
在进行数据库迁移时，应将源数据库中的所有触发器和存储过程复制到目标数据库中。以下是一些注意事项：
应定期检查触发器和存储过程是否有效并解决无效情况。
在迁移过程中，应注意存储过程的性能问题。
在进行数据转移之前，应对触发器和存储过程进行禁用，以避免在数据传输期间触发误操作。
以下示例代码展示如何创建一个触发器：
CREATE TRIGGER HumanResources.uAddressInsert
ON HumanResources.Address
FOR INSERT
AS
DECLARE @value nvarchar(50);
SELECT @value = SUSER_SNAME();
INSERT AdventureWorks2012_Copy.[Audit].dbo.Address_Audit
(AddressID, AddressLine1, City, StateProvinceID, PostalCode, rowguid,ModifiedDate,ModifiedBy)
SELECT AddressID, AddressLine1, City, StateProvinceID, PostalCode, rowguid,ModifiedDate,@value
FROM inserted;
2.8 安全问题
安全问题在数据迁移中也是一个非常重要的方面。以下是一些应注意的问题：
在迁移过程中，应保护数据的机密性和完整性。
在迁移完成后，应修改源数据库和目标数据库的默认密码。
应定期检查数据库的安全性和完整性。
以下示例代码展示如何创建一个用户和授权：
CREATE LOGIN [UserName] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks2012_Copy]
GO
CREATE USER [UserName] FOR LOGIN [UserName]
GO
ALTER ROLE [db_datareader] ADD MEMBER [UserName]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [UserName]
GO
2.9 日志记录
在数据库迁移期间，应保留足够的日志记录以进行故障排除和监视。以下是一些注意事项：
应定期对日志进行清理以避免过度增长。
在进行数据库迁移时，应将重要事件记录在日志中以供后续参考。
以下示例代码展示如何写入一条日志记录：
USE AdventureWorks2012_Copy;
GO
DECLARE @message VARCHAR(255);
SET @message = 'A new employee was added to the database.';
RAISERROR (@message, 10, 1);
2.10 测试
在迁移完成后，应对目标数据库进行测试以确保其功能和性能与源数据库相同。以下是一些测试方面的注意事项：
应测试目标数据库的查询性能。
应在目标数据库中运行所有现有应用程序以确保其正常工作。
应从目标数据库中提取和恢复数据以确保其完整性。
以下示例代码展示如何进行简单查询测试：
SELECT * FROM AdventureWorks2012_Copy.HumanResources.Employee WHERE BusinessEntityID=12.11 备份和恢复
在数据库迁移完成后，应定期对目标数据库进行备份以确保数据安全。以下是一些备份和恢复方面的注意事项：
应在目标数据库创建备份计划。
应在迁移过程中测试备份和恢复功能。
应将备份文件存储在安全可靠的位置。
以下示例代码展示如何创建备份：
BACKUP DATABASE AdventureWorks2012_Copy
TO DISK = 'D:\Backups\AdventureWorks2012_Copy.bak'
WITH COMPRESSION, COPY_ONLY
2.12 错误和故障排除
在迁移过程中，应时刻准备好进行故障排除和错误处理。以下是一些注意事项：
应在迁移过程中记录所有错误和警告消息。
应定期检查日志以查找问题并解决它们。
在遇到严重问题时，应考虑使用专业数据库管理员的帮助。
以下示例代码展示如何查看SQL Server错误日志：
EXEC xp_readerrorlog;2.13 定期更新
在迁移完成后，应定期更新目标数据库以确保其与源数据库保持同步。以下是一些注意事项：
应定期检查目标数据库的数据完整性和正确性。
应在源数据库中配置自动更新程序以确保目标数据库得到及时更新。
以下示例代码展示如何在数据库中配置自动更新：
USE AdventureWorks2012;
GO
CREATE PROCEDURE dbo.Employee_Update
AS
BEGIN
UPDATE HumanResources.Employee
SET ModifiedDate = GETDATE()
WHERE ModifiedDate < GETDATE() - 7;
END
GO
BEGIN
DECLARE @jobId binary(16)
EXEC msdb.dbo.sp_add_job @job_name=N'EmployeeUpdate',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Updates employee modified date',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update Employee',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0,
@subsystem=N'TSQL',
@command=N'EXEC dbo.Employee_Update;',
@database_name=N'AdventureWorks2012',
@flags=0
END
GO
2.14 版本控制
在进行数据库迁移时，应定期检查数据库引擎版本以确保与源数据库兼容。以下是一些注意事项：
应定期监视数据库引擎版本并进行升级。
在进行升级之前，应备份现有数据库以备份意外情况。
在升级后，应重新测试数据库以确保其功能和性能符合预期。
以下示例代码展示如何检查数据库引擎版本：
SELECT @@VERSION;2.15 迁移后清理
在完成数据库迁移后，应进行清理工作以确保系统的整洁和