info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



Automate Database Attach or Detach of all User Databases for a Migration
by BF (Principal Consultant; Architecture; Engineering)
2017-12-18









Solution to Migrate a SQL Server 2005/2008/2012/2014 STD hosting 1000's databases into a new Server running SQL Server 2016 STD.


1. Ensure identical file structure on the Source(SQL Server 2014) and Destination(SQL Server 2016) for User Databases.

2. Shut down all Applications / Websites (ensure no active connections into SQL Server)

3. Generate Source Logins from Microsoft script here

4. Generate Source Default Databases Script

Select 'ALTER LOGIN ' + name + ' WITH DEFAULT_DATABASE = ' + dbname from syslogins order by name asc

5. Generate Source Detach & Save output to a file (see below)

6. Generate Source Attach & Save output to a file (see below)

7. Execute Detach on Source

8. Un-mount LUNS from Source and Mount to Destination OR Copy Source Data and Log Folders/Files to Destination

9. Execute Attach on Destination

10. Execute Logins Script on Destination (output from Step 3)

11. Execute Fix Orphaned Users Script on Destination (see below)

12. Execute Adjust Default Database on Destination (output from Step 4)



Generate Source Detach or Generate Source Attach:


USE [master];
GO

DECLARE @db NVARCHAR(200), @file NVARCHAR(MAX), @sql_detach_cmd NVARCHAR(MAX), @sql_attach_cmd NVARCHAR(MAX), @cnt int = 1, @cur_rows int
DECLARE @i INT, @DetachOrAttach BIT, @TotalSize INT, @TotalDrives INT;
SET @DetachOrAttach = 0; --1 Detach, 0 Attach

Set @TotalSize = (Select SUM(size)*8 / 1024 / 1024 as 'Total GB' FROM sys.databases d
inner join sys.master_files f on d.database_id = f.database_id
WHERE d.name not in ('MASTER', 'TEMPDB', 'MODEL', 'MSDB'))

Set @TotalDrives = (Select COUNT(DISTINCT(substring(f.physical_name,1, 1))) FROM sys.databases d
inner join sys.master_files f on d.database_id = f.database_id
WHERE d.name not in ('MASTER', 'TEMPDB', 'MODEL', 'MSDB'))

DECLARE cur_dbs CURSOR STATIC LOCAL FORWARD_ONLY
FOR SELECT RTRIM(LTRIM([name])) FROM sys.databases WHERE name not in ('MASTER', 'TEMPDB', 'MODEL', 'MSDB') ORDER BY NAME ASC
OPEN cur_dbs

FETCH NEXT FROM cur_dbs INTO @db

Set @cur_rows = @@CURSOR_ROWS

Print CHAR(10) + + CHAR(10) + '/* SUMMARY: ' + convert(varchar(10),@@CURSOR_ROWS) + ' Databases selected; ' + convert(varchar(10),@TotalSize) + 'gb total size; '
+ convert(varchar(10),@TotalDrives) + ' different drives */' + CHAR(10) + CHAR(10)


WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @i = 1;

