Sql Server Frequently Used Commands

I am in the process of putting my notes into web pages, please keep checking in later for more contents

quick link

home   mssql   sqlcmd syntax   show databases   list datafiles master_file   logfile shrink   free memory   tempfile shrink  
change_servername   sp_configure   Configure SQL Agent   create user and extract permission   drop db user   orphan_user   current running sql   dbbackup   backup/restore status   delete backup file   current job status  
current_time   set nocount   current session   kill session  
log shipping 
connectsql    
top   prev   next  

Get Back sysadm access when lost password

"D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER -m

CREATE LOGIN [mydomainname\myusername] FROM WINDOWS; 
go

EXEC sp_addsrvrolemember [mydomainname\myusername],[sysadmin];
go

EXEC sp_addsrvrolemember [mydomainname\myusername],[sysadmin]; 
go

top   prev   next  

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

top   prev   next  

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


top   prev   next  

Random Password

openssl rand -base64 6

openssl rand -base64 8

openssl rand -base64 12


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


top   prev   next  
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 nclmiami.ncl.com

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.


top   prev   next  
net use

IF NOT EXIST M: net use M: \\nclmiami\dfs\dba
IF NOT EXIST P: net use M: \\nclmiami\dfs\dba\promotions
IF NOT EXIST U: net use M: \\nclmiami.ncl.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

top   prev   next  

sc windows service

mode con: cols=90 lines=25
wmic service get name,state,startname,startMode,status|findstr /i sql

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

top   prev   next  

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

sqlcmd -r1 2> install-err.log 1> install.log
sqlcmd -r0 2>
sqlcmd [-b On error batch abort]

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
top
top   prev   next  

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
top
top   prev   next  

set nocount on|off

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


top   prev   next  
SET QUOTED_IDENTIFIER ON
GO


top   prev   next  
SET ANSI_NULLS ON
GO
top
top   prev   next  

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
top
top   prev   next  

Current session or Current Active Session

sp_who
use msdb;
go

sp_who
sp_who2
sp_who3


-- sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]
EXEC sp_who 'janetl'
sp_who 'amos'
EXEC sp_who 'active'
EXEC sp_who '10' --specifies the process_id
sp_who sql server 2000

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


top   prev   next  
-- 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


top   prev   next  
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


top   prev   next  
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

top   prev   next  
top
-- kill ms sql server session 
KILL 86;
kill 86;
GO
top
top   prev   next  

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
top
top   prev   next  

datafile or masterfile

master file
select physical_name from sys.master_files order by 1;
go

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
top
top   prev   next  

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)'

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

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



top   prev   next  
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
top
top   prev   next  

Free Memory

DBCC FREESYSTEMCACHE ('ALL') 
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE 
DBCC DROPCLEANBUFFERS
DBCC MEMORYSTATUS 

EXEC sp_configure 'max server memory';
-- EXEC sp_configure 'max server memory', 1024;
-- RECONFIGURE;

DBCC FREEPROCCACHE -- clean cache
DBCC DROPCLEANBUFFERS -- clean buffers
DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
DBCC FREESESSIONCACHE -- clean session cache
GO
top
top   prev   next  

tempfile shrink

use tempdb
GO
EXEC sp_spaceused;
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
top
top   prev   next  

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
top
top   prev   next  

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
top
top   prev   next  

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;"


top   prev   next  
-- Change report Server Name
rsconfig -c -s HDQPSIRSDB01V -d reportserver -a Windows -u NCLMIAMI\MSSQLGRPSIR -p xxx

RSKeyMgmt /?

RSKeyMgmt -e -f FileName -p StrongPassword

RSKeyMgmt -e -f HDQPSIRSDB01V.rskeymgmt -p HDQPSIRSDB01V.rskeymgmt

RSKeyMgmt -r InstallationID 
RSKeyMgmt -r 29a914e2-ff6f-45f9-b89d-1f1e528c30bd


top   prev   next  

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
top
top   prev   next  

sp_configure

sp_configure
sp_configure 'show advanced option'
sp_configure 'show advanced option',1
reconfigure
sp_configure
top
top   prev   next  

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
top
top   prev   next  

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


top   prev   next  
-- 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


top   prev   next  
-- 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 ;

top   prev   next  

backup job owner : sa, not windows authentication

select s.name,l.name from  msdb..sysjobs s left join master.sys.syslogins l on s.owner_sid = l.sid;

select s.name,l.name from  msdb..sysjobs s left join master.sys.syslogins l on s.owner_sid = l.sid where l.name is null;
top
top   prev   next  

delete backup file

