SP_WHO to see SQL statement

Quick query to see user and full SQLstatement.  Don't forget one can use the Profile from SSMS for a full view of all db activity.


SELECT  D.text SQLStatement, A.Session_ID SPID, ISNULL(B.status,A.status) Status,
A.login_name Login, A.host_name HostName, C.BlkBy,  DB_NAME(B.Database_ID) DBName,
B.command, ISNULL(B.cpu_time, A.cpu_time) CPUTime, ISNULL((B.reads + B.writes),
(A.reads + A.writes)) DiskIO,  A.last_request_start_time LastBatch, A.program_name FROM
   sys.dm_exec_sessions A    LEFT JOIN    sys.dm_exec_requests B  
 ON A.session_id = B.session_id   LEFT JOIN    
 (        SELECT                 A.request_session_id SPID,        
       B.blocking_session_id BlkBy        
  FROM sys.dm_tran_locks as A          
 INNER JOIN sys.dm_os_waiting_tasks as B        
  ON A.lock_owner_address = B.resource_address        ) C
  ON A.Session_ID = C.SPID   OUTER APPLY sys.dm_exec_sql_text(sql_handle) D

Rank Over

,RANK() OVER
    (PARTITION BY col] ORDER BY [colA] desc, [colB] desc) AS Rank

from MSN  (http://msdn.microsoft.com/en-us/library/ms176102.aspx):

USE AdventureWorks2012;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,RANK() OVER 
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i 
INNER JOIN Production.Product AS p 
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;
GO

SQL Server DB extract and restore from Litespeed

declare @extractor varchar(4000)
, @fullbkp_filename varchar(4000)
, @fullbkp_filepath varchar(4000)
, @ext_path varchar(4000)
,@database varchar(100)
,@fullrestore varchar(4000)
,@datafilepath varchar(500)
,@logfilepath varchar(500)
set @ext_path='R:\Litespeed'
set @fullbkp_filepath='R:\Litespeed\archive_bkps'
set @fullbkp_filename='ods_stage_201211011253.bak'
set @database='ods_stage'
set @datafilepath='R:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data'
set @logfilepath='L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Logs'

--EXTRACTOR
set @extractor='xp_cmdshell '''+@ext_path+'\extractor.exe -E  '+@fullbkp_filepath+'\'+@fullbkp_filename+' -F '+@fullbkp_filepath+'\'+@fullbkp_filename+''''
exec (@extractor)

--FULL
set @fullrestore='
RESTORE DATABASE '+@database+'
FROM  DISK = N'''+@fullbkp_filepath+'\'+@fullbkp_filename+''+'0''
,DISK = N'''+@fullbkp_filepath+'\'+@fullbkp_filename+''+'1''
,DISK = N'''+@fullbkp_filepath+'\'+@fullbkp_filename+''+'2''
with replace
,  MOVE N'''+@database+''+'_data'' TO N'''+@datafilepath+'\'+@database+'_data.mdf''
,  MOVE N'''+@database+''+'_log'' TO N'''+@logfilepath+'\'+@database+'_log.ldf''
,norecovery'
exec (@fullrestore)

--DIFF (enable reocvery after last diff apply)
RESTORE DATABASE [scratch]
FROM  DISK = N'R:\LiteSpeed\U1048487_201210311424.bak'
with file=2
--toggle to norecovery if need to apply another diff
, recovery


--toggle to norecovery if need to apply a diff
--,norecovery

/*
,  NOUNLOAD,  STATS = 10*/

/*
RESTORE DATABASE [scratch] FROM  DISK = N'R:\LiteSpeed\scratch_2012102108151.bak' WITH  FILE = 2
,  MOVE N'scratch' TO N'R:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\scratch.mdf'
,  MOVE N'scratch_log' TO N'L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Logs\scratch.ldf',  NOUNLOAD,  STATS = 10

RESTORE DATABASE [scratch] FROM  DISK = N'R:\LiteSpeed\scratch_2012102108152.bak' WITH  FILE = 3
,  MOVE N'scratch' TO N'R:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\scratch.mdf'
,  MOVE N'scratch_log' TO N'L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Logs\scratch.ldf',  NOUNLOAD,  STATS = 10*/

quick db restore

RESTORE DATABASE em700
FROM  DISK = N'C:\em700201211161241.BKP'

with replace

Quick sql server db bkp

--db name   em700

--use master db for backups
use master

--variables
declare @year varchar(4)set @year=datepart(yy,getdate())
declare @month varchar(2)set @month=case when datepart(mm,getdate())<10 then '0'+cast(datepart(mm,getdate()) as varchar(1)) else cast(datepart(mm,getdate())as varchar) end
declare @day varchar(2)set @day=case when datepart(dd,getdate())<10 then '0'+cast(datepart(dd,getdate()) as varchar(1)) else cast(datepart(dd,getdate())as varchar) end
declare @hour varchar(2) set @hour=case when datepart(hh,getdate())<10 then '0'+cast(datepart(hh,getdate()) as varchar(1)) else cast(datepart(hh,getdate())as varchar) end
declare @minute varchar(2)set @minute=case when datepart(mi,getdate())<10 then '0'+cast(datepart(mi,getdate()) as varchar(1)) else cast(datepart(mi,getdate())as varchar) end

--create bkp device
declare @sqlscript varchar(2000)
set @sqlscript='exec sp_addumpdevice ''disk'', ''em700_networkdevice'', ''\\udrsql11\C$\em700'+@year+''+@month+''+@day+''+@hour+''+@minute+'.BKP'''
exec (@sqlscript)

--bkp db
BACKUP DATABASE [em700] TO [em700_networkdevice] WITH  INIT ,  NOUNLOAD ,  RETAINDAYS = 5,  NAME = N'em700 backup',  NOSKIP ,  STATS = 10,  DESCRIPTION = N'em700 backup',  NOFORMAT DECLARE @i INT
select @i = position from msdb..backupset where database_name='em700'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='em700')
RESTORE VERIFYONLY FROM  [em700_networkdevice]  WITH FILE = @i