SET @sql_attach_cmd = '--' + CAST(@cnt as varchar(10)) + '/' + CAST(@cur_rows as varchar(10)) + ':' + CHAR(10) + 'EXEC sp_attach_db @dbname = ''' + @db + '''' + CHAR(10);
SET @sql_detach_cmd = '--' + CAST(@cnt as varchar(10)) + '/' + CAST(@cur_rows as varchar(10)) + ':' + CHAR(10) + 'EXEC sp_detach_db @dbname = ''' + @db + ''' , @skipchecks = ''true'';' + CHAR(10);

DECLARE cur_dbs_files CURSOR STATIC LOCAL FORWARD_ONLY
FOR SELECT physical_name FROM sys.master_files WHERE database_id = DB_ID(@db) ORDER BY [file_id];
OPEN cur_dbs_files

FETCH NEXT FROM cur_dbs_files INTO @file
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_attach_cmd = @sql_attach_cmd + ' ,@filename' + CAST(@i AS NVARCHAR(10)) + ' = ''' + @file + '''' + CHAR(10);
SET @i = @i + 1;
FETCH NEXT FROM cur_dbs_files INTO @file
END

CLOSE cur_dbs_files;
DEALLOCATE cur_dbs_files;

IF @DetachOrAttach = 0 BEGIN
PRINT @sql_attach_cmd;
END
ELSE
PRINT @sql_detach_cmd;
FETCH NEXT FROM cur_dbs INTO @db
Set @cnt = @cnt + 1
END
CLOSE cur_dbs;
DEALLOCATE cur_dbs;



UPDATED: Attach Detach with Set Offline With Rollback Immediate & Set Online & Drive Replace

ALTER PROCEDURE [dbo].[AutomatedDetachAttach]
(
--DECLARE
@DATABASES VARCHAR(MAX) = 'SQL%',
@DetachOrAttach BIT, --1 Detach, 0 Attach
@DestinationServer varchar(150),
@SetOfflineThenOnline BIT
)
AS

Set nocount on

/*
exec msdb.[dbo].[AutomatedDetachAttach]
@DetachOrAttach = 0 ,
@DestinationServer = 'new_server',
@SetOfflineThenOnline = 1
*/

BEGIN

IF OBJECT_ID('tempdb.DBO.#DATABASES') IS NOT NULL drop table #DATABASES
CREATE TABLE #DATABASES (name varchar(1024));

IF @DetachOrAttach IS NULL SET @DetachOrAttach = 1

INSERT INTO #DATABASES select DBName from msdb.dbo.Driver

DECLARE @db NVARCHAR(200), @file NVARCHAR(MAX), @sql_detach_cmd NVARCHAR(MAX), @sql_attach_cmd NVARCHAR(MAX), @cnt int, @cur_rows int
DECLARE @i INT, @TotalSize INT, @TotalDrives INT, @Type int;

SET @cnt = 1
Set @TotalSize = (Select SUM(size)*8 / 1024 / 1024 as 'Total GB' FROM sys.databases d
inner join sys.master_files f on d.database_id = f.database_id
JOIN #DATABASES DB ON DB.NAME = D.name)

Set @TotalDrives = (Select COUNT(DISTINCT(substring(f.physical_name,1, 1))) FROM sys.databases d
inner join sys.master_files f on d.database_id = f.database_id
JOIN #DATABASES DB ON DB.NAME = D.name)

DECLARE cur_dbs CURSOR STATIC LOCAL FORWARD_ONLY
FOR SELECT RTRIM(LTRIM([name])) FROM #DATABASES

ORDER BY NAME ASC
OPEN cur_dbs

FETCH NEXT FROM cur_dbs INTO @db

Set @cur_rows = @@CURSOR_ROWS

Print CHAR(10) + + CHAR(10) + '/* SUMMARY: ' + convert(varchar(10),@@CURSOR_ROWS) + ' Databases selected; ' + convert(varchar(10),@TotalSize) + 'gb total size; '
+ convert(varchar(10),@TotalDrives) + ' different drives */' + CHAR(10) + CHAR(10)

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @i = 1;

SET @sql_attach_cmd = '--' + CAST(@cnt as varchar(10)) + '/' + CAST(@cur_rows as varchar(10)) + ':' + CHAR(10) + 'EXEC sp_attach_db @dbname = ''' + @db + '''' + CHAR(10);

If @SetOfflineThenOnline = 0 Begin
SET @sql_detach_cmd = '--' + CAST(@cnt as varchar(10)) + '/' + CAST(@cur_rows as varchar(10)) + ':' + CHAR(10) + 'EXEC sp_detach_db @dbname = ''' + @db + ''' , @skipchecks = ''true'';' + CHAR(10);
End

If @SetOfflineThenOnline = 1 Begin
SET @sql_detach_cmd = '--' + CAST(@cnt as varchar(10)) + '/' + CAST(@cur_rows as varchar(10)) + ':' + CHAR(10) + 'Alter Database ' + @db + ' Set Offline With Rollback Immediate' + CHAR(10) + 'go' + CHAR(10) + 'Alter Database ' + @db + ' Set Online' + CHAR(10) + 'go' + CHAR(10) + 'EXEC sp_detach_db @dbname = ''' + @db + ''' , @skipchecks = ''true'';' + CHAR(10);
End

DECLARE cur_dbs_files CURSOR STATIC LOCAL FORWARD_ONLY
FOR SELECT physical_name,type FROM sys.master_files WHERE database_id = DB_ID(@db) ORDER BY [file_id];
OPEN cur_dbs_files

FETCH NEXT FROM cur_dbs_files INTO @file,@type
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_attach_cmd = @sql_attach_cmd + ' ,@filename' + CAST(@i AS NVARCHAR(10)) + ' = ''' + case @Type when 0 then replace(@file,'G:\','F:\') when 1 then replace(@file,'G:\','E:\') else @file end + '''' + CHAR(10);
--SET @sql_attach_cmd = @sql_attach_cmd + ' ,@filename' + CAST(@i AS NVARCHAR(10)) + ' = ''' + @file + '''' + CHAR(10);
SET @i = @i + 1;
FETCH NEXT FROM cur_dbs_files INTO @file,@type
END

CLOSE cur_dbs_files;
DEALLOCATE cur_dbs_files;

IF @DetachOrAttach = 0 BEGIN
PRINT @sql_attach_cmd;
END
ELSE
PRINT @sql_detach_cmd;
FETCH NEXT FROM cur_dbs INTO @db
Set @cnt = @cnt + 1
END
CLOSE cur_dbs;
DEALLOCATE cur_dbs;
END
GO



Fix Orphaned Users in a database:

Declare @Tbl table (Name sysname)
Insert into @Tbl (Name) Select Name from syslogins

Select
'EXEC sp_change_users_login ''Update_One'', ' + '''' + name + '''' + ', ' + '''' + name + '''' from sysusers
where
name not like 'ud_%'
and
name not like 'db_%'
and
name not like 'CREA\%'
and
name not in
(
'public','dbo','guest','INFORMATION_SCHEMA','sys'
)
and name in (Select Name from @Tbl)

OR

Script to fix and list out Orphan Users on all the databases on server

USE MASTER
GO
SET NoCount ON
DECLARE @VarDbId INT,
@SQL nvarchar(4000),
@VDBName nvarchar(260),
@OUCounter INT,
@Max_OUCounter INT
SELECT @VarDbId=4,
@SQL =''
CREATE TABLE #OrphaneUsers
(
ID INT IDENTITY (1,1) NOT NULL,
DBName VARCHAR(125) NULL ,
UserName sysname NULL ,
UserSID VARBINARY(85) NULL ,
LoginExists bit NULL
)
WHILE EXISTS
(SELECT database_id
FROM sys.databases
WHERE database_id>@VarDbId
AND state_desc ='ONLINE'
)
BEGIN
SELECT TOP 1
@SQL ='Create table #OrphaneUser

(UserName sysname null,

UserSID varbinary(85) null )

insert into #OrphaneUser exec ' + name+ '.dbo.sp_change_users_login ''report''

insert into #OrphaneUsers(DBName,UserName,UserSID,LoginExists) select '''+ name+''' as[dbname], UserName, UserSID,0 from #OrphaneUser

drop Table #OrphaneUser',
@VDBName=name
FROM sys.databases
WHERE database_id>@VarDbId
AND state_desc ='ONLINE'
ORDER BY database_id
EXEC SP_Executesql @SQL
SELECT TOP 1
@VarDbId=database_id
FROM sys.databases
WHERE database_id>@VarDbId
AND state_desc ='ONLINE'
END
UPDATE #OrphaneUsers
SET LoginExists=1
FROM #OrphaneUsers
JOIN syslogins
ON #OrphaneUsers.UserName=syslogins.NAME
SELECT @OUCounter =0,
@Max_OUCounter =COUNT(0)
FROM #OrphaneUsers
WHERE LoginExists=1
WHILE EXISTS
(SELECT TOP 1
id
FROM #OrphaneUsers
WHERE LoginExists=1
AND id >@OUCounter
)
BEGIN
SELECT TOP 1
@OUCounter=id
FROM #OrphaneUsers
WHERE LoginExists=1
AND id >@OUCounter
SELECT @SQL ='EXEC '+DBName+'.dbo.sp_change_users_login ''Auto_Fix'', '''+UserName+''', NULL, '''+UserName+''''
FROM #OrphaneUsers
WHERE LoginExists=1
AND id =@OUCounter
EXEC SP_Executesql @SQL
PRINT @SQL
END
SELECT *
FROM #OrphaneUsers
DROP TABLE #OrphaneUsers


UPDATED:


/*Check ALL User Databases for ANY Orphaned Users and provide the fix in Messages tab*/

/*Orphaned User is a User that exists in a Database and it is not aligned with a Login or a Login does not exist*/

/*Script uses Exec dbo.sp_change_users_login 'report' - Maps an existing database user to a SQL Server login.*/


USE MASTER

GO

SET NoCount ON

DECLARE @VarDbId INT, @SQL nvarchar(4000), @VDBName nvarchar(260), @OUCounter INT, @Max_OUCounter INT


SELECT @VarDbId=4,

@SQL =''

CREATE TABLE #OrphaneUsers

(

ID INT IDENTITY (1,1) NOT NULL,DBName VARCHAR(125) NULL,UserName sysname NULL,UserSID VARBINARY(85) NULL ,LoginExists bit NULL

)

WHILE EXISTS

(SELECT database_id FROM sys.databases WHERE database_id > @VarDbId AND state_desc ='ONLINE'

)

BEGIN

SELECT TOP 1

@SQL ='Create table #OrphaneUser (UserName sysname null, UserSID varbinary(85) null)

insert into #OrphaneUser exec ' + name+ '.dbo.sp_change_users_login ''report''

insert into #OrphaneUsers(DBName,UserName,UserSID,LoginExists) select '''+ name+''' as[dbname], UserName, UserSID,0 from #OrphaneUser

drop Table #OrphaneUser',

@VDBName=name

FROM sys.databases WHERE database_id > @VarDbId AND state_desc ='ONLINE' ORDER BY database_id



EXEC SP_Executesql @SQL



SELECT TOP 1 @VarDbId=database_id FROM sys.databases WHERE database_id > @VarDbId AND state_desc ='ONLINE'

END



UPDATE #OrphaneUsers SET LoginExists = 1 FROM #OrphaneUsers JOIN syslogins ON #OrphaneUsers.UserName=syslogins.NAME

SELECT @OUCounter = 0, @Max_OUCounter =COUNT(0)

FROM #OrphaneUsers WHERE LoginExists = 1



WHILE EXISTS

(SELECT TOP 1

id

FROM #OrphaneUsers WHERE LoginExists = 1 AND id > @OUCounter

)

BEGIN

SELECT TOP 1 @OUCounter=id

FROM #OrphaneUsers WHERE LoginExists=1 AND id > @OUCounter

SELECT @SQL ='EXEC '+DBName+'.dbo.sp_change_users_login ''Update_One'', '''+UserName+''', '''+UserName+''''

FROM #OrphaneUsers WHERE LoginExists = 1 AND id = @OUCounter

PRINT @SQL

END



SELECT ID, DBName, UserName, UserSID, Case LoginExists When 0 Then 'N | Orphaned User' When 1 Then 'Y | See Messages' End as 'LoginExists' FROM #OrphaneUsers

DROP TABLE #OrphaneUsers