Sql Server Frequently Used Commands
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 mywindowsdomain.xxx.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: \\mywindowsdomain\dfs\dba
IF NOT EXIST P: net use M: \\mywindowsdomain\dfs\dba\promotions
IF NOT EXIST U: net use M: \\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
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=convert(sysname,serverproperty('Machinename'));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 my#_sp_who2;
go
DROP TABLE my#_sp_who2;
go
CREATE TABLE my#_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 my#_sp_who2
GO
INSERT INTO my#_sp_who2
EXEC sp_who2
GO
SELECT *
FROM my#_sp_who2
where Status not in ( 'sleeping', 'BACKGROUND' )
order by status, Login
go
SELECT *
FROM my#_sp_who2
order by Login
GO
SELECT *
FROM my#_sp_who2
WHERE Login like '%SAN%'
GO
SELECT *
FROM my#_sp_who2
WHERE upper(Login) like upper('%San%')
GO
TRUNCATE TABLE my#_sp_who2
GO
DROP TABLE my#_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;
DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS;
exec dbas.dbo.sp_helpfile
exec ocenvsproddb.dbo.sp_helpfile
exec xp_fixeddrives
go
EXEC sp_MSForEachDB '
use [?]
select @@servername as servername,
db_id(''?'') As DB_ID,
''?'' As DatabaseName,
CAST(size/128.0 AS int) AS FileSize,
name AS LogicalFileName, filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(size/128.0 - CAST(FILEPROPERTY(name,''SpaceUsed'' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((size/128.0 -CAST(FILEPROPERTY(name,''SpaceUsed'' ) AS int)/128.0)/(size/128.0)) aS decimal(4,2))) AS varchar(8)) AS FreeSpacePct
FROM dbo.sysfiles
-- where ''?'' not in ( ''master'', ''tempdb'',''model'',''msdb'')
-- where ''?'' in ( ''tempdb'')
order by db_id(''?'')
'
go
exec xp_fixeddrives;
go
use tempdb
go
select @@servername as servername,
db_id() As DB_ID,
db_name() As DatabaseName,
CAST(size/128.0 AS int) AS FileSize,
name AS LogicalFileName, filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(db_name(),'Status')) AS Status,
CONVERT(sysname,DatabasePropertyEx(db_name(),'Updateability')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(db_name(),'Recovery')) AS RecoveryMode,
CAST(size/128.0 - CAST(FILEPROPERTY(name,'SpaceUsed' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((size/128.0 -CAST(FILEPROPERTY(name,'SpaceUsed' ) AS int)/128.0)/(size/128.0)) aS decimal(4,2))) AS varchar(8)) AS FreeSpacePct
FROM dbo.sysfiles
go
top
top prev next
logfile shrink
select name from sys.databases;
go
select db_name()
go
-- check recovery_model simple or not
select database_id, name, recovery_model_desc
from master.sys.databases
order by recovery_model_desc,database_id;
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)';
go
EXEC sp_MSforeachdb 'DECLARE @ln varchar(100) SET @ln=(SELECT name FROM [?].dbo.sysfiles WHERE fileid=2) USE [?] DBCC SHRINKFILE (@ln, 0)';
go
exec master.sys.xp_fixeddrives;
go
EXEC sp_MSforeachdb
'
DECLARE @ln varchar(100)
SET @ln=(SELECT name FROM [?].dbo.sysfiles WHERE fileid=2)
USE [?]
print "-- " + convert(sysname,db_id());
print "use " + DB_NAME(); print "go";
print "dbcc shrinkfile ( " + convert(sysname,@ln) + ", 0 );";
print "go";
print "";
DBCC SHRINKFILE (@ln, 0)
'
go
exec master.sys.xp_fixeddrives;
go
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
top prev next
datafile or transaction logfile size
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
--Displaying log space information for all databases
DBCC SQLPERF(LOGSPACE);
GO
-- filesize is not the size of the datafile, look like a max_filesize
DBCC LOGINFO
GO
-- Finding open SQL transactions using DBCC OPENTRAN
DBCC OPENTRAN
-- Checking Pending Transaction In Transaction Log Files
select name, log_reuse_wait_desc, recovery_model_desc
from sys.databases where log_reuse_wait_desc not in ( N'NOTHING');
--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
DBCC FREEPROCCACHE;
GO
DBCC FREESYSTEMCACHE ('ALL');
Go
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO
DBCC FREESESSIONCACHE;
GO
use tempdb
go
DBCC SHRINKFILE (TEMPDEV, 20480);
GO
DBCC SHRINKFILE (TEMP2, 20480);
GO
DBCC SHRINKFILE (TEMPLOG, 10240);
GO
use master
go
# what is open transaction in tempdb and take proper action on that particular SPID.
dbcc opentran(tempdb)
go
use tempdb;
go
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
go
DBCC SQLPERF
DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS;
GO
-- pick "sql plan xml" -- find missing index
use master
go
;WITH task_space_usage AS (
-- SUM alloc/delloc pages
SELECT session_id,
request_id,
SUM(internal_objects_alloc_page_count) AS alloc_pages,
SUM(internal_objects_dealloc_page_count) AS dealloc_pages
FROM sys.dm_db_task_space_usage WITH (NOLOCK)
WHERE session_id <> @@SPID
GROUP BY session_id, request_id
)
SELECT TSU.session_id,
TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
EST.text,
-- Extract statement from sql text
ISNULL(
NULLIF(
SUBSTRING(
EST.text,
ERQ.statement_start_offset / 2,
CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
), ''
), EST.text
) AS [statement text],
EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
ON TSU.session_id = ERQ.session_id
AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC, 5 DESC;
GO
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
SELECT 'dbcc shrinkfile (''' + name + ''') -- shrink ' +
case type when 0 then ' db ' when 1 then ' log ' end + ' file ' xxx
FROM sys.master_files WHERE database_id = DB_ID(N'tempdb');
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=convert(sysname,serverproperty('Machinename'));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=convert(sysname,serverproperty('Machinename'));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 mywindowsdomain\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();
select @my_hostname=convert(sysname,serverproperty('Machinename'));
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();
select @my_hostname=convert(sysname,serverproperty('Machinename'));
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();
select @my_hostname=convert(sysname,serverproperty('Machinename'));
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'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\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
use [master]
GO
GRANT VIEW ANY DATABASE TO [myusername]
GO
use [master]
GO
GRANT VIEW ANY DEFINITION TO [myusername]
GO
use [master]
GO
GRANT VIEW SERVER STATE TO [myusername]
GO
grant select on [master].[sys].[master_files] to mydbuser;
revoke select on [master].[sys].[master_files] from mydbuser;
grant select on [master].[sys].[master_files] to mydbuser with grant option;
revoke select on [master].[sys].[master_files] from mydbuser cascade;
revoke VIEW ANY DATABASE from mydbuser;
revoke VIEW ANY DEFINITION from mydbuser;
revoke VIEW SERVER STATE from mydbuser;
grant VIEW ANY DATABASE to mydbuser;
grant VIEW ANY DEFINITION to mydbuser;
grant VIEW SERVER STATE to mydbuser;
rem at database level
use db
go
grant VIEW DATABASE STATE To xxx;
go
rem Find Other Permissions in SSMS
rem Select the Securables Pages
SELECT
l.name as grantee_name, p.state_desc, p.permission_name FROM
sys.server_permissions AS p JOIN sys.server_principals AS l ON
p.grantee_principal_id = l.principal_id WHERE
permission_name in ( 'VIEW Server State', 'VIEW ANY DATABASE') ;
use master
exec sp_helpsrvrole
exec sp_helpsrvrole 'sysadmin';
exec sp_helpsrvrolemember 'sysadmin';
exec sp_helplogins
exec sp_helplogins ignite
EXEC sp_helprole;
EXEC sp_helprole 'db_datareader';
EXEC sp_helprolemember 'db_datareader';
exec sp_helpuser
exec sp_helpuser ignite
-- object level permission
exec sp_helprotect
exec sp_helprotect 'xp_fixeddrives';
exec sp_helprotect @username='ignite'
-- server permission
SELECT
pe.state_desc collate database_default + ' ' + pe.permission_name + ' to ' + pr.name + ';' server_permission
-- + '-- ' + rtrim(convert(nchar,pr.principal_id)) + ' ' + pr.type_desc collate database_default
FROM sys.server_principals AS pr
JOIN sys.server_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id
where type_desc in ( N'SQL_LOGIN', 'SERVER_ROLE', 'SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP' )
and pe.permission_name not in ( N'CONNECT SQL' )
and pr.name in ( N'my_USER_NAME' )
order by pr.name,type_desc;
-- server level
SELECT
l.name as grantee_name, p.state_desc, p.permission_name FROM
sys.server_permissions AS p JOIN sys.server_principals AS l ON
p.grantee_principal_id = l.principal_id
where l.name in ( 'LNKUTERM' );
-- grant server role: ALTER SERVER ROLE [sysadmin] ADD MEMBER [sa]
select N'ALTER SERVER ROLE [ ' +
CAST(sp2.NAME AS VARCHAR(255)) + N'] ADD MEMBER [' + sp.name + N'] /*' +
sp.type_desc collate database_default + N' */;' cmd
FROM sys.server_principals sp2, sys.server_principals sp,
sys.server_role_members srm2, sys.server_role_members srm
where
srm2.role_principal_id = sp2.principal_id
and sp.principal_id = srm.member_principal_id
and srm2.member_principal_id = srm.member_principal_id
-- and sp.name=N'sa'
;
-- grant database role: ALTER ROLE [db_datareader] [db_datawriter] [db_owner] ADD MEMBER [my_username]
SELECT N'ALTER ROLE [' + CAST(dp3.NAME AS VARCHAR(255)) +
N'] ADD MEMBER [' + dp2.name + N'];' permission
FROM sys.database_principals dp2,
sys.database_role_members drm2,
sys.database_principals dp3,
sys.database_principals dp,
sys.sysusers u
WHERE
dp2.principal_id = dp.principal_id
and dp2.principal_id = drm2.member_principal_id
and dp3.principal_id = drm2.role_principal_id
and u.uid = dp.principal_id
-- and dp2.name = N'mywindowsdomain\mywindowsusername'
order by dp2.name, dp3.principal_id
;
-- extract database permission --- database level
select p.state_desc collate database_default +
N' ' + p.permission_name + N' to ' + u.name + N';' permission
from sys.database_permissions p, sys.database_principals u
where u.principal_id = p.grantee_principal_Id
and p.class_desc in ( N'DATABASE')
and u.name = N'myusername' ;
select princ.name
, princ.type_desc
, perm.permission_name
, perm.state_desc
, perm.class_desc
, object_name(perm.major_id)
from sys.database_principals princ
left join
sys.database_permissions perm
on perm.grantee_principal_id = princ.principal_id
where princ.name in ( 'ignite', 'myusername');
-- extract database permission --- object or table level
select state_desc COLLATE DATABASE_DEFAULT + ' ' + permission_name
+ ' on [' + sys.schemas.name
+ '].[' + sys.objects.name + '] to ['
-- + user_name(sys.database_permissions.grantee_principal_id)
+ sys.database_principals.name
+ ']' Permissions
from sys.database_permissions join sys.objects
on sys.database_permissions.major_id = sys.objects.object_id join sys.schemas
on sys.objects.schema_id = sys.schemas.schema_id join sys.database_principals
on sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id
where
-- user_name(sys.database_permissions.grantee_principal_id) = N'MYDBUSERNAME'
sys.database_principals.name = N'MYDBUSERNAME'
order by sys.database_principals.name,sys.objects.name;
-- database level
SELECT
[UserName] = ulogin.[name],
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = princ.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.type_desc -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[ObjectName] = CASE perm.[class]
WHEN 1 THEN OBJECT_NAME(perm.major_id) -- General objects
WHEN 3 THEN schem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
END,
[ColumnName] = col.[name]
FROM
--database user
sys.database_principals princ
LEFT JOIN
--Login accounts
sys.server_principals ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
--Table columns
sys.columns col ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
LEFT JOIN
sys.schemas schem ON schem.[schema_id] = perm.[major_id]
LEFT JOIN
sys.database_principals imp ON imp.[principal_id] = perm.[major_id]
WHERE
princ.[type] IN ('S','U','G') AND
princ.[name] in ( 'ignite', 'applnvs', 'lnkuterm' ) AND
-- No need for these system accounts
princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA');
-- database default collate: SELECT name, collation_name FROM master.sys.databases;
USE [master]
GO
select LOGINPROPERTY(N'sa',N'PasswordLastSetTime') as PasswordLastSetTime;
GO
USE [master]
GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [master]
GO
select LOGINPROPERTY(N'sa',N'PASSWORDHASH') as passwordhash;
GO
ALTER LOGIN [sa] WITH PASSWORD = 0x02004... HASHED,
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [master]
GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [master]
GO
select LOGINPROPERTY(N'sa',N'PasswordLastSetTime') as PasswordLastSetTime;
GO
loginproperty
declare @mylogin nvarchar(256);
set @mylogin=N'sa';
SELECT
@mylogin mylogin,
LOGINPROPERTY(@mylogin,N'PASSWORDHASH') as passwordhash,
LOGINPROPERTY(@mylogin,N'BadPasswordCount') AS BadPasswordCount,
LOGINPROPERTY(@mylogin,N'IsLocked') AS IsLocked,
LOGINPROPERTY(@mylogin,N'PasswordLastSetTime') as PasswordLastSetTime,
LOGINPROPERTY(@mylogin,N'PasswordHashAlgorithm') as PasswordHashAlgorithm,
LOGINPROPERTY(@mylogin,N'HistoryLength');
SELECT LOGINPROPERTY(name, 'BadPasswordCount') AS 'BadPasswordCount'
,LOGINPROPERTY(name, 'BadPasswordTime') AS 'BadPasswordTime'
,LOGINPROPERTY(name, 'DaysUntilExpiration') AS 'DaysUntilExpiration'
,LOGINPROPERTY(name, 'DefaultDatabase') AS 'DefaultDatabase'
,LOGINPROPERTY(name, 'DefaultLanguage') AS 'DefaultLanguage'
,LOGINPROPERTY(name, 'HistoryLength') AS 'HistoryLength'
,LOGINPROPERTY(name, 'IsExpired') AS 'IsExpired'
,LOGINPROPERTY(name, 'IsLocked') AS 'IsLocked'
,LOGINPROPERTY(name, 'IsMustChange') AS 'IsMustChange'
,LOGINPROPERTY(name, 'LockoutTime') AS 'LockoutTime'
,LOGINPROPERTY(name, 'PasswordHash') AS 'PasswordHash'
,LOGINPROPERTY(name, 'PasswordLastSetTime') AS 'PasswordLastSetTime'
,LOGINPROPERTY(name, 'PasswordHashAlgorithm') AS 'PasswordHashAlgorithm'
,is_expiration_checked As 'is_expiration_checked'
FROM sys.sql_logins
WHERE
-- is_policy_checked = 1
name = N'SA';
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