db Backup and Restore....to make life easier

--############################################################

--Step 1. :: backup the target database

--Step 2. :: Kill connections from the source database

use mastergo

DECLARE @kill varchar(8000) = '';SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'FROM master..sysprocesses WHERE dbid = db_id('Reference Data Warehouse')

EXEC(@kill);

--Step 3. :: Single User Mode

ALTER Database [Reference Data Warehouse] SET Single_UserGO

--Step 4. :: Disconnect the session!

--Step 5. :: Multi-User Mode

ALTER Database [Reference Data Warehouse] SET Multi_User
GO

--##############################################################

EXEC sp_change_users_login 'UPDATE_ONE','MDMDQuser','MDMDQuser'


--First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

--If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

--If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Check Transaction Log available space

Check Transaction Log available space

DBCC SQLPERF(logspace)

SQLAgentOperator cannot edit Jobs for other owners

Yes, you read that right.  Only the sysadmin can do this.

my_permissions

USE [sandbox]
GO

select * from sys.fn_my_permissions(NULL, 'database')

select * from sys.fn_my_permissions(NULL, 'server')

T-SQL Create table syntax

if exists (select * from sysobjects where id = object_id('dbo.tblMetricEvaluationMethod') and sysstat & 0xf = 3)

    drop table dbo.tblMetricEvaluationMethod

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE dbo.tblMetricEvaluationMethod (

     memID int IDENTITY (1, 1) NOT NULL ,

     memText varchar(200) ,

     memTaskID int ,

     memCreateDate datetime ,

     memCreatedBy int ,

     memModifiedDate datetime ,

     memModifiedByec_EventDescription varchar(1000)

CONSTRAINT PK_tblMetricEvaluationMethod PRIMARY KEY CLUSTERED ( memID )

CONSTRAINT FK_tblMetricEvaluationMethod FOREIGN KEY (memCreatedBy) REFERENCES tblUsers(UserID)

)

GO

SET ANSI_PADDING OFF
GO



===========================================================

Important to remember that ORDER BY works below when TOP is used.
--------------------------------------------------------------------

USE [database]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].vw_name
as
SELECT TOP 10
[col1], [col2], ...
FROM dbo.[tblName]
GROUP BY [col1], [col2], ...
ORDER BY [col1], [col2], ...
GO

Quick View Table Structure and Contents

Viewing table Structure & Contents

USE [db_name]

go

declare @tableName as nvarchar(50) = 'test'

declare @SQLstmt as nvarchar(100) = 'SELECT top 10* FROM [dbo].[' + @tableName + ']'

SELECT obj.type_desc,  OBJECT_NAME(col.object_id) as 'table_name', col.column_id, col.name as 'column_name'

, TYPE_NAME(col.user_type_id) as 'DataType_name'

--, col.system_type_id, col.user_type_id

, col.max_length, col.[precision], col.scale, col.collation_name, col.is_nullable, col.is_ansi_padded, col.is_rowguidcol, col.is_identity

FROM sys.objects obj inner join sys.columns col on obj.object_Id=col.object_Id

WHERE obj.Name=@tableName

--AND col.name LIKE '%org%'

ORDER BY col.column_id

EXEC sp_executesql @SQLstmt

Month and Day Leading Zeros? 09 not 9

declare @REL_START_DATE as datetime = GetDate()

SELECT cast(year(@REL_START_DATE) as varchar(4)) + '-' + RIGHT('0' + cast(month(@rel_start_date) as varchar(2)),2) + '-' + right('0' + cast(day(@rel_start_date) as varchar(2)),2) + ' 00:00:00.000' as 'REL_START_DATE'

Always On

If secondary runs out of space, make sure the Always On connection is removed and re-added so that no data is lost between the two.