This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing two different scripts to populate attach and detach scripts for all your SQL Server Databases.
When we are migrating our SQL Database Server, we require to move all databases from one server to another server.
If you have a big number of databases, making of individual attach and detach script will take more time.
I have also added script to change database access mode like: Multi_User to Single_User for Detach Process and Single_User to Multi_User for Attach Process.
If your database is in use, you may require to change the database access mode to close all running connections.
Using this script you can populate attach and detach scripts for all databases.
Execute this script to generate detach scripts for all databases:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
DECLARE @DatabaseName AS VARCHAR(255) DECLARE CurDetach CURSOR FOR SELECT name FROM MASTER.sys.databases WHERE owner_sid > 1; OPEN CurDetach FETCH Next FROM CurDetach INTO @DatabaseName WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'ALTER DATABASE ' + @DatabaseName + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE' + CHAR(10) + ' GO' + CHAR(10) + 'sp_detach_db ' + @DatabaseName + CHAR(10) + ' GO' + CHAR(10) + 'Print ''Detach of ' + @DatabaseName + ' database completed successfully''' + CHAR(10) + ' GO' FETCH NEXT FROM CurDetach INTO @DatabaseName END CLOSE CurDetach DEALLOCATE CurDetach |
Execute this script to generate attach scripts for all databases:
You can change your file name or file path using @Filepath variable.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
DECLARE @DatabaseName AS VARCHAR(255) DECLARE @Filepath AS VARCHAR(255) SET @Filepath = 'C:\SQLDATA\' DECLARE CurAttach CURSOR FOR SELECT name FROM MASTER.sys.databases WHERE owner_sid > 1; OPEN CurAttach FETCH Next FROM CurAttach INTO @DatabaseName WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'CREATE DATABASE ' + @DatabaseName + ' ON (FILENAME = '+ @Filepath + @DatabaseName +'.mdf) ,(FILENAME = '+ @Filepath + @DatabaseName +'.ldf) FOR ATTACH GO ALTER DATABASE '+ @DatabaseName +' SET MULTI_USER WITH ROLLBACK IMMEDIATE GO' FETCH NEXT FROM CurAttach INTO @DatabaseName END CLOSE CurAttach DEALLOCATE CurAttach |
Leave a Reply