info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
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
|
|
|
|
|
|
|
|
|
|