搬运出处: https://www.cnblogs.com/Geton/p/5329989.html
我见过太多的数据库管理员花大量的时间在数据库迁移上，即便在客户的实际环境亦是如此。由于微软频繁的发布新版，基于业务和客户的要求，应用服务不得不同时升级。当然，还有许多用户仍在使用SQL Server 2000 (n 2005)的版本，这些都是接下来的不久需要升级的潜在用户。这里我将提供最简单，也是最快的解决方案把系统停机的时间降到最低。
有很多方式方法去升级之前的数据库，有人采用数据库备份恢复的方式，从一个数据库实例备份，然后恢复到另一个实例，这可是个耗时的过程，如果我们只有几个数据库需要升级的话还算好，可是假如我们需要升级的是50+甚至是100+个数据库呢？很好，这里将介绍的是最合理的解决方案去减轻这样的管理工作。
为了最大限度的减少系统停机时间，我们开始升级前需准备好一切事前准备工作，还需要有升级后执行的和出错时回滚的脚本。尽量避免采用GUI去更改实例或数据库的配置，不再多述了，我们开始吧。 -- 我将会在每一个部分尽量的多写备注。
现假定我们需要把SQL2005实例升级到2012/2014，同样从2000到2008/2008R2的步骤也是一样的。
请仔细阅读和按步骤执行：
升级前准备清单
重要提醒：脚本1-8和步骤2须预先执行并保存好，如果脚本的输出的内容要符合目标应用的要求，那么脚本可能需要稍做一些更改。
步骤 1:
1. 获取源服务器的数据实例和数据库的配置信息，保存其结果到EXCEL文件中以做升级之用。
Sheet 1 -
sp_helpdb
Sheet 2 -
select * from master.sys.master_files
Sheet 3 -
select * from master.sys.configurations -- To run on SQL 2005 and above. sp_configure -- To run on SQL 2000 (enable show advanced options and rerun).
Sheet 4 -
SELECT * FROM master.sys.sysfulltextcatalogs
STEP 2:
导出所有logins, jobs, linked servers, operators和系统数据库中的用户对象，SQL MAIL或者DATABASE MAIL等
对于导出登陆对象，请参考以下链接
从链接中得知，先在源实例中创建sp_help_revlogin存储过程，然后拷贝其结果。
注： 需删除系统帐户，只拷贝所需的帐户。
http://support.microsoft.com/kb/918992
http://support.microsoft.com/kb/246133
导出作业和其它的对象
确认哪些作业和哪些对象需要迁移到目标服务器。你可以打开SSMS，定位到所需要导出的对象，然后在对象资源管理器中选中对象，导出脚本到新窗口或文件，然后保存，其它对象类似，然后这些导出的脚本需要在目标服务器的SSMS中运行。
STEP 3:
提供的 t-sql 脚本 [# 1 to # 8] 必须在源服务器中执行从而产生目标服务器执行的脚本，这一动作可以在几天/星期前完成。在具体执行期间，这个产生的脚本将会在目标服务器上执行。
/*============================================================ Script 1 - 产生分离数据库脚本
重要注释: 在源服务器上执行以下语句以在结果窗口产生分离数据库脚本。保存这个结果。万一目标服务器需要回滚，此脚本同样适用。 增加了参数 @keepfulltextindexfile=‘true‘ 以防某一用户数据库采用了全文检索索引目录，下一版本中，将会取消此参数 分离命令将会在以下情况中不能正常执行 如果你的数据库用于同步，其必须停止发布。 必须删掉的有的快照才能分离数据库。 数据库处于镜像会话，必须停止。 质疑的数据库不能被分离，除非你更改紧急模式。
Author : Dathuraj Pasarge Last Edited : 2nd June 2014 ===============================================================*/ declare @dbname varchar(150) declare @cmd varchar(250) IF CURSOR_STATUS(‘global‘,‘dbcursor‘)>=-1 BEGIN CLOSE dbcursor DEALLOCATE dbcursor END declare dbcursor cursor for select name from master..sysdatabases where dbid>4 and name not in(‘ReportServer‘,‘ReportServerTempDB‘,‘pubs‘,‘Northwind‘,‘AdventureWorks‘) -- Include any additional databases in NOT IN clause to exclude the databases from detach. Open dbcursor Fetch next from dbcursor into @dbname WHILE @@FETCH_STATUS = 0 BEGIN Begin set @cmd=‘sp_detach_db ‘+‘‘‘‘+@dbname+‘‘ +‘‘‘,‘‘true‘‘‘ print @cmd print ‘go‘ End Fetch next from dbcursor into @dbname END close dbcursor deallocate dbcursor
output：
/*===============================================================
Script 2 : 生成脚本 - 生成重新附加数据库的脚本。以防需要回滚。
注意事项
----------------------
在源服务器上运行，然后保存输出的结果用于重新附加数据库。此举以防在升级过程中，出现一些不可预见性的错误。
Note: Scripts for Rolling back the change.
Author : Dathuraj Pasarge.
Last Edited : 2nd June 2014
================================================================*/
declare @dbname nvarchar(450)
declare @cmd nvarchar(1200)
declare @mdf nvarchar(800)
declare @ldf nvarchar(800)
DECLARE @serverVersion varchar(50)
select @serverVersion = CONVERT(varchar(50),SERVERPROPERTY(‘ProductVersion‘))
SET @serverVersion = LEFT(@serverVersion, CHARINDEX(‘.‘, @serverVersion) - 1)
IF CURSOR_STATUS(‘global‘,‘dbcursor‘)>=-1
BEGIN
CLOSE dbcursor
DEALLOCATE dbcursor
END
If convert(int,@serverVersion)<9
BEGIN
declare dbcursor cursor for
select name from master..sysdatabases where dbid>4 and name not in(‘ReportServer‘,‘ReportServerTempDB‘,‘pubs‘,‘Northwind‘,‘AdventureWorks‘)
-- Include the db‘s in NOT IN clause, which are suspect or inaccessible.
Open dbcursor
Fetch next from dbcursor into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
Begin
set @mdf = RTRIM((select [filename] from master..sysaltfiles where DB_NAME(dbid)=@dbname and fileid=1))
set @ldf= RTRIM((select [filename] from master..sysaltfiles where DB_NAME(dbid)=@dbname and fileid=2))
set @cmd=‘sp_attach_db ‘+‘‘‘‘+@dbname+‘‘‘,‘+ ‘‘‘‘+@mdf+‘‘‘,‘‘‘+@ldf+‘‘‘‘
print @cmd
print ‘go‘
End
Fetch next from dbcursor into @dbname
END
close dbcursor
deallocate dbcursor
END
ELSE
If convert(int,@serverVersion)>=9
BEGIN
declare dbcursor cursor for
select name from master..sysdatabases where dbid>4 and name not in(‘ReportServer‘,‘ReportServerTempDB‘,‘pubs‘,‘Northwind‘,‘AdventureWorks‘)
-- Include the db‘s in NOT IN clause, which are suspect or inaccessible.
Open dbcursor
Fetch next from dbcursor into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
Begin
set @mdf = (select physical_name from master.sys.master_files where DB_NAME(database_id)=@dbname and file_id=1
and type_desc=‘ROWS‘
)
set @ldf = (select physical_name from master.sys.master_files where DB_NAME(database_id)=@dbname and file_id=2
and type_desc=‘LOG‘)
set @cmd=‘sp_attach_db ‘+‘‘‘‘+@dbname+‘‘‘,‘+ ‘‘‘‘+@mdf+‘‘‘,‘‘‘+@ldf+‘‘‘‘
print @cmd
print ‘go‘
End
Fetch next from dbcursor into @dbname
END
close dbcursor
deallocate dbcursor
END
output:
/*==========================================================
Script 3 : 产生拷贝命令
注意事项 :
源服务器上运行
以下脚本产生拷贝命令，把数据文件和日志文件复制到目标服务器上
设置@DestinationServerName变量为目标服务器名，同样需设置目标服务器的共享文件夹，其权限必须符可读可写
Author : Dathuraj Pasarge
Last Edited : 10th Dec 2014
==============================================================*/
declare @dbname nvarchar(150)
declare @cmd NVARCHAR(2000)
declare @MDFSourceFile nvarchar(500)
declare @LDFSourceFile varchar(500)
declare @NDFSourceFile nvarchar(500)
declare @MDFdestinationPath nvarchar(800)
declare @LDFdestinationPath nvarchar(800)
declare @DestinationServerName nvarchar(200)
declare @DestinationShareFolderMDF nvarchar(200)
declare @DestinationShareFolderLDF nvarchar(200)
declare @count1 int, @count2 int, @fileid int
set @DestinationServerName=‘SQL02‘ -- Update destination serverName
set @DestinationShareFolderMDF=‘Share1‘ -- Update ShareFolder created on destination server, for copying all mdf files
set @DestinationShareFolderLDF=‘Share2‘ -- Update ShareFolder created on destination server, for copying all ldf files
set @MDFdestinationPath=‘\\‘+@DestinationServerName+‘\‘+@DestinationShareFolderMDF+‘\MSSQL\DATA‘ -- update folder names as per your requirement
set @LDFdestinationPath=‘\\‘+@DestinationServerName+‘\‘+@DestinationShareFolderLDF+‘\MSSQL\Logs‘ -- update folder names as per your requirement
IF CURSOR_STATUS(‘global‘,‘dbcursor‘)>=-1
BEGIN
CLOSE dbcursor
DEALLOCATE dbcursor
END
DECLARE @serverVersion varchar(50)
select @serverVersion = CONVERT(varchar(50),SERVERPROPERTY(‘ProductVersion‘))
SET @serverVersion = LEFT(@serverVersion, CHARINDEX(‘.‘, @serverVersion) - 1)
If convert(int,@serverVersion)>=9
BEGIN
declare dbcursor cursor for
select name from master..sysdatabases where dbid>4 and name not in(‘ReportServer‘,‘ReportServerTempDB‘,‘pubs‘,‘Northwind‘,‘AdventureWorks‘)
-- Include the db‘s in NOT IN clause, which are suspect or inaccessible.
Open dbcursor
Fetch next from dbcursor into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @MDFSourceFile = (select physical_name from master.sys.master_files where DB_NAME(database_id)=@dbname and file_id=1) -- For MDF Files
set @cmd=‘XCOPY ‘+‘"‘+@MDFSourceFile+ ‘" "‘ +@MDFdestinationPath+ ‘"‘
PRINT @cmd
set @LDFSourceFile = (select physical_name from master.sys.master_files where DB_NAME(database_id)=@dbname and file_id=2) -- For LDF Files
set @cmd=‘XCOPY ‘+‘"‘+@LDFSourceFile+ ‘" "‘ +@LDFdestinationPath+ ‘"‘
PRINT @cmd
set @count1 = (select count(physical_name) from master.sys.master_files where DB_NAME(database_id)=@dbname and file_id>2)
set @count2=1
set @fileid=3
WHILE (@count2<=@count1)
BEGIN
set @NDFSourceFile = (select physical_name from master.sys.master_files where DB_NAME(database_id)=@dbname and file_id=‘‘+@fileid+‘‘ )
set @cmd=‘XCOPY ‘+‘"‘+@NDFSourceFile+ ‘" "‘ +@MDFdestinationPath+ ‘"‘
set @count2=@count2+1
set @fileid=@fileid+1
PRINT @cmd
END
Fetch next from dbcursor into @dbname
PRINT ‘‘
END
END
If convert(int,@serverVersion)<9
BEGIN
declare dbcursor cursor for
select name from master..sysdatabases where dbid>4 and name not in(‘ReportServer‘,‘ReportServerTempDB‘,‘pubs‘,‘Northwind‘,‘AdventureWorks‘)
-- Include the db‘s in NOT IN clause, which are suspect or inaccessible.
Open dbcursor
Fetch next from dbcursor into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @MDFSourceFile = RTRIM((SELECT [filename] FROM master..sysaltfiles where DB_NAME(dbid)=@dbname and fileid=1)) -- For MDF Files
set @cmd=‘XCOPY ‘+‘"‘+@MDFSourceFile+ ‘" "‘ +@MDFdestinationPath+ ‘"‘
PRINT @cmd
set @LDFSourceFile = RTRIM((SELECT [filename] FROM master..sysaltfiles where DB_NAME(dbid)=@dbname and fileid=2)) -- For LDF Files
set @cmd=‘XCOPY ‘+‘"‘+@LDFSourceFile+ ‘" "‘ +@LDFdestinationPath+ ‘"‘
PRINT @cmd
set @count1 = (SELECT count([filename]) FROM master..sysaltfiles where DB_NAME(dbid)=@dbname and fileid>2)
set @count2=1
set @fileid=3
WHILE (@count2<=@count1)
BEGIN
set @NDFSourceFile = RTRIM((SELECT [filename] FROM master..sysaltfiles where DB_NAME(dbid)=@dbname and fileid=‘‘+@fileid+‘‘ ))
set @cmd=‘XCOPY ‘+‘"‘+@NDFSourceFile+ ‘" "‘ +@MDFdestinationPath+ ‘"‘
set @count2=@count2+1
set @fileid=@fileid+1
PRINT @cmd
END
Fetch next from dbcursor into @dbname
PRINT ‘‘
END
END
close dbcursor
deallocate dbcursor
Output
/*============================================================== Script 4 : 产生脚本 - 用于目标服务器附加数据库的 注意事项: 源服务器运行以下脚本, 产生附加数据库脚本用于目标服务器。 取得所有的次数据库文件(.ndf). 如果存在次日志文件，其路径需手动包含到输出结果中。 在目标服务器运行前，请保文件有效 Author - Dathuraj Pasarge. Last Modified Date: 28th Nov 2014. ===========================================================*/ declare @dbname nvarchar(500) declare @FileName nvarchar(500) declare @cmd nvarchar(2000) declare @MdfPath nvarchar(500) declare @LdfPath nvarchar(500) declare @count int, @count2 int declare @fileid int /* IMP NOTE - Set the below paths as per your environment for mdf and ldf location on the destination server*/ set @MdfPath=‘D:\MSSQL\DATA\‘ set @LdfPath=‘E:\MSSQL\Logs\‘ IF CURSOR_STATUS(‘global‘,‘dbcursor‘)>=-1 BEGIN CLOSE dbcursor DEALLOCATE dbcursor END declare dbcursor cursor for select db_name(dbid),RTRIM([filename]) from master..sysaltfiles where dbid>4 and db_name(dbid) not in (‘ReportServer‘,‘ReportServerTempDB‘,‘pubs‘,‘Northwind‘,‘AdventureWorks‘) and dbid<>32767 and fileid=1 -- Include the db‘s in NOT IN clause, which are suspect or inaccessible or not in scope. Open dbcursor Fetch next from dbcursor into @dbname,@FileName WHILE @@FETCH_STATUS = 0 BEGIN Begin set @FileName= REVERSE(left(@FileName, CHARINDEX(‘.mdf‘, @FileName) + 4)) set @FileName= reverse(left(@FileName, CHARINDEX(‘\‘, @FileName) -1 )) SET @cmd=‘exec sp_attach_db ‘+‘‘‘‘+@dbname+‘‘‘‘+‘, ‘+‘‘‘‘+@MdfPath+‘‘+@FileName+‘‘‘,‘ SET @FileName=RTRIM((SELECT filename from master..sysaltfiles where fileid=2 and db_name(dbid)=@dbname)) set @FileName= REVERSE(left(@FileName, CHARINDEX(‘.ldf‘, @FileName) + 4)) set @FileName= reverse(left(@FileName, CHARINDEX(‘\‘, @FileName) -1 )) SET @cmd=@cmd + ‘‘‘‘+@LdfPath+‘‘+@FileName+‘‘‘‘ set @count=(SELECT COUNT(*) from master..sysaltfiles where fileid>2 and db_name(dbid)=@dbname) set @count2 =1 set @fileid=3 while(@count2<=@count) begin SET @FileName=RTRIM((SELECT filename from master..sysaltfiles where fileid=‘‘+@fileid+‘‘ and db_name(dbid)=@dbname)) set @FileName= REVERSE(left(@FileName, CHARINDEX(‘.ndf‘, @FileName) + 4)) set @FileName= reverse(left(@FileName, CHARINDEX(‘\‘, @FileName) -1 )) SET @cmd=@cmd + ‘,‘‘‘+@MdfPath+‘‘+@FileName+‘‘‘‘ set @fileid=@fileid+1; set @count2=@count2+1; end print @cmd print ‘go‘ End Fetch next from dbcursor into @dbname,@FileName PRINT ‘‘ END close dbcursor deallocate dbcursor
output
/*================================================================= Script 5 : 更改数据库兼容等级 在源服务器上运行, 同时保存执行的结果。 查询输出在目标服务器上执行 设置@cmptlevel变量，不同的数据代表不同的版本 90 for SQL 2005, 100 for SQL 2008 and 2008 R2, 110 for SQL 2012 and 120 for SQL 2014. Author : Dathuraj Pasarge Last Edited : 14th July 2014 ==================================================================*/ declare @dbname varchar(250) declare @cmptlevel varchar(10) declare @Counter int declare @cmd varchar(250) -- Set Counter to Zero Select @Counter = 0 SET @cmptlevel = 110 -- Change compatibility 90 for SQL 2005, 100 for SQL 2008 and 2008 R2, 110 for SQL 2012 and 120 for SQL 2014. IF CURSOR_STATUS(‘global‘,‘dbcursor‘)>=-1 BEGIN CLOSE dbcursor DEALLOCATE dbcursor END declare dbcursor cursor for select sd.name from master..sysdatabases sd where sd.dbid>4 and sd.name not in(‘ReportServer‘,‘ReportServerTempDB‘, ‘pubs‘,‘Northwind‘,‘AdventureWorks‘) -- Include databases with readonly,offline ones in this NOT IN clause, or make them online. Open dbcursor Fetch next from dbcursor into @dbname WHILE @@FETCH_STATUS = 0 BEGIN Begin set @cmd=‘alter database ‘+ @dbname+ ‘ set compatibility_level = ‘+ @cmptlevel Select @Counter = @Counter + 1 print @cmd End Fetch next from dbcursor into @dbname END close dbcursor deallocate dbcursor
output
/*=============================================================
Script 6 : 更新数据库usage。
注意事项 –
源服务器上运行，保存查询输出
查询输出在目标服务器上执行
只有从SQL2000的数据库上升级才需要执行
Author : Dathuraj Pasarge
Last Edited : 14th July 2014
===============================================================*/
DECLARE @SQL VARCHAR(1000)
DECLARE @DB sysname
IF CURSOR_STATUS(‘global‘,‘dbcursor‘)>=-1
BEGIN
CLOSE dbcursor
DEALLOCATE dbcursor
END
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name] FROM master..sysdatabases WHERE dbid>4 and [name] not in(‘ReportServer‘,‘ReportServerTempDB‘,‘pubs‘,‘Northwind‘,‘AdventureWorks‘) ORDER BY [name] -- Include the db‘s in NOT IN clause, which are suspect or inaccessible.
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = ‘USE [‘ + @DB +‘]‘ + CHAR(13) + ‘DBCC updateusage(0)‘ + CHAR(13)
PRINT @SQL
FETCH NEXT FROM curDB INTO @DB
END
CLOSE curDB
DEALLOCATE curDB
output
/*=============================================================
Script 7 : 产生脚本 - 更新所有数据库的统计信息
注意事项：
源服务器上执行，然后保存结果用于目标服务器上执行
Author : Dathuraj Pasarge
Last Edited : 14th July 2014
===============================================================*/
DECLARE @SQL VARCHAR(1000)
DECLARE @DB sysname
IF CURSOR_STATUS(‘global‘,‘dbcursor‘)>=-1
BEGIN
CLOSE dbcursor
DEALLOCATE dbcursor
END
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR
SELECT [name] FROM master..sysdatabases WHERE dbid>4 and [name] NOT IN (‘ReportServer‘,‘ReportServerTempDB‘,‘pubs‘,‘Northwind‘,‘AdventureWorks‘)
ORDER BY [name] -- Include the db‘s in NOT IN clause, which are suspect or inaccessible.
OPEN curDB
FETCH NEXT FROM curDB INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = ‘USE [‘ + @DB +‘]‘ + CHAR(13) + ‘EXEC sp_updatestats‘ + CHAR(13)
PRINT @SQL
FETCH NEXT FROM curDB INTO @DB
END
CLOSE curDB
DEALLOCATE curDB
output
/*============================================================ Script 8 : 产生脚本 - 更改数据库所属 Author : Dathuraj Pasarge ===============================================================*/ declare @dbname varchar(250) declare @login varchar(250) declare @cmd varchar(250) IF CURSOR_STATUS(‘global‘,‘dbcursor‘)>=-1 BEGIN CLOSE dbcursor DEALLOCATE dbcursor END declare dbcursor cursor for select sd.name, suser_sname(sd.sid) from master..sysdatabases sd where sd.dbid>4 and sd.name NOT IN (‘ReportServer‘,‘ReportServerTempDB‘,‘pubs‘,‘Northwind‘,‘AdventureWorks‘) -- Include the db‘s in NOT IN clause, which are suspect or inaccessible. Open dbcursor Fetch next from dbcursor into @dbname,@login WHILE @@FETCH_STATUS = 0 BEGIN Begin print ‘use ‘ + @dbname set @cmd=‘exec sp_changedbowner ‘+‘‘‘‘+@login+‘‘‘‘ print @cmd End Fetch next from dbcursor into @dbname,@login END close dbcursor deallocate dbcursor
output
在正式升级的时候请按以下脚本和步骤执行。
/*========================================================================= Script 9 : 杀掉所有源服务器连接进程 分离数据库前，请运行以下脚本杀掉现有连接进程 Author : Dathuraj Pasarge. Last Modified Date: 10th Nov 2014. =========================================================================*/ DECLARE @kill varchar(4000) set @kill= ‘‘; SELECT @kill = @kill + ‘kill ‘ + CONVERT(varchar(5), spid) + ‘;‘ FROM master..sysprocesses WHERE spid >50 and spid<>(select @@SPID) EXEC(@kill); go use master go sp_who2; go
4. 分享所有源服务器的数据库
执行 Script # 1 的查询结果
5. 生成XCOPY命令复制源服务器的所有主数据文件，日志文件到目标服务器上，生成的命令在以管理员身份运行的命令提示符下运行。
执行 Script # 3 的查询结果
注意： 对比数据库文件的个数，如果存在多于一个ldf文件的数据库，记得手动添加相应的命令。
6. 在目标服务器附加所有数据库
执行 Script # 4 的查询结果
7. 附加完毕后，需确保所有数据库在线且可以访问
8.在附加过程中如果出错，请确保数据库所有文件均复制到目标数据库且确保与你脚本的路径一致
9.执行创建登陆，作业，连接服务器，操作员脚本以创建相应的对象
10. 修复孤立用户。
/*===========================================================
Script 10 - Fix Orphaned users.
Notes:
1. 以下脚本修复所有数据库的孤立用户
2. 当你创建了所有登陆用户后,运行以下脚本去除孤立用户.
Author: Dathuraj Pasarge
==========================================================*/
SET NOCOUNT ON
If exists (select * from sysobjects where name like ‘#databases%‘)
DROP TABLE #databases
if exists (select * from sysobjects where name like ‘#orphanusers%‘)
DROP TABLE #orphanusers
BEGIN
declare @username sysname,
@DBName varchar(250),
@NoOfUsers smallint,
@Query1 varchar(2000),
@Query2 varchar(2000)
CREATE TABLE #orphanusers
(
rowid smallint IDENTITY(1,1),
UserName sysname,
UserSID varbinary(100)
)
CREATE TABLE #databases
(
dbname varchar(250)
)
INSERT INTO #databases SELECT name from master.sys.databases where database_id > 4 and state_desc = ‘ONLINE‘
WHILE EXISTS(SELECT 1 FROM #databases)
BEGIN
SET @Query1 = ‘‘
SELECT TOP 1 @DBName = dbname FROM #databases ORDER BY dbname
SET @Query1 = ‘EXEC ‘ + @DBName + ‘.dbo.sp_change_users_login ‘‘report‘‘‘
INSERT INTO #orphanusers EXEC(@Query1)
WHILE EXISTS(SELECT 1 FROM #orphanusers)
BEGIN
SELECT TOP 1 @username = UserName
FROM #orphanusers ORDER BY rowid
BEGIN TRY
SET @Query2 = ‘EXEC ‘ + @DBName + ‘.dbo.sp_change_users_login ‘‘Update_One‘‘,‘ + @username+ ‘,‘ + @username
EXEC(@Query2)
Print ‘Orphaned user ‘ + @username + ‘ fixed in ‘ +@DBName +‘ database‘
END TRY
BEGIN CATCH
PRINT ‘Login ‘+ @username + ‘ has not found. If needed , create it and fix the orphaned issue. ‘
END CATCH
DELETE FROM #orphanusers WHERE UserName = @username
END
DELETE FROM #databases WHERE dbname = @DBName
END
DROP TABLE #orphanusers
DROP TABLE #databases
END
11. 更改数据库兼容级别
执行 Script # 5 查询输出
12. 更改所有用户数据库所属对象.
执行 Script # 6 查询输出
13. 更新数据库usage
执行 Script # 7 查询输出
14. 更新数据库统计信息
执行 Script # 8 查询输出
迁移后须检查事项
如果你发现应用程序响应慢了，同时又有足够的停机时间，试着重建所有索引和维护计划或者那些你认为可行的办法.
1. 检查SQL Server错误日志
2. 如果你在附加数据库的时候出现以下错误，以管理员身份打开SSMS
操作系统错误5: "5(failed to retrieve text for this error. Reason: 15105)".
3. 如果不能解析主机名，试着刷新一下IP地址和DNS缓存
4. 如果有用到ODBC,确保ODBC连接可用。
5. 确保所有连接服务器可用.
原文：https://www.cnblogs.com/lab-zj/p/14245500.html