Data Science Lab

SELECT

       volume_mount_point,

       used_gb = CAST(MIN(total_bytes / 1024. / 1024 / 1024) AS NUMERIC(8,1)),

       available_gb = CAST(MIN(available_bytes / 1024. / 1024 / 1024) AS NUMERIC(8,1)),

       total_gb = CAST(MIN((total_bytes+available_bytes) / 1024. / 1024 / 1024) AS NUMERIC(8,1))

FROM sys.master_files AS f

       CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)

GROUP BY volume_mount_point;

Setup.exe /q /ACTION=editionupgrade /INSTANCENAME=MSSQLSERVER /PID=XXXXX-XXXXX-XXXXX-XXXXX-XXXXX /IACCEPTSQLSERVERLICENSETERMS

Date                    7/29/2019 1:23:41 PM

Log                       Job History (syspolicy_purge_history)

Step ID                3

Server                

Job Name                         syspolicy_purge_history

Step Name                       Erase Phantom System Health Records.

Duration                           00:00:00

Sql Severity        0

Sql Message ID  0

Operator Emailed          

Operator Net sent         

Operator Paged

Retries Attempted          0

Message

Executed as user: NT Service\SQLSERVERAGENT. The job script encountered the following errors. These errors did not stop the script:  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory.  '  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'SQLSERVER:'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'SQLSERVER:' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.  '.  Process Exit Code -1.  The step failed.

C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules

-- Replace live with staging

BEGIN TRAN

DROP TABLE DataTable;

EXEC sp_rename DataTable_Staging, DataTable;

COMMIT

-- Swap live and staging

BEGIN TRAN

EXEC sp_rename DataTable, DataTable_Old;

EXEC sp_rename DataTable_Staging, DataTable;

EXEC sp_rename DataTable_Old, DataTable_Staging;

COMMIT

-- Replace live with staging

BEGIN TRAN

TRUNCATE TABLE DataTable;

ALTER TABLE DataTable_Staging SWITCH TO DataTable;

COMMIT

-- Swap live and staging

/* Note: An extra table, DataTable_Old, is required to temporarily hold the data being replaced before it is moved into DataTable_Staging. The rename-based approach did not require this extra table. */

BEGIN TRAN

ALTER TABLE DataTable SWITCH TO DataTable_Old;

ALTER TABLE DataTable_Staging SWITCH TO DataTable;

ALTER TABLE DataTable_Old SWITCH TO DataTable_Staging;

COMMIT

ET

External table

IT

Internal table

PC

Assembly (CLR) stored procedure

PG

Plan guide

RF

Replication-filter-procedure

S

System table

SQ

Service queue

TA

Assembly (CLR) DML Trigger

X

Extended procedure

-

CLR User-Defined Data Type (UDDT)

C

Check constraint

D

Default constraint

PK

Primary key constraint

TR

DML trigger

UQ

Unique constraint

1

F

Foreign key constraint

Need to be dropped before tables, as described below.

2

V

View

Should be dropped before tables, since they can have direct or indirect SCHEMABINDING.

FN

SQL scalar function

IF

SQL inline table-valued function

TF

SQL table-valued-function

3

P

Stored procedure