DECLARE @ThreeDaysAgo VARCHAR(50)
SELECT @ThreeDaysAgo = CAST(DATEADD(d, -3, GETDATE()) AS VARCHAR)
EXECUTE dbo.xp_delete_file 0,N'\\spirit\dbbackups\DR_SAN\SPISHPTINTRA01V\DAILY',N'bak',@ThreeDaysAgo
EXECUTE dbo.xp_delete_file 0,N'\\spirit\dbbackups\PROD_SAN\SPISHPTINTRA01V\WEEKLY',N'bak',@ThreeDaysAgo
EXECUTE dbo.xp_delete_file 0,N'\\spirit\dbbackups\DR_SAN\SPISHPTINTRA01V\TRANSLOG',N'trn',@ThreeDaysAgo


top   prev   next  
-- 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


top   prev   next  
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

error log location

-- error log location:
xp_readerrorlog 0,1,null,null,null,null,'DESC'
go
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc' 
GO

C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG

DBCC ERRORLOG command or sp_cycle_errorlog to cycle the error log without recycling the instance of SQL Server

sql server management studio --> SQl Server Logs --> Configure --> Maximum number of error log files

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


top   prev   next  
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.


top   prev   next  
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
cf:sql server instance 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


top   prev   next  

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

top   prev   next  

point in time restroe and recovery with transaction log

How to: Apply a Transaction Log Backup (Transact-SQL)   
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


top   prev   next  
select * from sys.databases d, sys.master_files f 
where d.database_id=f.database_id and d.name=db_name();
GO
select @@servername servername, d.name db_name, 
f.name file_name, f.physical_name physical_file_name
from sys.databases d, sys.master_files f 
where d.database_id=f.database_id and d.name=db_name();
GO

declare @my_db_name as sysname
-- set @my_db_name=N'AdventureWorks2008R2'
set @my_db_name=db_name();
select '-- RESTORE DATABASE ... ...' + char(13) 
union all 
select 'FILE = N''' + f.name + ''',' 
from sys.master_files f, sys.databases d
where  d.name=@my_db_name and d.database_id=f.database_id
union all
select '-- FROM ' + char(13)
union all
select '-- DISK ... ...'
GO
declare @my_db_name as sysname
-- set @my_db_name=N'AdventureWorks2008R2'
set @my_db_name=db_name();
select '-- WITH' + char(13) 
union all 
select 'MOVE N''' + f.name + ''' TO N''' + f.physical_name + ''',' 
from sys.master_files f, sys.databases d
where  d.name=@my_db_name and d.database_id=f.database_id
;
GO
RESTORE DATABASE [AdventureWorks2008R2] 
FILE = N'AdventureWorks2008R2_dat',  
FILE = N'AdventureWorks2008R2_images',  
FILE = N'AdventureWorks2008R2_messages',  
FILE = N'MarineXFiles' FROM  
DISK = N'x:\SSCNAVSQL01_AdventureWorks2008R2_FULL_20160411_143016_01.bak',
DISK = N'x:\SSCNAVSQL01_AdventureWorks2008R2_FULL_20160411_143016_02.bak' 
WITH  
FILE = 1,  
MOVE N'AdventureWorks2008R2_dat' TO N'F:\MSSQL\DATA\\AdventureWorks2008R2.mdf',  
MOVE N'AdventureWorks2008R2_images' TO N'F:\MSSQL\DATA\\AdventureWorks2008R2_Images.NDF',  
MOVE N'AdventureWorks2008R2_messages' TO N'F:\MSSQL\DATA\\AdventureWorks2008R2_Messages.NDF',  
MOVE N'MarineXFiles' TO N'F:\MSSQL\DATA\\AdventureWorks2008R2_XFiles.NDF',  
MOVE N'AdventureWorks2008R2_log' TO N'L:\MSSQL\Log\\AdventureWorks2008R2.LDF',  
NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO


RESTORE LOG [AdventureWorks2008R2]
   FROM DISK = N'x:\SSCNAVSQL01_AdventureWorks2008R2_LOG_20160411_143752.trn'
   WITH NORECOVERY;
GO
-- Step 4: Restore the tail-log backup.
RESTORE LOG [AdventureWorks2008R2]
   FROM  DISK =  N'x:\SSCNAVSQL01_AdventureWorks2008R2_LOG_20160411_164136.trn'
   WITH NORECOVERY;
GO

RESTORE DATABASE [AdventureWorks2008R2]
  WITH RECOVERY
GO


top   prev   next  
-- tail log backup required
BACKUP LOG [MYDB] TO DISK = 'C:\TEMP\MYDB_01.bck' WITH INIT, NO_TRUNCATE;
GO

-- sp_who or sp_who2, looking for MYDB connections

RESTORE DATABASE [MYDB] FROM
DISK = 'D:\DBBACKUPS\MYDB\FULL\MYHOST_MYDB_FULL_20170604_230004_1.bak',
DISK = 'D:\DBBACKUPS\MYDB\FULL\MYHOST_MYDB_FULL_20170604_230004_2.bak',
DISK = 'D:\DBBACKUPS\MYDB\FULL\MYHOST_MYDB_FULL_20170604_230004_3.bak',
DISK = 'D:\DBBACKUPS\MYDB\FULL\MYHOST_MYDB_FULL_20170604_230004_4.bak'
WITH NORECOVERY
GO

RESTORE LOG [MYDB]
   FROM DISK = N'D:\DBBACKUPS\MYDB\LOG\MYHOST_MYDB_LOG_20170604_230001.trn'
WITH NORECOVERY;
GO

-- second last log
RESTORE LOG [MYDB]
   FROM DISK = N'D:\DBBACKUPS\MYDB\LOG\MYHOST_MYDB_LOG_20170605_073000.trn'
WITH NORECOVERY,
 STOPAT = '2017-06-05 07:35:00.000'
GO

-- last log
RESTORE LOG [MYDB]
   FROM DISK = N'D:\DBBACKUPS\MYDB\LOG\MYHOST_MYDB_LOG_20170605_074501.trn'
WITH NORECOVERY,
   STOPAT = '2017-06-05 07:35:00.000'
GO

RESTORE LOG MYDB WITH RECOVERY
GO

top   prev   next  

Simple Recovery Mode Exclusion For Transaction Log Backup

Recovery Model: Simple, Full, Bulk-logged       
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


top   prev   next  
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
top
top   prev   next  

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;
top
top   prev   next  

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'NCLMIAMI\mburke')
DROP LOGIN [NCLMIAMI\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'NCLMIAMI\mburke')
DROP USER [NCLMIAMI\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 [NCLMIAMI\mburke] FOR LOGIN [NCLMIAMI\mburke] WITH DEFAULT_SCHEMA=[dbo]
GO

-- msdb
USE [msdb]
GO
CREATE USER [JEWEL\NCL_SM_ASM] FOR LOGIN [JEWEL\NCL_SM_ASM]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'JEWEL\NCL_SM_ASM'
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentReaderRole', N'JEWEL\NCL_SM_ASM'
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentUserRole', N'JEWEL\NCL_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


top   prev   next  
USE MYDB
GO
EXEC sp_changedbowner 'sa'
GO
ALTER DATABASE ITS SET TRUSTWORTHY ON
GO
top
top   prev   next  

Report And Fix Orphan users

-- change sid in user database
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_change_users_login ''Report''';
GO

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'