--drop bkp device
exec sp_dropdevice 'em700_networkdevice'

Moving User databases

-->Moving user databases

-->Detach the database as follows
use master
   go
   sp_detach_db 'db'
   go

-->Next, copy the data files and the log files from the current location
-->(D:\Mssql7\Data) to the new location (E:\Sqldata).

-->Re-attach the database
use master
  go
  sp_attach_db 'db',
'H:\MSSQL\db1.mdf',
'H:\MSSQL\db2.mdf',
'H:\MSSQL\db3.mdf',
'I:\MSSQL\db4.mdf',
'I:\MSSQL\db5.mdf',
'I:\MSSQL\db6.mdf',
'N:\MSSQL\db_log.ldf'
--'N:\MSSQL\db_log2.ldf'
  go

-->Verify

use db
   go
   sp_helpfile
   go

-->source: http://support.microsoft.com/kb/224071
-->source for tempdb:


use master
go
Alter database tempdb modify file (name = tempdev, filename = 'K:\mssql\tempdev.mdf')
Alter database tempdb modify file (name = tempdev2, filename = 'K:\mssql\tempdev2.mdf')
Alter database tempdb modify file (name = tempdev3, filename = 'K:\mssql\tempdev3.mdf')
Alter database tempdb modify file (name = tempdev4, filename = 'K:\mssql\tempdev4.mdf')
Alter database tempdb modify file (name = tempdev5, filename = 'K:\mssql\tempdev5.mdf')
Alter database tempdb modify file (name = tempdev7, filename = 'K:\mssql\tempdev7.mdf')


go
Alter database tempdb modify file (name = templog, filename = 'J:\mssql\templog.ldf')
go

Update DB recovery model

--VIEW

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

--CHANGE

USE master ;
ALTER DATABASE model SET RECOVERY FULL /*SIMPLE*/