Sql Server Most Used Commands

quick link

rdp remote desktop shortcut

CTRL+ALT+BREAK   full-screen or window mode
CTRL+ALT+END     similar to CTRL+ALT+DELETE
ALT+INSERT       similar to ALT+TAB

Powershell

$PSVersionTable

$PSVersionTable.PSVersion

# du command, run as administrartor, need to suppress warning
gci . | %{$f=$_; gci -r $_.FullName| measure-object -property length -sum | select  @{Name="Name"; Expression={$f}} , @{Name="Sum (MB)"; Expression={  "{0:N3}" -f ($_.sum / 1MB) }}, Sum } | sort Sum -desc | format-table -Property Name,"Sum(MB)", Sum -autosize

# tail or tail -f
cd "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MXPPROD\MSSQL\Log"

Get-Content ./log.log -Tail 10  --> powershell 3.0
Get-Content .\test.txt -Wait -Tail 1 
Get-Content ERRORLOG | Select-Object -Last 10  --> powershell 2.0
Get-Content -Path ERRORLOG -Wait
Get-Content -Path "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MXPPROD\MSSQL\Log\ERRORLOG" -Wait

# loop
while ($true) {Clear-Host; gc E:\test.txt | select -last 3; sleep 2 }

-- delete old log files
Get-ChildItem ./HOTARCHIVES_ToDSk*.htm | Where-Object {-not $_.PSIsContainer -and (Get-Date).Subtract($_.CreationTime).Days -gt 308 } | Out-File test.txt
Get-Content test.txt | Measure-Object
Get-Content test.txt | Measure-Object -line -char -word

Get-ChildItem ./HOTARCHIVES_ToDSk*.htm | Where-Object {-not $_.PSIsContainer -and (Get-Date).Subtract($_.CreationTime).Days -gt 308 } | Remove-Item -WhatIf

Get-ChildItem ./HOTARCHIVES_ToDSk*.htm | Where-Object {-not $_.PSIsContainer -and (Get-Date).Subtract($_.CreationTime).Days -gt 308 } | Remove-Item

Get-ChildItem . | Where-Object {-not $_.PSIsContainer -and (Get-Date).Subtract($_.CreationTime).Days -gt 30 } | Remove-Item -WhatIf

Random Password

openssl rand -base64 6

openssl rand -base64 8

openssl rand -base64 12

Windows Temp Files:  
C:\Documents and Settings\username\Local Settings\Temp
C:\WINDOWS\Temp

check disk drive mapping: net use

check user groups: net localgroup

-- find whether a domain account has been locked or not same domain:
net user
net user  devintrasp07 /DOMAIN
net user  devintrasp07 /DOMAIN | find "Account active"
net user /domain devintrasp07 | find "Account active"
 --> Account active               Yes

net user mywindowuser /DOMAIN
The request will be processed at a domain controller for domain mywindowsdomain.xxxcom

User name                    xxxx 
Full Name                    xxxx 
Comment                      DBA account 
User's comment
Country/region code          000 (System Default)
Account active               Yes
Account expires              Never

Password last set            8/25/2011 10:54:55 AM
Password expires             Never
Password changeable          8/26/2011 10:54:55 AM
Password required            Yes
User may change password     No

Workstations allowed         All
Logon script
User profile
Home directory
Last logon                   3/5/2014 5:02:28 PM

Logon hours allowed          All

Local Group Memberships
Global Group memberships     *Domain Users         *DBBackups_RW
                             *MSSQLGRP_ALL
The command completed successfully.

net use

IF NOT EXIST M: net use M: \\mywindowsdomain\dfs\dba
IF NOT EXIST P: net use M: \\mywindowsdomain\dfs\dba\promotions
IF NOT EXIST U: net use U: \\mywindowsdomain.xxx.com\users$\UserDirectories02\hshen 

net user

net localgroup

net localgroup group_name
net localgroup Administrators
net localgroup Users
net localgroup "Remote Desktop users"

net localgroup group_name UserLoginName /add
net localgroup administrators John /add
net localgroup administrators domainname\username /add
net localgroup "Remote Desktop Users" UserLoginName  /add
net localgroup "Debugger users" UserLoginName /add
net localgroup "Power users" UserLoginName /add
net localgroup users domainname\username /add

net user userName

Net user add newuseLoginid  newuserPassword /domain
Net user add newuserLoginid  newuserPassword

net user username /DELETE
net user username /DELETE /DOMAIN

Net user loginid  /ACTIVE:NO /domain
Net user loginid /ACTIVE:YES  /domain
Net user loginid /Passwordchg:No
Net user loginid /Passwordchg:Yes

ver
systeminfo | findstr /B /C:"OS Name" /C:"OS Version"
systeminfo | findstr /C:"OS"

wmic useraccount where name='hshen'
wmic useraccount where name='hshen'  get sid

wmic os get Caption,CSDVersion /value

wmic useraccount get name
wmic useraccount get name,fullname

wmic useraccount where name='username' set disabled=false
wmic useraccount where name='username' set disabled=true
wmic useraccount where name='username' set PasswordRequired=false
wmic useraccount where name='username' rename newname
wmic useraccount where name='username' set passwordchangeable=false

wmic useraccount where (name='administrator' and domain='%computername%') get name,sid
wmic useraccount where (name='administrator' and domain='%userdomain%') get name,sid

whoami /?
whoami
whoami /user
whoami /ALL

sc windows service

Please the updated the script :
***********************************
::Note: Watch out for extra spaces: 
::SC QUERY state= all Works 
::SC QUERY sTate =all Fails! 
::***********************************
::

sc query MSSQLSERVER
sc queryex MSSQLSERVER