Need to be dropped before tables, but after views and functions (since procedures can reference views and functions, but can't be referenced by them except in rare cases using OPENQUERY()).

4

U

Table (user-defined)

Dropped after foreign keys and any type of module that can reference them with SCHEMABINDING.

5

TT

Table type

Dropped after tables and modules, but before old-style rules that could be bound to them. Table types need to be dropped before other types.

Alias types

6

SO

Sequence object

Dropped after tables/modules because they can't be dropped if any table or module references them.

R

Rule (old-style, stand-alone)

D

Old-style CREATE DEFAULT

Partition functions

7

Partition schemes

Can't be dropped until after partition functions.

8

SN

Synonym

I drop these toward the end because they can reference many of the items above (though this entity is not really prone to any issues on its own, unless you have external references pointing at them).

9

Schemas

I drop these almost last because almost all entities above can belong to a certain schema. For any entities you want to keep but move to a different schema, you'll need to first use ALTER SCHEMA ... TRANSFER.

10

Roles and Users

For users you don't want to keep, you'll need to first remove them from user-defined role membership and ownership, as well as ownership of any schemas. 

(For users you do want to keep, you may also want to remove any inappropriate permissions, but I'll deal with that in a separate post.) 

For roles you don't want to keep, you'll need to first remove any members; but you can't remove members who also happen to own roles.

try { drop view 1; go; drop view 2; }

-- script to drop Foreign Keys

USE [master];

GO

SET NOCOUNT ON;

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  ALTER TABLE ' + objectname + N'

    DROP CONSTRAINT ' + fkname + N';

END TRY

BEGIN CATCH

  SELECT N''FK ' + fkname + N' failed. Run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT fkname = QUOTENAME(fk.[name]),

    objectname = QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name])

  FROM sys.foreign_keys AS fk

  INNER JOIN sys.objects AS t

  ON fk.parent_object_id = t.[object_id]

  INNER JOIN sys.schemas AS s

  ON t.[schema_id] = s.[schema_id]

) AS src;

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop views

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  DROP VIEW ' + objectname + N';

END TRY

BEGIN CATCH

  SELECT N''View ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(v.[name])

  FROM sys.views AS v

  INNER JOIN sys.schemas AS s

  ON v.[schema_id] = s.[schema_id]

  WHERE v.is_ms_shipped = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop functions

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP FUNCTION ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Function ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

  AND o.[type] IN ('FN','IF','TF')

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop procedures

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP PROCEDURE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Procedure ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.[type] = 'P'

  AND o.is_ms_shipped = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop user tables

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP TABLE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Table ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.tables AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop table types

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Type ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.types AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_table_type = 1

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop alias types

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Type ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.types AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.user_type_id > 256

  AND o.is_table_type = 0

  AND o.is_assembly_type = 0  -- not a CLR UDT

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop sequences

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP SEQUENCE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Sequence ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.sequences AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop old-style Rules

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP RULE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Rule ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

  AND [type] = 'R'

  AND parent_object_id = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop old-style Defaults

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP DEFAULT ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Default ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

  AND [type] = 'D'

  AND parent_object_id = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop partition functions

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP PARITITON FUNCTION ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Partition function ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME([name])

  FROM sys.partition_functions

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop partition schemes

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP PARITITON SCHEME ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Partition scheme ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME([name])

  FROM sys.partition_schemes

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop synonyms

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP SYNONYM ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Synonym ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.synonyms AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop schemas

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP SCHEMA ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Schema ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name])

  FROM sys.schemas AS s

  WHERE [schema_id] BETWEEN 5 AND 16383

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to re-route default schemas

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  ALTER USER ' + QUOTENAME(name) + N' WITH DEFAULT_SCHEMA = dbo;

 END TRY

 BEGIN CATCH

  SELECT N''User ' + p.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS p

WHERE p.default_schema_name IS NOT NULL

AND NOT EXISTS

(

  SELECT 1

  FROM sys.schemas AS s

  WHERE name = p.default_schema_name

);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to change ownership of roles to dbo

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  ALTER AUTHORIZATION ON ROLE::' + QUOTENAME(r.name) + N' TO dbo;

 END TRY

 BEGIN CATCH

   SELECT N''Role ' + r.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS r

INNER JOIN sys.database_principals AS u

ON r.owning_principal_id = u.principal_id

WHERE r.[type] = 'R'

AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')

AND u.name NOT LIKE N'##%##' -- hopefully you don't name users/roles this way!

AND r.is_fixed_role = 0;

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to change ownership of schemas to dbo

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  ALTER AUTHORIZATION ON SCHEMA::' + QUOTENAME(name) + N' TO dbo;

 END TRY

 BEGIN CATCH

  SELECT N''Schema ' + name + N' failed - run the script again.'',

    ERROR_MESSAGE(); END CATCH

'

FROM sys.schemas

WHERE [schema_id] BETWEEN 5 AND 16383

AND principal_id BETWEEN 5 AND 16383;

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to remove members from roles

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

