MSSql Admin Tips&Techs

_mssql-admin-tips-and-techs.md

A script collection for MSSql databases administration

1. Enable SA user
2. --
3. Restore database

create-database.sql

CREATE DATABASE [MyDataBase] ON  PRIMARY 
( NAME = N'MyDataBase_Data', FILENAME = N'path/to/data/file.mdf' , SIZE = 167872KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
    LOG ON 
( NAME = N'MyDataBase_Log', FILENAME = N'path/to/log/file_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
GO

drop-data-base-single-user.sql

ALTER DATABASE [MyDataBase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [MyDataBase]
GO

enable-sa-user.sql

USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'z43VGYT@Iu*60i'
GO
ALTER LOGIN [sa] ENABLE
GO
ALTER LOGIN [sa] WITH PASSWORD=N'', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

restore-database.sql

ALTER DATABASE [MyDataBase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- List of the logical names of databases from a backup files
RESTORE FILELISTONLY FROM DISK = 'diretory-path\backup-file.bak'

-- Restore the database from backup file
RESTORE DATABASE MyDataBase FROM DISK = 'diretory-path\backup-file.bak'
WITH REPLACE,
MOVE 'dbname' TO 'restore-directory\dbname-file.mdf',
MOVE 'dbname_log' TO 'restore-directory\dbname-log-file.ldf';
GO

show-dependents-from-tables.sql

DECLARE @tableName VARCHAR(64) = 'table-name'

SELECT
	SO_P.name AS [parent TABLE]
	,SC_P.name AS [parent COLUMN]
	,'is a foreign key of' AS [direction]
	,SO_R.name AS [referenced TABLE]
	,SC_R.name AS [referenced COLUMN]
	,*
FROM sys.foreign_key_columns FKC
INNER JOIN sys.objects SO_P ON SO_P.object_id = FKC.parent_object_id
INNER JOIN sys.columns SC_P ON (SC_P.object_id = FKC.parent_object_id) AND (SC_P.column_id = FKC.parent_column_id)
INNER JOIN sys.objects SO_R ON SO_R.object_id = FKC.referenced_object_id
INNER JOIN sys.columns SC_R ON (SC_R.object_id = FKC.referenced_object_id) AND (SC_R.column_id = FKC.referenced_column_id)
WHERE
 ((SO_P.name = @tableName) AND (SO_P.type = 'U')) OR
 ((SO_R.name = @tableName) AND (SO_R.type = 'U'))