sc.exe config "MSSQLSERVER" obj= "MYWINDOWDOMAIN\MYWINDOWUSERNAME"  password= PASSWORD
sc.exe config "SQLSERVERAGENT" obj= "MYWINDOWDOMAIN\MYWINDOWUSERNAME"  password= PASSWORD
sc.exe config "MSSQLFDLauncher" obj= "MYWINDOWDOMAIN\MYWINDOWUSERNAME"  password= PASSWORD
sc.exe config "MSSQLServerOLAPService" obj= "MYWINDOWDOMAIN\MYWINDOWUSERNAME"  password= PASSWORD
sc.exe config "SQLBrowser" obj= "MYWINDOWDOMAIN\MYWINDOWUSERNAME"      password= PASSWORD
sc.exe config "ReportServer" obj= "MYWINDOWDOMAIN\MYWINDOWUSERNAME"    password= PASSWORD
sc.exe config "SQLWriter" obj= "MYWINDOWDOMAIN\MYWINDOWUSERNAME"      password= PASSWORD
sc.exe config "MsDtsServer100" obj= "MYWINDOWDOMAIN\MYWINDOWUSERNAME"      password= PASSWORD
::
net stop   MSSQLSERVER
net stop   SQLSERVERAGENT
net stop   MSSQLFDLauncher
net stop   MSSQLServerOLAPService
net stop   SQLBrowser
net stop   ReportServer
net stop   SQLWriter
net stop   MsDtsServer100
::
net start  MSSQLFDLauncher
net start  MSSQLSERVER
net start  SQLSERVERAGENT
net start  MSSQLServerOLAPService
net start  SQLBrowser
net start  ReportServer
net start  SQLWriter
net start  MsDtsServer100

sqlcmd login

sqlcmd -L  --> list servers

sqlcmd -E --> -E windows login

rem -e: echo input
rem -H hostname
sqlcmd -E -S JEWMXPDB01V\MXPPROD 

sqlcmd -E -S JEWMXPDB01V\MXPPROD -i BK_TRANSLOG_DATABASE_Ships.sql.txt -o BK_TRANSLOG_DATABASE_setup_log.txt

sqlcmd -E -S JEWMXPDB01V\MXPPROD -i BK_WEEKLY_DATABASE_Ships.sql.txt -o BK_WEEKLY_DATABASE_setup_log.txt

sqlcmd -e -E -S JEWPHOTOGRDB01V -i UserScriptlogins.sql -o UserScriptlogins.sql.log

sqlcmd -e -l 1200 -t 1200 -H GETMXPDB01V  -S GETMXPDB01V\MXPPROD -E -i test.sql -o test.sql_GETMXPDB01V.txt

sqlcmd -e -l 1200 -t 1200 -H GETMXPDB01V  -S GETMXPDB01V\MXPPROD -E -i test.sql -o test.sql_GETMXPDB01V.txt

select @@servername;
go

select db_name();
go

-- selecr @@version you can get both version 2000, 2005, 2008, 2012, 2014 and enterprise, standard edition info
select @@version;
go
select @@servername
go
exec sp_databases;
go

exec sp_configure;
go

mssql sqlcmd syntax

sqlcmd /?
usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f  | i:[,o:]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, enviroment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

# -r 1: Send error messages to STDOUT
# -V 1: Display all error messages with severity of greater than 1
# -e: echo sql statement
sqlcmd -U  -P  -d  [-S <server_name>[\<sql_server_instance>]] -r 1 -V 1 -i <script_name> -v [<variable>=<value>]...

sqlcmd -L ---> list servername and instance name


sqlcmd -E -d master -e -q "select @@servername"
sqlcmd -E -d master -e -Q "select @@servername"

sqlcmd -E -d master -e -Q "exec sp_helpserver;select @@servername;"

sqlcmd -E -d master -e -Q "declare @my_servername nvarchar(256);select @my_servername=@@servername;exec sp_dropserver @my_servername;declare @my_hostname nvarchar(256);select @my_hostname=host_name();exec sp_addserver @my_hostname, local;"

sqlcmd -E -d master -e -Q "exec sp_helpserver;select @@servername;"

net stop MSSQLSERVER

net start MSSQLSERVER

net localgroup Administrators

SELECT
SERVERPROPERTY('MachineName') AS [ServerName], 
SERVERPROPERTY('ServerName') AS [ServerInstanceName], 
SERVERPROPERTY('InstanceName') AS [Instance], 
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductVersion') AS [ProductVersion], 
Left(@@Version, Charindex('-', @@version) - 2) As VersionName;
go

SELECT
SERVERPROPERTY('InstanceName') AS [Instance]
go

set nocount on|off

-- show or not show number of rows selected, just like set feedback on in Oracle 
set nocount on
set nocount off

Current Time

SELECT GETDATE() AS CurrentTime,
       DATEADD(HH,-5,GETUTCDATE()) AS MiamiTime  ,
       GETUTCDATE() AS UTCTime,
       DATEDIFF(HH,GETUTCDATE(),GETDATE()) AS HOURS_DIFF_FROM_GMT;
go

Current session or Current Active Session

use msdb;
go

sp_who
sp_who2
sp_who3

-- sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]

exec sp_who;
go

exec sp_who 'ACTIVE';
go

exec sp_who @loginame='ACTIVE'
go

exec sp_who @loginame='MYDOMAIN\MYWINDOWUSER';
go

exec sp_who @loginame='ANJURJO';
go

-- not working
exec sp_who2 @loginame='ANJURJO';
go

exec sp_who 66;
go

exec sp_who2;
go

-- sp_who2

select count(*) from ncl#_sp_who2;
go

DROP TABLE ncl#_sp_who2;
go

CREATE TABLE ncl#_sp_who2 
(
   SPID INT,  
   Status VARCHAR(1000) NULL,  
   Login SYSNAME NULL,  
   HostName SYSNAME NULL,  
   BlkBy SYSNAME NULL,  
   DBName SYSNAME NULL,  
   Command VARCHAR(1000) NULL,  
   CPUTime INT NULL,  
   DiskIO INT NULL,  
   LastBatch VARCHAR(1000) NULL,  
   ProgramName VARCHAR(1000) NULL,  
   SPID2 INT,
   REQUESTID INT
) 
GO