' + CASE WHEN CONVERT(tinyint,

  PARSENAME(CONVERT(nvarchar(128),

  SERVERPROPERTY(N'ProductVersion')),4)) >= 11 -- 2012+

 THEN

  N'ALTER ROLE ' + QUOTENAME(r.name) + N' DROP MEMBER '

   + QUOTENAME(m.name) + N';'

 ELSE

  N'EXEC [sys].[sp_droprolemember] @rolename = N''' + r.name

    + ''', @membername = N''' + m.name + N''';'

 END + N'

 END TRY

 BEGIN CATCH

   SELECT N''Role ' + r.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_role_members AS rm

INNER JOIN sys.database_principals AS r

ON rm.role_principal_id = r.principal_id

AND r.is_fixed_role = 0

INNER JOIN sys.database_principals AS m

ON rm.member_principal_id = m.principal_id;

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop roles

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  DROP ROLE ' + QUOTENAME(r.name) + N';

 END TRY

 BEGIN CATCH

  SELECT N''Role ' + r.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS r

WHERE r.[type] = 'R'

AND r.name <> N'public'

AND r.is_fixed_role = 0;

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop users

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

  DROP USER ' + QUOTENAME(u.name) + N';

 END TRY

 BEGIN CATCH

  SELECT N''User ' + u.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS u

WHERE u.[type] IN ('U','S')

AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')

AND u.name NOT LIKE N'##%##';

SELECT @sql;

--EXEC sys.sp_executesql @sql;

sqlcmd -S tcp:<computer name>,<port number>

sqlcmd –S tcp:SQL-A

sqlcmd –S tcp:SQL-A –U sa –P Pa$$w0rd

sqlcmd –S tcp:10.10.10.10

sqlcmd –S tcp:10.10.10.10 –U sa –P Pa$$w0rd

sqlcmd –S tcp:SQL-A\TEST

sqlcmd –S tcp:SQL-A\TEST –U sa –P Pa$$w0rd

sqlcmd –S tcp:SQL-A,1433

sqlcmd –S tcp:SQL-A,1433 –U sa –P Pa$$w0rd

sqlcmd –S tcp:10.10.10.10,1433

sqlcmd –S tcp:10.10.10.10,1433 –U sa –P Pa$$w0rd

sqlcmd –S tcp:SQL-A,51613

sqlcmd –S tcp:SQL-A,51613 –U sa –P Pa$$w0rd

sqlcmd –S tcp:10.10.10.10,51613

sqlcmd –S tcp:10.10.10.10,51613 –U sa –P Pa$$w0rd

\\[COMPUTER NAME OR IP ADDRESS]\pipe\sql\query

\\ MYSERVER \ pipe \ sql \ query

\\[COMPUTER NAME OR IP ADDRESS]\pipe\MSSQL$[SQL Server Instance Name]\sql\query

\\SQL-A\pipe\MSSQL$TEST\sql\query

sqlcmd –S np:\\SQL-A\pipe\sql\query

sqlcmd –S np:\\SQL-A\pipe\sql\query –U sa –P Pa$$w0rd

sqlcmd –S np:\\SQL-A\pipe\MSSQL$TEST\sql\query

sqlcmd –S np:\\SQL-A\pipe\MSSQL$TEST\sql\query –U sa –P Pa$$w0rd

sqlcmd –S lpc:SQL-A

sqlcmd –S lpc:SQL-A –U sa –P Pa$$w0rd

sqlcmd –S lpc:SQL-A\TEST

sqlcmd –S lpc:SQL-A\TEST –U sa –P Pa$$w0rd

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 99

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1,

      @databasemail_profile=N'DBServerAlerts_Profile', -- replace with your Agent's profile

      @use_databasemail=1

GO  

USE [msdb]

GO

EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1

GO

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\SQLServerAgent

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\SQLServerAgent.

DECLARE @SERVER_ROLE nvarchar(50)

SELECT

    @SERVER_ROLE = A.ROLE_DESC

FROM sys.dm_hadr_availability_replica_states AS A

    INNER JOIN sys.availability_groups AS B ON A.GROUP_ID = B.GROUP_ID

WHERE A.IS_LOCAL = 1

IF @SERVER_ROLE <> 'PRIMARY'

       EXEC msdb.dbo.sp_stop_job N'Job Name' ;