使用分离和附加方式迁移SQLServer 数据库是一种很方便的方法,当库不多的时候,可以直接在图形界面上操作,但当库很多时,可以使用如下脚本。
1 批量分离数据库脚本
use master
declare @SqlStr varchar(8000)
select @SqlStr=''
select @SqlStr=@SqlStr+'EXEC sp_detach_db ['+[name]+']'+CHAR(10)
from master..sysdatabases where dbid>=7
Exec(@SqlStr)
print @SqlStr
由于定义的 @ SqlStr 是varchar(8000)的类型,如果要分离的数据很多的话,超出限制的SQL就无法正常运行,所以如果很多的库的话,可以执行多次,分批进行分离。
另外需要注意,如果有会话连接的话,会导致分离失败,我们可以先将数据库禁止远程连接,并重启DB,这样可以方式没有其他程序连接到数据库上,设置之后,在执行分离操作。
2 批量附加数据库脚本
USE MASTER
GO
IF OBJECT_ID('[sp_AttchDataBase]') IS NOT NULL
DROP PROCEDURE [sp_AttchDataBase]
GO
/*附加数据库(V2.0) Andy 2011-7-8 */
CREATE PROCEDURE sp_AttchDataBase(
@Path NVARCHAR(1024),
@DataFiles NVARCHAR(MAX) = NULL,
@SplitStr NVARCHAR(50) = ','
)
AS
SET NOCOUNT ON
/*
V2.0 版本,在V1.0基础上,处理文件路径不规范原則,e.g. @DataFiles='E:/"my data DB"/"Hello RT"'
@Path 文件路径
@DataFiles 文件名列表
@SplitStr 文件名列表中的文件分隔符
1.必须把要附加的数据库文件(*.mdf和*.ldf)放到@Path下,
2.当@DataFiles Is Null 会附加@Path文件夹下的所有数据库文件.
e.g:
Exec sp_AttchDataBase 'D:/db2'
*/
--检查文件路径是否正确
DECLARE @Dir NVARCHAR(1024),
@i INT,
@x XML
IF RIGHT(@Path, 1) <> '/'
SET @Path = @Path + '/'
IF CHARINDEX('//', @Path) > 0
BEGIN
--RAISERROR 50001 N'文件路径中不能包含有"//",@Path设置错误.'
RETURN(1)
END
SET @Dir = 'Dir ' + @Path
EXEC @i = xp_cmdshell @Dir,
no_output
IF @i <> 0
BEGIN
--RAISERROR 50001 N'无效的文件路径,@Path设置错误.'
RETURN(1)
END
SET @Path = REPLACE(@Path, '"', '') /*处理文件路径不规范原則*/
DECLARE @Files TABLE(NAME NVARCHAR(512))
DECLARE @filetmpfin TABLE(
NAME NVARCHAR(255) NOT NULL,
depth INT NULL,
IsFile BIT NULL
)
DECLARE @SmoPrimayChildren TABLE(
STATUS INT,
fileid INT,
NAME SYSNAME,
FILENAME NVARCHAR(512)
)
DECLARE @smoPrimaryFileProp TABLE(PROPERTY SQL_VARIANT NULL, VALUE SQL_VARIANT NULL)
SET @DataFiles = REPLACE(
REPLACE(REPLACE(@DataFiles, CHAR(13) + CHAR(10), ''), CHAR(13), ''),
CHAR(10),
''
)
SET @x = N'<Root><File>' + REPLACE(@DataFiles, @SplitStr, N'</File><File>') +
N'</File></Root>'
INSERT INTO @Files
SELECT t.v.value('.[1]', 'nvarchar(512)') AS NAME
FROM @x.nodes('Root/File') t(v)
WHERE t.v.value('.[1]', 'nvarchar(512)') > ''
INSERT INTO @filetmpfin
EXEC MASTER.dbo.xp_dirtree @Path,
1,
1
DECLARE @File NVARCHAR(255),
@sql NVARCHAR(4000),
@DataBase SYSNAME
DECLARE cur_File CURSOR
FOR
SELECT NAME
FROM @filetmpfin AS a
WHERE IsFile = 1
AND NAME LIKE '%.mdf'
AND (
EXISTS(
SELECT 1
FROM @Files
WHERE NAME = a.Name
)
OR @DataFiles IS NULL
)
AND NOT EXISTS(
SELECT 1
FROM MASTER.sys.master_files
WHERE physical_name = @Path + a.Name
)
OPEN cur_File
BEGIN TRY
FETCH NEXT FROM cur_File INTO @File
WHILE @@Fetch_Status = 0
BEGIN
SET @sql = 'dbcc checkprimaryfile (N''' + @Path + @File + ''' , 2) With No_Infomsgs'
INSERT INTO @smoPrimaryFileProp
EXEC (@sql)
SET @sql = 'dbcc checkprimaryfile (N''' + @Path + @File + ''' , 3) With No_Infomsgs'
INSERT INTO @SmoPrimayChildren
EXEC (@sql)
SELECT @DataBase = QUOTENAME(CONVERT(NVARCHAR(255), VALUE)),
@sql = NULL
FROM @smoPrimaryFileProp
WHERE CONVERT(NVARCHAR(255), PROPERTY) = 'Database name'
SELECT @sql = ISNULL(
@sql + ',' + CHAR(13) + CHAR(10),
'Create DataBase ' + @DataBase + ' On' + CHAR(13) + CHAR(10)
) +
'(FileName=N''' + @Path + RIGHT(
RTRIM(FILENAME),
CHARINDEX('/', REVERSE(RTRIM(FILENAME))) -1
) + ''')'
FROM @SmoPrimayChildren
EXEC (@sql + ' For Attach')
PRINT N'成功附加数据库: ' + @DataBase
DELETE
FROM @SmoPrimayChildren
DELETE
FROM @smoPrimaryFileProp
FETCH NEXT FROM cur_File INTO @File
END
END TRY
BEGIN CATCH
DECLARE @Error NVARCHAR(2047)
SET @Error = ERROR_MESSAGE()
--RAISERROR 50001 @Error
END CATCH
CLOSE cur_File
DEALLOCATE cur_File
GO
/************************************************************
* 调用方式
************************************************************/
use master
go
Exec sp_AttchDataBase
@Path = 'F:/LQDB/', -- nvarchar(1024)
@DataFiles = NULL, -- nvarchar(max)
@SplitStr = NULL -- nvarchar(50)