TRUNCATE TABLE ncl#_sp_who2
GO

INSERT INTO ncl#_sp_who2
EXEC sp_who2
GO


SELECT *
FROM ncl#_sp_who2
where Status not in ( 'sleeping', 'BACKGROUND' )
order by status, Login
go


SELECT *
FROM ncl#_sp_who2
order by Login
GO

SELECT *
FROM ncl#_sp_who2
WHERE Login like '%SAN%'
GO

SELECT *
FROM ncl#_sp_who2
WHERE upper(Login) like upper('%San%')
GO

TRUNCATE TABLE ncl#_sp_who2
GO

DROP TABLE ncl#_sp_who2
GO

Declare @dbName varchar(1000)
set @dbName='MarineXProcurement'

;WITH DBConn(SPID,[Status],[Login],HostName,DBName,Command,LastBatch,ProgramName)
As
(
SELECT 
    SPID = s.session_id,
    Status = UPPER(COALESCE
        (
            r.status,
            ot.task_state,
            s.status, 
        '')),
    [Login] = s.login_name,
    HostName = COALESCE
        (
            s.[host_name],
            '  .'
        ),
    DBName = COALESCE
        (
            DB_NAME(COALESCE
            (
                r.database_id,
                t.database_id
            )),
            ''
        ),
    Command = COALESCE
        (
            r.Command,
            r.wait_type,
            wt.wait_type,
            r.last_wait_type,
            ''
        ),
    LastBatch = COALESCE
        (
            r.start_time,
            s.last_request_start_time
        ),
    ProgramName = COALESCE
        (
            s.program_name, 
            ''
        )
FROM
    sys.dm_exec_sessions s
LEFT OUTER JOIN
    sys.dm_exec_requests r
ON
    s.session_id = r.session_id
LEFT OUTER JOIN
    sys.dm_exec_connections c
ON
    s.session_id = c.session_id
LEFT OUTER JOIN
(
    SELECT 
        request_session_id,
        database_id = MAX(resource_database_id)
    FROM
        sys.dm_tran_locks
    GROUP BY
        request_session_id
) t
ON
    s.session_id = t.request_session_id
LEFT OUTER JOIN
    sys.dm_os_waiting_tasks wt
ON 
    s.session_id = wt.session_id
LEFT OUTER JOIN
    sys.dm_os_tasks ot
ON 
    s.session_id = ot.session_id
LEFT OUTER JOIN
(
    SELECT
        ot.session_id,
        CPU_Time = MAX(usermode_time)
    FROM
        sys.dm_os_tasks ot
    INNER JOIN
        sys.dm_os_workers ow
    ON
        ot.worker_address = ow.worker_address
    INNER JOIN
        sys.dm_os_threads oth
    ON
        ow.thread_address = oth.thread_address
    GROUP BY
        ot.session_id
) tt
ON
    s.session_id = tt.session_id
WHERE
    COALESCE
    (
        r.command,
        r.wait_type,
        wt.wait_type,
        r.last_wait_type,
        'a'
    ) >= COALESCE
    (
        '', 
        'a'
    )
)
select * from DBConn where DBName like '%'+@dbName+'%'
go

Select
      s.login_name,
      s.host_name,
      c.auth_scheme,
      COUNT(*) cnt
from
sys.dm_exec_connections c
inner join
sys.dm_exec_sessions s
on c.session_id = s.session_id
group by s.login_name,s.host_name,c.auth_scheme
order by 4 desc;
go

-- kill ms sql server session 
KILL 86;
kill 86;
GO

show databases

EXEC sp_databases;
GO
SELECT dbid,[name] FROM master.dbo.sysdatabases order by dbid;
GO
SELECT dbid,[name] FROM master.dbo.sysdatabases order by name;
GO
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused';
GO
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_helpfile';
GO

datafile or masterfile

select physical_name,state,state_desc,size,max_size,max_size-size diff from sys.master_files order by max_size-size;
go

select * from sys.master_files;
go

logfile shrink

select name from sys.databases;
go

select db_name()
go

select name + ' <--> ' + physical_name xxx from sys.master_files where type = 1;
go


DBCC SHRINKFILE(WSS_Content_log, 0, truncateonly)
GO
DBCC SHRINKFILE(WSS_Content_log, 0)
GO

select 'DBCC SHRINKFILE(' + name + ', 0, truncateonly)' xxx from sys.master_files where type = 1;
go

EXEC sp_MSforeachdb 'DECLARE @ln varchar(100) SET @ln=(SELECT name FROM [?].dbo.sysfiles WHERE fileid=2) USE [?] DBCC SHRINKFILE (@ln, 0, TRUNCATEONLY)'

notes:
check last translog backup and whether the database is in full archivelog mode, if not needed, turn it off.
USE [master]
GO
ALTER DATABASE [ignite_confio] SET RECOVERY SIMPLE WITH NO_WAIT
GO

Use myDB
GO

EXEC sp_spaceused;
GO

--see space available in file
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
GO

--see logspace used
DBCC SQLPERF(LOGSPACE)
GO

DBCC LOGINFO
GO

--shrink logfile to 1000 MB,had to shrink twice to significantly reduce log file size
DBCC SHRINKFILE(SharePoint_Config_log, 1000)
GO

DBCC SHRINKFILE(SharePoint_Config_log, 1000)
GO

-- different option
USE [VirtualCenter]
GO
DBCC SHRINKFILE (N'VirtualCenter_log' , 1024)
GO
DBCC SHRINKFILE (N'VirtualCenter_log' , 0, TRUNCATEONLY)
GO
DBCC SHRINKFILE (N'VirtualCenter_log' , EMPTYFILE)
GO

tempfile shrink

