Databaselerin Detach-Attach Scriptini Oluşturmak

Aşağıdaki sp ile mevcut bir ortamda olan db lerin attach scriptlerini generate edeceğiz, disk kopyalamada, disk klonlama işlemlerinde hızlıca database’leri attach etmek için lazım olabilir. Hedef sunucu da disk adresleri değişmişse scriptin çıktısını değiştirmeniz gerekebilir, ama aynı harflerle diskler mount edilmişse direk çalıştırılabilir.

----Tüm databaseleri detach etmek için script
DECLARE @dbName varchar(255);
DECLARE DBCURSOR CURSOR LOCAL FOR
SELECT name
FROM sys.databases 
WHERE database_id>4; 
---- System dbleri hariç sadece user databaseleri
OPEN DBCURSOR
FETCH Next from DBCURSOR INTO @dbName
WHILE @@FETCH_STATUS = 0 
BEGIN
PRINT 'EXEC sp_detach_db ' + @dbName + CHAR(10) -- CHAR(10) for newline 
+ 'GO' + CHAR(10) 
+'--- '+ @dbName + ' databasei için detach scripti oluşturuldu.''' 
+ CHAR(10) + 'GO'
FETCH NEXT FROM DBCURSOR INTO @dbName
END

Aşağıdaki gibi bir çıktı verecektir..
EXEC sp_detach_db omer
GO
--- omer databasei için detach scripti olusturuldu.'
GO
EXEC sp_detach_db omer_archive
GO
--- omer_archive databasei için detach scripti olusturuldu.'
GO
EXEC sp_detach_db LoginInfoDB
GO
--- LoginInfoDB databasei için detach scripti olusturuldu.'
GO

----Tüm databaseleri attach etmek için script üreten stored procedure
use master
GO

IF EXISTS (SELECT name
FROM sys.sysobjects
WHERE name = 'SpGenerateAttachScript')
DROP PROCEDURE SpGenerateAttachScript
GO

