一、数据库批量分离
USE [master]
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE [object_id] = OBJECT_ID(N'[dbo].[spDetachAllUserDatabases]')
AND type IN ( N'P', N'PC' ) )
DROP PROCEDURE [dbo].[spDetachAllUserDatabases]
GO
CREATE PROCEDURE [dbo].[spDetachAllUserDatabases]
AS
BEGIN
--Declare Variables
DECLARE @DatabaseName VARCHAR(100)
DECLARE @MinDatabaseID INT
DECLARE @MaxDatabaseID INT
DECLARE @SQL VARCHAR(4000)
--Check for temporary table and drop it if it exists
IF OBJECT_ID('tempDB.dbo.#Database') IS NOT NULL
DROP TABLE [#Database];
--Create temporary table
CREATE TABLE #Database
(
ID INT IDENTITY(1, 1),
DatabaseName VARCHAR(100)
)
--Check for existing user databases
IF EXISTS ( SELECT name
FROM sys.databases
WHERE database_id > 4
AND name NOT IN ( 'SQLDBA', 'ReportServer',
'ReportServerTempDB',
'distribution' ) )
BEGIN
--Insert all database names into a temporary table
INSERT INTO #Database ( DatabaseName )
SELECT name
FROM sys.databases
WHERE database_id > 4
AND name NOT IN ( 'SQLDBA', 'ReportServer',
'ReportServerTempDB',
'distribution' )
--Set Variables for the detach database loop
SELECT @MinDatabaseID = MIN(ID),
@MaxDatabaseID = MAX(ID)
FROM #Database
--Begin loop to detach databases
WHILE @MinDatabaseID <= @MaxDatabaseID
BEGIN
--Get DatabaseName
SELECT @DatabaseName = DatabaseName
FROM #Database
WHERE ID = @MinDatabaseID
--Build Detach Database Command
SET @SQL = 'EXEC sp_detach_db ' + '''' + @DatabaseName
+ '''' + ';'
--Try Catch block to execute SQL and handle errors
BEGIN TRY
--Detach Database
EXEC ( @SQL
)
PRINT 'Detached ' + @DatabaseName
END TRY
BEGIN CATCH
SELECT @DatabaseName,
message_id,
severity,
[text],
@SQL
FROM sys.messages
WHERE message_id = @@ERROR
AND language_id = 1033 --British English
END CATCH
--Get the next DatabaseName ID
SET @MinDatabaseID = @MinDatabaseID + 1
--End Loop
END
END
END
GO
二、批量附加(比较麻烦)
--使用方法:在重装系统前,运行这个,把生成的SQL语句保存起来
-- 重装系统后再运行生成的那个文件,便可实现批量附加数据库
--目前只适合SQL2005,SQL2000的不行,还正在研究。
USE [master]
GO
IF OBJECT_ID('attach_db_pro') IS NOT NULL
DROP PROCEDURE attach_db_pro
GO
CREATE PROCEDURE attach_db_pro
@dir NVARCHAR(520)
AS
DECLARE @dbname VARCHAR(20)
DECLARE @filename NVARCHAR(520)
DECLARE @name SYSNAME
DECLARE @temp_db_name VARCHAR(20)
DECLARE @i INT
DECLARE my_cursor CURSOR
FOR
SELECT DB_NAME(DBID) dbname,name,filename FROM master..sysaltfiles
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @dbname,@name,@filename
WHILE @@fetch_status = 0
BEGIN
SET @temp_db_name = @dbname
SET @i = 1
PRINT 'exec sp_attach_db @db_name = ' + @dbname + ',@filename' + CONVERT(VARCHAR,@i) + ' = ''' + @dir + '\' + @name + RIGHT(@filename,4)+''''
FETCH NEXT FROM my_cursor INTO @dbname,@name,@filename
WHILE @@fetch_status = 0 AND @temp_db_name = @dbname
BEGIN
SET @i = @i + 1
print ',@filename' + CONVERT(VARCHAR,@i) + ' = ''' + @dir + '\'+@name+RIGHT(@filename,4)+''''
FETCH NEXT FROM my_cursor INTO @dbname,@name,@filename
END
PRINT ''
END
CLOSE my_cursor
DEALLOCATE my_cursor
GO