use tempdb
GO
EXEC sp_spaceused;
GO
DBCC FREEPROCCACHE -- clean cache
DBCC DROPCLEANBUFFERS -- clean buffers
DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
DBCC FREESESSIONCACHE -- clean session cache
GO
DBCC SHRINKDATABASE(tempdb, 10); -- shrink tempdb
GO
DBCC SHRINKDATABASE('tempdb');
GO
DBCC SHRINKDATABASE('tempdb', TRUNCATEONLY );
GO
dbcc shrinkfile ('tempdev') -- shrink db file
GO
dbcc shrinkfile ('templog') -- shrink log file
GO
-- report the new file sizes
SELECT name, size FROM sys.master_files WHERE database_id = DB_ID(N'tempdb');
GO

current running sql

use master;
go
SELECT  a.session_id,
        datediff(ss, a.start_time, getdate()) as ExecSecs,
        a.command,
        db_name(a.database_id),
        a.blocking_session_id,
        b.text,
        substring(b.text, a.statement_start_offset / 2, case when (a.statement_end_offset - a.statement_start_offset) / 2 > 0 THEN (a.statement_end_offset - a.statement_start_offset) / 2  else 1 END ) as stmt,
        a.wait_type,
        a.wait_time,
        a.last_wait_type,
        a.wait_resource,
        a.reads,
        a.writes,
        a.logical_reads,
        a.cpu_time,
        a.total_elapsed_time,
        a.sql_handle,
        a.statement_start_offset,
        a.statement_end_offset
FROM    sys.dm_exec_requests a
OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE   session_id > 50
        and session_id <> @@spid;
go

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext;
go

SELECT sqltext.TEXT,
req.start_time,
req.status,
req.blocking_session_id,
req.wait_time,
req.wait_type,
req.wait_resource,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext;
go

SELECT sqltext.TEXT,req.* 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext;
go

SELECT sqltext.*,req.* 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext;
go

Current Job Status

use msdb;
go

SELECT Convert(varchar(22),SERVERPROPERTY('ServerName')) AS 'Server Name',
       convert(varchar(35),j.name) as 'Job Name',
j.enabled,
CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS 'Job Status',
jh.run_status,
CASE jh.run_status WHEN 0 THEN 'Error Failed'
				WHEN 1 THEN 'Succeeded'
				WHEN 2 THEN 'Retry'
				WHEN 3 THEN 'Cancelled'
				WHEN 4 THEN 'In Progress' ELSE
				'Status Unknown' END AS 'Last Run Status',
convert(varchar(20),ja.run_requested_date) as 'Last Run Date',
CONVERT(VARCHAR(14),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS 'Run Duration',
convert(varchar(24),ja.next_scheduled_run_date) as 'Next Scheduled Run Date'
--CONVERT(VARCHAR(500),jh.message) AS step_description
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id)  from msdb.dbo.sysjobactivity) ORDER BY 'job name','job status';
go

mssql: change server name

rem start dos cmd prompt as administrator
sqlcmd -E -d master -Q "exec sp_helpserver;select @@servername;"
sqlcmd -E -d master -Q "begin declare @my_servername nvarchar(256);select @my_servername=@@servername;print 'exec sp_dropserver '''+ @my_servername + '''';declare @my_hostname nvarchar(256);select @my_hostname=host_name();print 'exec sp_addserver ''' + @my_hostname + ''', local';print 'go';END;"
sqlcmd -E -d master -Q "begin declare @my_servername nvarchar(256); select @my_servername=@@servername;exec sp_dropserver @my_servername;declare @my_hostname nvarchar(256);select @my_hostname=host_name();exec sp_addserver @my_hostname, local;END;"

sqlcmd -E -d master -Q ^
"begin declare @my_username nvarchar(256); ^
set @my_username='ESCAPE\Mssqlgrpapass'; ^
print 'CREATE LOGIN [' + @my_username + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master];'^
print 'GO'^
print 'ALTER SERVER ROLE [sysadmin] ADD MEMBER [' + @my_username + ']'^
print 'GO'^
END;"

sqlcmd -E -d master -v my_username='ESCAPE\Mssqlgrpapass' -Q "begin print 'CREATE LOGIN [' + $(my_username) + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master]' + char(13) + char(10) + 'GO' + char(13) + char(10) + 'ALTER SERVER ROLE [sysadmin] ADD MEMBER [' + $(my_username) + ']' + char(13) + char(10) + 'GO' + char(13) + char(10); END;"

net stop SQLSERVERAGENT
net stop MSSQLFDLauncher
net stop MSSQLSERVER

net start MSSQLFDLauncher
net start MSSQLSERVER
net start SQLSERVERAGENT
sqlcmd -E -d master -Q "exec sp_helpserver;select @@servername;"

sqlcmd -L  ---> List servername and instance name

sqlcmd -E -d master -Q "exec sp_helpserver;select @@servername;"

sqlcmd -E -e
USE master
GO
select @@Version;
go

SELECT
SERVERPROPERTY('MachineName') AS [ServerName],
SERVERPROPERTY('ServerName') AS [ServerInstanceName],
SERVERPROPERTY('InstanceName') AS [Instance],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductVersion') AS [ProductVersion],
Left(@@Version, Charindex('-', @@version) - 2) As VersionName;
go
SELECT SERVERPROPERTY('InstanceName') AS [Instance]
go
sp_helpserver
go
select @@servername
go