top   prev   next  
-- get password hash
SELECT LOGINPROPERTY('MyAppUser','PASSWORDHASH');

-- get sid
USE AppDB 
GO 
SELECT name, sid FROM sys.sysusers WHERE name = 'MyAppUser' 
GO 
USE MASTER 
GO 
SELECT name, sid FROM sys.sql_logins WHERE name = 'MyAppUser' 
GO

select * from sys.server_principals where name like 'MyAppUser%'



top   prev   next  
-- create server login
CREATE LOGIN [MyAppUser] WITH 
PASSWORD = 0x0100FEFCB7E9DF1FD04B3DB6E048A92F07DE060C4B75C97DC267 HASHED, 
SID = 0x8AE08F0D255CE34C9164AD21DF960A16, 
DEFAULT_DATABASE = [master], 
CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;
top
top   prev   next  

user password last changed: eg sa user's last change date

sqlcmd -l 1200 -t 1200 -h-1 -W -d master -S mydbservername
set nocount on 
SELECT ltrim(rtrim(@@servername)) + space(30-len(@@servername)) servername,
substring (convert (varchar (20),modify_date,120),1,20) time
FROM sys.sql_logins 
WHERE [name] = 'sa';
top
top   prev   next  

Create User and Extract Permission

  Server Logins  create user  grant permission    
SELECT LOGINPROPERTY('mydbusername','PASSWORDHASH');

CREATE LOGIN [mydbusername] 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

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


top   prev   next  
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
top
top   prev   next  

add sysadmin permission

EXEC sp_addrolemember N'sysadmin', N'itsadmin';
GO
EXEC master..sp_addsrvrolemember @loginame = N'itsadmin', @rolename = N'sysadmin'
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER my_username;
GO
EXEC master..sp_addsrvrolemember @loginame = N'NT AUTHORITY\SYSTEM', @rolename = N'sysadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'JEWEL\MSSQLGRPFWI', @rolename = N'sysadmin'
GO
EXEC master..sp_dropsrvrolemember @loginame = N'PHOTOGAL', @rolename = N'sysadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'JEWEL\MSSQLGRPFWI', @rolename = N'sysadmin'
GO
EXEC master..sp_dropsrvrolemember @loginame = N'PHOTOGAL', @rolename = N'sysadmin'
GO
EXEC master..sp_dropsrvrolemember @loginame = N'ITSAdmin', @rolename = N'bulkadmin'
GO
EXEC master..sp_dropsrvrolemember @loginame = N'ITSAdmin', @rolename = N'sysadmin'
GO
top
top   prev   next  

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


top   prev   next  
-- 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


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

top   prev   next  

rebuild msdb

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"

-- ----------------------------------------------------------------
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 -SInstName -E
1> Restore database MODEL with RECOVERY
2> go
ola.hallengren.com  ole automation