CREATE PROC SpGenerateAttachScript
@db SYSNAME = NULL
AS
SET nocount ON
SET concat_null_yields_null OFF
DECLARE @cmd VARCHAR(1000),
@a VARCHAR(MAX),
@Filecnt INT,
@cnt INT,
@fileid INT,
@sq CHAR(1),
@dq CHAR(2),
@TempFilename VARCHAR(1000),
@TempFilename1 VARCHAR(1000)
SET @sq = ''''
SET @dq = ''''''
SET @cnt = 1
SET @fileid = 1
IF @db IS NOT NULL
BEGIN
CREATE TABLE #1 (
fileid INT,
filename SYSNAME,
name SYSNAME)
SET @cmd = 'Insert into #1 (fileid,filename,name) select file_id, physical_name, name 
from sys.master_files where database_id = db_id('''
+ @db
+ ''')'
EXEC( @cmd)
SELECT @filecnt = (SELECT COUNT(* )
FROM #1)
WHILE @cnt <= @filecnt
BEGIN
SELECT @TempFileName = filename
FROM #1
WHERE fileid = @fileid
SELECT @TempFileName = RTRIM(@TempFileName)
SELECT @a = @a
+ CHAR(13)
+ CHAR(9)
SELECT @a = @a
+ '(Filename = '
+ @sq
+ @TempFilename
+ @sq
+ '),'
SET @cnt = @cnt
+ 1
SET @fileid = (SELECT MIN(fileid)
FROM #1
WHERE fileid > @fileid)
END
SELECT @a = 'CREATE DATABASE '
+ @db
+ ' ON '
+ @a
SELECT @a = (SELECT SUBSTRING(@a,1,LEN(@a)- 1)) --Trim off the final comma; I'm sure there is a better way to do this... 
SELECT @a = @a
+ '
FOR ATTACH;
GO'
PRINT @a
END
ELSE
BEGIN
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('tempdb','master','msdb','model')
AND database_id NOT IN (SELECT database_id
FROM sys.databases
WHERE state > 0)
ORDER BY database_id
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @db
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE #2 (
fileid INT,
filename SYSNAME,
name SYSNAME)
SET @cmd = 'Insert into #2 (fileid,filename,name) select file_id, physical_name, name 
from sys.master_files where database_id = db_id('''
+ @db
+ ''')'
EXEC( @cmd)
SELECT @filecnt = (SELECT COUNT(* )
FROM #2)
WHILE @cnt <= @filecnt
BEGIN
SELECT @TempFileName = filename
FROM #2
WHERE fileid = @fileid
SELECT @TempFileName = RTRIM(@TempFileName)
SELECT @a = @a
+ CHAR(13)
+ CHAR(9)
SELECT @a = @a
+ '(Filename = '
+ @sq
+ @TempFilename
+ @sq
+ '),'
SET @cnt = @cnt
+ 1
SET @fileid = (SELECT MIN(fileid)
FROM #2
WHERE fileid > @fileid)
END
SELECT @a = 'CREATE DATABASE '
+ @db
+ ' ON '
+ @a
SELECT @a = (SELECT SUBSTRING(@a,1,LEN(@a)- 1))
SELECT @a = @a
+ '
FOR ATTACH;
GO

'
PRINT @a
SELECT @a = ' '
DROP TABLE #2
SET @cnt = 1
SET @fileid = 1
FETCH NEXT FROM db_cursor
INTO @db
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
Sp yi create ettikten sonra çalıştırıyoruz.
exec SpGenerateAttachScript
Aşağıdaki gibi bir çıktı vermelidir.
CREATE DATABASE omer ON
(Filename = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\omer.mdf'),
(Filename = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\omer_log.ldf')
FOR ATTACH;
GO

CREATE DATABASE LoginInfoDB ON
(Filename = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\LoginInfoDB.mdf'),
(Filename = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\LoginInfoDB_log.ldf')
FOR ATTACH;
GO

Tüm DB leri offline-Online Yapmak

-- Tüm dbleri online yap(sistem dbleri hariç)
BEGIN
DECLARE @db sysname, @q varchar(max);
DECLARE cur_db CURSOR FOR
SELECT name FROM sys.databases WHERE database_id>4 and state=6 order by name;
OPEN cur_db;
WHILE 1=1
BEGIN
FETCH NEXT FROM cur_db INTO @db;
IF @@FETCH_STATUS <> 0
BREAK;
SET @q = N'ALTER DATABASE [' + @db + N'] SET ONLINE WITH NO_WAIT';
---- EXEC(@q);
Print(@q); 
END;
CLOSE cur_db;
DEALLOCATE cur_db;
END;


-- Tüm dbleri offline yap(sistem dbleri hariç)
BEGIN
DECLARE @db sysname, @q varchar(max);
DECLARE cur_db CURSOR FOR
SELECT name FROM sys.databases WHERE database_id>4 and state=0 order by name;
OPEN cur_db;
WHILE 1=1
BEGIN
FETCH NEXT FROM cur_db INTO @db;
IF @@FETCH_STATUS <> 0
BREAK;
SET @q = N'ALTER DATABASE [' + @db + N'] SET OFFLINE WITH NO_WAIT';
---- EXEC(@q);
Print(@q); 
END;
CLOSE cur_db;
DEALLOCATE cur_db;
END;

Missing index ve Unused indexleri Bulmak

--unused indexes
Declare @dbid int 
Select @dbid = db_id() 
Select objectname=object_name(i.object_id), indexname=i.name, i.index_id from sys.indexes i 
join sys.objects o on i.object_id = o.object_id where objectproperty(o.object_id,'IsUserTable') = 1 
and i.index_id NOT IN (select s.index_id from sys.dm_db_index_usage_stats s where s.object_id=i.object_id 
and i.index_id=s.index_id and database_id = @dbid ) order by objectname,i.index_id,indexname asc


-- Missing indexes
SELECT MID.[statement] AS ObjectName
,MID.equality_columns AS EqualityColumns
,MID.inequality_columns AS InequalityColms
,MID.included_columns AS IncludedColumns
,MIGS.last_user_seek AS LastUserSeek
,MIGS.avg_total_user_cost 
* MIGS.avg_user_impact 
* (MIGS.user_seeks + MIGS.user_scans) AS Impact
,N'CREATE NONCLUSTERED INDEX <Add Index Name here> ' + 
N'ON ' + MID.[statement] + 
N' (' + MID.equality_columns 
+ ISNULL(', ' + MID.inequality_columns, N'') +
N') ' + ISNULL(N'INCLUDE (' + MID.included_columns + N');', ';')
AS CreateStatement
FROM sys.dm_db_missing_index_group_stats AS MIGS
INNER JOIN sys.dm_db_missing_index_groups AS MIG
ON MIGS.group_handle = MIG.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS MID
ON MIG.index_handle = MID.index_handle
WHERE database_id = DB_ID()
AND MIGS.last_user_seek >= DATEDIFF(month, GetDate(), -1)
ORDER BY Impact DESC;

SQL tablo kayıt sayılarını bulmak

Mssql tablodaki satır sayısını bulmak.. sp_MSForEachTable  kullanımı örnek.

SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
-- WHERE [Tables].name = N'name of the table'
GROUP BY SCHEMA_NAME(schema_id), [Tables].name

-- yada sp_MSForEachTable ile güncel rowcount alabilirsiniz.(büyük dblerde kullanmayın,sistemi yorabilir)
CREATE TABLE #counts
( table_name varchar(255),
row_count int)
EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY row_count DESC
DROP TABLE #counts

Active Directory User listesini Powershell ile listelemek

Powershelli kullanarak Activedirectory de bulunan tüm userları UTF8  setinde excel dosyaya export eder.

-- Tüm AD user listesini powershell ile almak için
Get-AdUser -filter "samaccountname -like '*'" -Properties * | select displayname, samaccountname |Export-Csv -Path "C:\Info\Kullanicilar.csv" -NoTypeInformation -encoding UTF8

-- Arama yapıp user listesini powershell ile almak için
Get-AdUser -filter "samaccountname -like 'UserKodu*'" -Properties * | select displayname, samaccountname |Export-Csv -Path "C:\Info\Kullanicilar.csv" -NoTypeInformation -encoding UTF8