begin
declare @my_servername nvarchar(256);
select @my_servername=@@servername;
print 'exec sp_dropserver '''+ @my_servername + '''';
print 'GO'
declare @my_hostname nvarchar(256);
select @my_hostname=host_name();
print 'exec sp_addserver ''' + @my_hostname + ''', local';
print 'go'
END;
GO

-- sp_dropserver 'XXX_MSSQL\YYY'
-- GO
-- sp_addserver 'HOSTNAME\YYY', local
-- GO

-- sp_dropserver 'GI_MXP_W2K3_SQL\MXPPROD'
-- GO
-- sp_addserver 'ShipnameMXPDB01V\MXPPROD', local
-- GO

use master
go

begin
declare @my_servername nvarchar(256);
select @my_servername=@@servername;
print 'exec sp_dropserver '''+ @my_servername + '''';
print 'go'
declare @my_hostname nvarchar(256);
select @my_hostname=host_name();
print 'exec sp_addserver ''' + @my_hostname + ''', local';
print 'go'
END;
go

begin
declare @my_servername nvarchar(256);
select @my_servername=@@servername;
exec sp_dropserver @my_servername;
declare @my_hostname nvarchar(256);
select @my_hostname=host_name();
exec sp_addserver @my_hostname, local;
END;
GO

net stop MSSQLFDLauncher
net stop SQLSERVERAGENT
net stop MSSQLSERVER

net start MSSQLFDLauncher
net start MSSQLSERVER
net start SQLSERVERAGENT

sp_configure

sp_configure

Configure SQL Agent

use master
go

sp_configure
go

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure
go

sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

check agent service login privileges: windows groups and sql privilege
Check the SQLAGENT.out file in the SQL Server \LOG folder for more details. 
D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT
D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG

db backup

-- check if current backup is running, sp_who2 @Command like '%BACKUP%';
use master
go
SELECT          physical_device_name,
                backup_start_date,
                backup_finish_date,
                backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
ORDER BY backup_finish_date ASC
go
SELECT          physical_device_name,
                backup_start_date,
                backup_finish_date,
                backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
where physical_device_name not like '%TRANSLOG%' and 
      physical_device_name like '%Gen%' 
ORDER BY backup_finish_date ASC
go

SELECT database_name,
backup_start_date,
backupType = CASE type
WHEN 'D' THEN 'Database (full)'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'Log'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
ELSE 'Unknown'
END
FROM msdb.dbo.backupset
ORDER BY database_name, backup_start_date;

-- unc path or network path, increase timeout setup
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanworkstation\parameters\SessTimeout  600 decimal 

open regedit
navigate to: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanworkstation\parameters
Create a New DWORD value with the name: SessTimeout
set the value: 360 Decimal, 168 Hexadecimal

-- last backup location
select top 200 media_set_id,physical_device_name from msdb.dbo.backupmediafamily order by media_set_id desc ;

select top 200 media_set_id,physical_device_name from msdb.dbo.backupmediafamily where upper(physical_device_name) not like '%LOG%' order by media_set_id desc ;

delete backup file

-- delete old backup file using service account permission
EXECUTE master.dbo.xp_delete_file 0,N'\\my_domainname\backups\my_hostname\my_folder',N'bak',N'2014-10-28T17:40:02'

-- master.dbo.xp_delete_file 0 = FileBackup, 1 = FileReport, backup files, transaction logs
-- delete all files in a folder
EXECUTE master.dbo.xp_delete_file 0,N'\\DBAUTLPRD01\SQLData\Backup\PCHDORASQ01\amos\FULL\',N'bak'

-- delete file older than a specific time
EXECUTE master.dbo.xp_delete_file 0,N'\\DBAUTLPRD01\SQLData\Backup\PCHDORASQ01\amos\FULL\',N'bak',N'2014-10-28T17:40:02'

DECLARE @DeleteDate datetime
SET @DeleteDate = DateAdd(day, -7, GetDate())

EXECUTE master.sys.xp_delete_file
0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)
N'D:\SQLbackups\', -- folder path (trailing slash)
N'bak', -- file extension which needs to be deleted (no dot)
@DeleteDate, -- date prior which to delete
1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)
GO

xp_cmdshell 'del <filename>'
exec sp_configure
go
exec sp_configure 'xp_cmdshell', 1
-- Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
go
reconfigure
go

xp_readerrorlog 0,1,null,null,null,null,'DESC'

USE master
GO
EXEC xp_readerrorlog 3

EXEC xp_ReadErrorLog 1
-- Reads SQL Server error log from ERRORLOG.1 file

EXEC xp_ReadErrorLog 0, 1

-- Reads current SQL Server error log

EXEC xp_ReadErrorLog 0, 2
-- Reads current SQL Server Agent error log

EXEC xp_ReadErrorLog 0, 1, 'Failed'
-- Reads current SQL Server error log with text 'Failed'

EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login'
-- Reads current SQL Server error log with text '..Failed'.. AND 'Login'

EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login', '20121101', NULL
-- Reads current SQL Server error log with text '..Failed'.. AND '..Login'.. from 01-Nov-2012

EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login', '20121101', '20121130'
-- Reads current SQL Server error log with text '..Failed... AND ...Login... between 01-Nov-2012 and 30-Nov-2012

EXEC xp_ReadErrorLog 0, 1, NULL, NULL, '20121101', '20121130'
-- Reads current SQL Server error between 01-Nov-2012 and 30-Nov-2012

EXEC xp_ReadErrorLog 0, 1, NULL, NULL, '20121101', '20121130', 'DESC'
-- Reads current SQL Server error log between 01-Nov-2012 and 30-Nov-2012 and sorts in descending order

xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc' 
GO

-- EXEC sp_readerrorlog 6
-- EXEC sp_readerrorlog 6, 1, '2005'
EXEC sp_readerrorlog 6, 1, '2005', 'exec'

xp_readerrrorlog
EXEC master.dbo.xp_readerrorlog 6, 1, '2005', 'exec', NULL, NULL, N'desc'
EXEC master.dbo.xp_readerrorlog 6, 1, '2005', 'exec', NULL, NULL, N'asc'

CREATE PROC [sys].[sp_readerrorlog](
   @p1     INT = 0,
   @p2     INT = NULL,
   @p3     VARCHAR(255) = NULL,
   @p4     VARCHAR(255) = NULL)
AS
BEGIN

   IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1)
   BEGIN
      RAISERROR(15003,-1,-1, N'securityadmin')
      RETURN (1)
   END
   
   IF (@p2 IS NULL)
       EXEC sys.xp_readerrorlog @p1
   ELSE
       EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4
END

powershell: 
powershell get-eventlog 
Get-EventLog -LogName Application 
Get-EventLog  -ComputerName Obiwan  -LogName Application       -After ((Get-Date).adddays(-1)) 
Get-EventLog  -ComputerName Obiwan  -LogName System     -Before ((Get-Date).addHours(-2)) 
Get-EventLog  -ComputerName Obiwan  -LogName Security  -After ((Get-Date).adddays(-1)    -Before ((Get-Date).addHours(-3)) 
Get-EventLog  -ComputerName Obiwan  -LogName Application -EntryType Error
Get-EventLog  -ComputerName Obiwan  -EntryTpe Error  -LogName Application -source '*sql*'
Get-EventLog  -ComputerName Obiwan  -LogName Application -Message '*started* '
Get-EventLog  -ComputerName Obiwan  -LogName Application -EntryTpe Error -Source '*sql*' -After ((Get-Date).adddays(-1)) 

Get-Content c:\temp\Servers.txt | ForEach-Object { #A
       Get-Eventlog -ComputerName $_ -LogName Application  -EntryType Error -After (Get-Date).adddays(-1) |
       Sort-Object Time -descending |
       Export-Xls c:\temp\ContosoMonitorError.xlsx -AppendWorksheet -WorksheetName $_ #B
}


Get-WmiObject  -Class "SqlErrorLogEvent"  -ComputerName R2D2 -Namespace "Root\Microsoft\SqlServer\ComputerManagement11"|
Select-object FileName,
       InstanceName,
       @{Expression={$_.ConvertToDateTime($_.LogDate)};Label = 'Logdate'},
       Message,
       Processinfo

event log or event viewer
%programfiles%\Microsoft SQL Server\130\Setup Bootstrap\Log\
On the Start menu, point to All Programs, point to Administrative Tools, and then click Event Viewer.

In Event Viewer, click Application.

SQL Server events are identified by the entry MSSQLSERVER (named instances are identified with MSSQL$) in the Source column. SQL Server Agent events are identified by the entry SQLSERVERAGENT (for named instances of SQL Server, SQL Server Agent events are identified with SQLAgent$). Microsoft Search service events are identified by the entry Microsoft Search.

To view the log of a different computer, right-click Event Viewer, click Connect to another computer, and complete the Select Computerdialog box.

Optionally, to display only SQL Server events, on the View menu click Filter, and in the Event source list, select MSSQLSERVER. To view only SQL Server Agent events, instead select SQLSERVERAGENT in the Event source list.

To view more information about an event, double-click the event.

Sqlservr.exe -m
Sqlservr.exe -T3608
SQLServr.exe -T3609
SQLServr.exe -T3608 -T3609

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=Virtuals\Administators
service startup parameter
https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/
-d master.mdf datafile location
-e ERRORLOG errorlog location
-l masterlog.ldf master logfile lcoation

-- how to move temp db file location, restart instance

-- how to move msdb file location

-- how to move model file location

USE [master]
GO
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

EXEC master..sp_addsrvrolemember @loginame = N'NT AUTHORITY\SYSTEM', @rolename = N'sysadmin'
GO

point in time restore and recovery with transaction log

Log backups must be applied in the sequence in which they were created, without any gaps in the log chain. 

Except for the last log backup, you must use WITH NORECOVERY, as follows: 
Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)
Leaves the database in an unrecovered state. This option is equivalent to using the NORECOVERY option in a Transact-SQL RESTORE statement.
When you choose this option, the Preserve replication settings option is unavailable. 








top   prev   next   




-- point in time restroe and recovery with transaction log
ALTER DATABASE AdventureWorks2008R2 SET RECOVERY FULL

# ALTER DATABASE AdventureWorks2008R2 SET RECOVERY SIMPLE

RESTORE DATABASE AdventureWorks2008R2
   FROM AdventureWorks2008R2_1
   WITH NORECOVERY
GO
RESTORE LOG AdventureWorks2008R2
   FROM AdventureWorks2008R2_log
   WITH FILE = 1,
   WITH NORECOVERY
GO
RESTORE DATABASE AdventureWorks2008R2
   WITH RECOVERY
GO

not  last log:
RESTORE LOG  FROM  WITH NORECOVERY;

last log:
a):
RESTORE LOG  FROM  WITH RECOVERY;
GO

b):
RESTORE LOG  FROM  WITH NORECOVERY; 
RESTORE DATABASE  WITH RECOVERY;
GO

Simple Recovery Mode Exclusion For Transaction Log Backup

REM recovery_mode SIMPLE FULL BULK
SELECT database_id,name, recovery_model_desc  FROM sys.databases;
SELECT database_id,name, state_desc  FROM sys.databases;
SELECT DATABASEPROPERTYEX('name_of_the_database', 'Recovery');
SELECT recovery_model_desc FROM sys.databases WHERE name = 'name_of_the_database';

REM recovery_mode SIMPLE FULL BULK
SELECT database_id,name, recovery_model_desc  FROM sys.databases;
SELECT database_id,name, state_desc  FROM sys.databases;

SELECT DATABASEPROPERTYEX('name_of_the_database', 'Recovery');

SELECT recovery_model_desc FROM sys.databases WHERE name = 'name_of_the_database';

USE master;
GO
ALTER DATABASE mydb SET RECOVERY FULL;
ALTER DATABASE mydb SET RECOVERY SIMPLE;
ALTER DATABASE mydb SET RECOVERY BULK_LOGGED;
GO

SELECT a.name 
   FROM sys.databases a, master.dbo.sysdatabases b 
where a.name=b.name and a.recovery_model_desc not in ( 'SIMPLE' )
and b.name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB');
GO


SELECT name, recovery_model_desc
   FROM sys.databases
      WHERE name = 'model' ;
GO

SELECT a.name 
   FROM sys.databases a, master.dbo.sysdatabases b 
where a.name=b.name and a.recovery_model_desc not in ( 'SIMPLE' )
and b.name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB');
GO

RESTORE DATABASE [ignite_confio] 
FILE = N'ignite_confio_data' 
FROM  DISK = N'\\epic\dbbackups\DR_SAN\EPICONFIO01V\Daily\ignite_confio_backup_2015_07_02_161901_5214659.bak' WITH  FILE = 1,  
MOVE N'ignite_confio_data' TO N'F:\MSSQL\DATA\\ignite_depository.mdf',  
MOVE N'ignite_confio_log' TO N'L:\MSSQL\DATA\ignite_depository_0.ldf',  
NOUNLOAD,  REPLACE,  STATS = 5
GO

DB Backup Restore Status

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE', 'BACKUP LOG', 'RESTORE HEADERONLY');

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, 
dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a;
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE', 'BACKUP LOG', 'RESTORE HEADERONLY') 

SELECT * FROM sys.dm_exec_requests WHERE session_id = 51;
SELECT command, percent_complete, start_time FROM sys.dm_exec_requests WHERE session_id = 51;

drop db user

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'EMGONZALEZ')
DROP LOGIN [EMGONZALEZ]
GO

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'mywindowsdomain\mburke')
DROP LOGIN [mywindowsdomain\mburke]
GO

USE [MarineXProcurement]
GO
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'EMGONZALEZ')
DROP USER [EMGONZALEZ]

USE [MarineXProcurement]
GO
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'mywindowsdomain\mburke')
DROP USER [mywindowsdomain\mburke]
GO

USE [MarineXProcurement]
GO
IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'EMGONZALEZ')
DROP SCHEMA [EMGONZALEZ]

USE [master]
GO
CREATE LOGIN [JEWEL\MSSQLGRPFWI] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = N'JEWEL\MSSQLGRPFWI', @rolename = N'sysadmin'
GO
EXEC master..sp_dropsrvrolemember @loginame = N'PHOTOGAL', @rolename = N'sysadmin'
GO

-- for sql server 2008
USE master
GO
EXEC master..sp_addsrvrolemember @loginame = N'myusername', @rolename = N'bulkadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'myusername', @rolename = N'diskadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'myusername', @rolename = N'processadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'myusername', @rolename = N'securityadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'myusername', @rolename = N'serveradmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'myusername', @rolename = N'setupadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'myusername', @rolename = N'sysadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'myusername', @rolename = N'dbcreator'
GO

-- for sql server 2012

USE [master]
GO
CREATE LOGIN [myusername] WITH PASSWORD=N'mypassword'
GO
ALTER SERVER ROLE [bulkadmin] ADD MEMBER [myusername]
GO
ALTER SERVER ROLE [diskadmin] ADD MEMBER [myusername]
GO
ALTER SERVER ROLE [processadmin] ADD MEMBER [myusername]
GO
ALTER SERVER ROLE [securityadmin] ADD MEMBER [myusername]
GO
ALTER SERVER ROLE [serveradmin] ADD MEMBER [myusername]
GO
ALTER SERVER ROLE [setupadmin] ADD MEMBER [myusername]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [myusername]
GO
ALTER SERVER ROLE [dbcreator] ADD MEMBER [myusername]
GO

ALTER LOGIN [sa] WITH PASSWORD=N'kjsdhfsdjf'
GO

USE [MarineXProcurement]
GO
CREATE USER [mywindowsdomain\mburke] FOR LOGIN [mywindowsdomain\myusername] WITH DEFAULT_SCHEMA=[dbo]
GO

-- msdb
USE [msdb]
GO
CREATE USER [JEWEL\NCL_SM_ASM] FOR LOGIN [mywindowsdomain\MY_SM_ASM]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'mywindowsdomain\MY_SM_ASM'
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentReaderRole', N'mywindowsdomain\MY_SM_ASM'
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentUserRole', N'mywindowsdomain\MY_SM_ASM'
GO

-- check policy
CREATE LOGIN [PHOTOGAL] WITH PASSWORD=N'n', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [master]
GO
ALTER LOGIN [PHOTOGAL] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

-- enable disable user
ALTER LOGIN [PHOTOGAL] ENABLE
GO
ALTER LOGIN [PHOTOGAL] DISABLE
GO

Report And Fix Orphan users

USE my_db
GO
EXEC sp_change_users_login 'Report' --> db username, login name, password must not presended
EXEC sp_change_users_login 'Update_One', 'my_db_username', 'my_login_name'  --> password must not presented

EXEC sp_change_users_login 'Auto_Fix', 'my_db_username'
EXEC sp_change_users_login 'Auto_Fix', 'my_db_username', NULL, 'my_password'

Create User and Extract Permission

SELECT LOGINPROPERTY('signage','PASSWORDHASH');

CREATE LOGIN [signage] WITH WITH PASSWORD = 0x0100AEDC88B73D8434AFE45CC3583C08AE4E95983C205CD2EDF0 HASHED, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

--> Query -> Current Connection Options -> Advanced (Tab) --> Maximum characters per column

-- Create User At Database Level

DECLARE @DatabaseUserName [sysname]
-- SET @DatabaseUserName = 'my_username_1'
SET @DatabaseUserName = 'my_username_2'

SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@ObjectID [int],
@ObjectName [varchar](261)

SELECT
@DatabaseUserID = [sysusers].[uid],
@ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
WHERE [sysusers].[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +
'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = '-Security creation script for user ' + @ServerUserName + CHAR(13) +
'-Created At: ' + CONVERT(varchar, GETDATE(), 112) + 
          REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
'-Created By: ' + SUSER_NAME() + CHAR(13) +
'-Add User To Database' + CHAR(13) +
'USE [' + DB_NAME() + ']' + CHAR(13) +
'EXEC [sp_grantdbaccess]' + ' ' + '@loginame = "' + @ServerUserName + '",' + ' ' + 
'@name_in_db = "' + @DatabaseUserName + '"' + CHAR(13) +
'GO' + CHAR(13) +
'CREATE USER ' + @DatabaseUserName + ' FOR LOGIN ' + @ServerUserName + CHAR(13) +
'GO' + CHAR(13) +
'-Add User To Roles'
PRINT @msgStatement
DECLARE _sysusers
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
[name]
FROM [dbo].[sysusers]
WHERE
[uid] IN
(
SELECT
[groupuid]
FROM [dbo].[sysmembers]
WHERE [memberuid] = @DatabaseUserID
)
OPEN _sysusers
FETCH
NEXT
FROM _sysusers
INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = 'EXEC [sp_addrolemember]' + ' ' + '@rolename = "' + @RoleName + '",' + ' ' + '@membername = "' + @DatabaseUserName + '"'
PRINT @msgStatement
FETCH
NEXT
FROM _sysusers
INTO @RoleName
END
SET @msgStatement = 'GO' + CHAR(13) +
'-Set Object Specific Permissions'
PRINT @msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
DISTINCT([sysobjects].[id]),
'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects]
ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'GRANT ' + @msgStatement + ' ON ' + @ObjectName + ' TO ' + @DatabaseUserName
PRINT @msgStatement
END
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'DENY' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
PRINT 'GO'
END

Procedure to drop users after DB refresh
========================================

use MarineXProcurement
GO
DECLARE RemoveMXPDBUsers CURSOR STATIC READ_ONLY FOR
select name from MarineXProcurement..sysusers
WHERE HASDBACCESS = 1
AND NAME NOT IN ('dbo','sys','guest','INFORMATION_SCHEMA','interface','SabreInterface' )

DECLARE
        @NAME                   sysname,
        @SCHEMACommand          nvarchar(400),
        @USERCommand            nvarchar(400)
OPEN RemoveMXPDBUsers
FETCH NEXT FROM RemoveMXPDBUsers INTO @NAME
WHILE (@@FETCH_STATUS = 0) BEGIN
        SET @SCHEMACommand = 'DROP SCHEMA ['+ @NAME +']'
        SET @USERCommand   = 'DROP USER ['+ @NAME +']'
        EXEC sp_executesql  @SCHEMACommand
        EXEC sp_executesql  @USERCommand
        FETCH NEXT FROM RemoveMXPDBUsers INTO @NAME
END
DEALLOCATE RemoveMXPDBUsers
GO

Autocommit

SET IMPLICIT_TRANSACTIONS OFF --> autocommit on 

SET IMPLICIT_TRANSACTIONS ON --> autocommit off

eg:
SET IMPLICIT_TRANSACTIONS ON
UPDATE MyTable SET MyField = 1 WHERE MyId = 1
COMMIT TRANSACTION

-- shrink db space
EXEC sp_databases;

SELECT name FROM master..sysdatabases;
SELECT [name] FROM master.dbo.sysdatabases WHERE dbid > 4 ;
SELECT [name] FROM master.dbo.sysdatabases WHERE dbid > 6;
SELECT * FROM sys.databases d WHERE d.database_id > 4;

execute sp_spaceused

USE AdventureWorks2012;
GO
SELECT file_id, name FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);

DBCC SHRINKDATABASE('mydb')
DBCC SHRINKDATABASE('MyDB') 
DBCC SHRINKDATABASE (N'SMS2_Data' , 0)
DBCC SHRINKDATABASE ( 'FWiServices', TRUNCATEONLY )

DBCC SHRINKFILE('MyDB_dat' , 2)
DBCC SHRINKFILE (N'SMS2_Data' , 0, TRUNCATEONLY)
DBCC SHRINKFILE (N'SMS2_Data' , 19500)

ALTER DATABASE SMS2HazSub MODIFY FILE (NAME = 'SMS2_Data', SIZE = 20000) 

ALTER DATABASE mydb MODIFY FILE ( NAME = N'MyDB_dat', SIZE = 100MB )
GO

DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2012
REMOVE FILE Test1data;
GO

-- error log location
D:\Program Files\Microsoft SQL Server\MSSQL10_50.MXPPROD\MSSQL\Log

rebuild master database

1): use the same version of binary and master db version
2): start server.exe in maintainance mode ( -c -m )
3): restore the master database

rebuild msdb

1):
You can use normal restore operation to restore the msdb.

2):
net start MSSQL$MXPPROD /T3608

sqlservr.exe -c -T3608 -sMXPPROD

D:

cd D:\Program Files\Microsoft SQL Server\MSSQL10_50.MXPPROD\MSSQL\Install

sqlcmd -E -S PEAMXPDB02V\MXPPROD -dmaster -i"instmsdb.sql" -o"instmsdb.sql.log"

rebuild model database

1): Because model database is a read only database, you can just copy the data files

2):
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install

start "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MXPPROD\MSSQL\Binn\sqlservr.exe" -sPEAMXPDB02v\MSSQL$MXPPROD -c T3608

set PATH="D:\Program Files\Microsoft SQL Server\MSSQL10_50.MXPPROD\MSSQL\Binn";%PATH%

start .\sqlserver.exe -sMXPPROD -c T3608

start "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MXPPROD\MSSQL\Binn"\sqlserver.exe -sMXPPROD -c T3608

start "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MXPPROD\MSSQL\Binn"\sqlserver.exe -s -c T3608

SQLCMD -E -SMXPPROD -dmaster -Q"EXEC sp_detach_db msdb"
SQLCMD -E -SMXPPROD -dmaster -Q"EXEC sp_detach_db model"

SQLCMD -SInstName -E
1> Restore database MODEL FROM DISK = 'C:\Databases\Backup\model.bak' WITH REPLACE
2> go
1> Restore database MODEL WITH RECOVERY
2> go