Batch File Commands

From the old days, sometimes batch file becomes very handy

http://academic.evergreen.edu/projects/biophysics/technotes/program/batch.htm

sp_MSforeachtable

create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 'insert into #rowcount select ''?'', count(*) from ?'
select top 5 * from #rowcount order by rowcnt desc
select * from #rowcount
drop table #rowcount

Transparent Data Encryption in SQL Server 2008

http://www.sql-server-performance.com/articles/dba/Transparent_Data_Encryption_p1.aspx

http://www.mssqltips.com/tip.asp?tip=1507

http://msdn.microsoft.com/en-us/library/bb677274.aspx


You encounter SQL Server error 17308 after you enable TDE on one of your databases in SQL Server

Moving System Databases - A Checklist

Moving System Databases
Moving a database in SQL Server is normally as simple as detaching it from the server, moving it to it's new location and then attaching it again. However, that only counts for user databases. Moving system databases requires quite a bit more work. Since I had to do this a couple of times during the last weeks I wrote down a simple checklist that I could follow to make sure it was done right, so I did not screw up the servers and could speed up the process as much as possible. And, since I guess I will be doing it again sometime in the future and inevitably will lose the notes I scribbled down, I thought I could just as well post them here for safe-keeping. If anyone wants to use this checklist go ahead, but remember to do the steps in the exact order of the list, and make sure you have all the necessary backups before starting. To be completely clear, doing this wrong can completely screw up your databases and I can not take any responsibility if anything does go wrong. Also note that the checklist was written for the specific situation I was encountered with. Your system databases might have more data files and/or other file paths and names than those in the list, so you might need to make some changes. Have fun!

Make sure you have backups of all user databases plus master, model and msdb.
Moving msdb and model
In Enterprise Manager, right-click the server and choose Properties.
Click Startup Parameters.
Add a new parameter "-T3608" (without the quotes)
Stop SQL Server.
Start SQL Server, and make sure that SQL Agent is NOT started.
Run the following command in Query Analyzer:
-------------
use master
go
exec sp_detach_db 'msdb'
go
exec sp_detach_db 'model'
go
-------------

Move the data and log files for both msdb (normally msdbdata.mdf and msdblog.ldf) and model (normally model.mdf and modellog.mdf) to their new locations.
Run the following in Query Analyzer:
-------------
use master
go
exec sp_attach_db 'model'
, 'PATH_TO_MODEL_DATAFILE\model.mdf'
, 'PATH_TO_MODEL_LOGFILE\modellog.ldf'
go
-------------

Remove the -T3608 flag in Enterprise Manager/Server/Properties/Startup Parameters.
Stop SQL Server.
Start SQL Server.
Run the following in Query Analyzer and check that the file paths are correct:
-------------
use model
go
exec sp_helpfile
go
-------------

Run the following in Query Analyzer:
-------------
use master
go
exec sp_attach_db 'msdb'
, 'PATH_TO_MSDB_DATAFILE\msdbdata.mdf'
, 'PATH_TO_MSDB_LOGFILE\msdblog.ldf'
go
-------------

Run the following in Query Analyzer and check that the file paths are correct:
-------------
use msdb
go
exec sp_helpfile
go
-------------

Finished!
Moving tempdb
Run the following in Query Analyzer:
-------------
use master
go
alter database tempdb modify file (name = tempdev
, filename = 'PATH_TO_NEW_LOCATION_OF_TEMPDB_DATAFILE\tempdb.mdf')
go
alter database tempdb modify file (name = templog
, filename = 'PATH_TO_NEW_LOCATION_OF_TEMPDB_DATAFILE\templog.ldf')
go
-------------

Stop SQL Server
Start SQL Server
Run the following in Query Analyzer and check that the file paths correspond to those stated in step 1:
-------------
use tempdb
go
exec sp_helpfile
go
-------------

Stop SQL Server.
Move the original data and log files for tempdb to some new location, or rename them, just so that you are sure that SQL Server can not be using them.
Start SQL Server.
Run the following in Query Analyzer and check that no error occurs:
-------------
use tempdb
go
create table test (a int)
insert into test (a) values (1)
select * from test
drop table test
go
-------------

Remove the original data and log files for tempdb.
Finished!

NOTE: Steps 5 through 8 are of course not really necessary, they are just included as a quick extra check to really make sure that nothing went wrong. Skip them if you wish.
Moving master

Note: In this scenario we are not only moving the master database, we are also moving all of the files that SQL Server uses in it's 'data location' (as specified when installing SQL Server). The situation I encountered was that SQL Server's data location was specified to be something like D:\ (though with the program files as normal on C:\Program Files\Microsoft SQL Server\), but now the entire D:\ drive needed to be removed, so we needed to move everything SQL Server had stored there plus all references to it to avoid problems in the future. If you are only moving the master database you only need to follow the applicable steps of course.
In Enterprise Manager, right-click the server and choose Properties.
Click Startup Parameters.
Remove all of the three parameters that are already there (if there are more, remove the three that correspond to the three below in step 4).
Add the three following parameters:
-dPATH_TO_NEW_LOCATION_OF_MASTER_MDFFILE\master.mdf
-ePATH_TO_NEW_LOCATION_OF_SQLAGENT_ERRORLOG\ERRORLOG
-lPATH_TO_NEW_LOCATION_OF_MASTER_LOGFILE\mastlog.ldf

In my case the values of these parameters where as follows:
-dE:\MSSQL\Data\master.mdf
-eE:\MSSQL\LOG\ERRORLOG
-lE:\MSSQL\Data\mastlog.ldf
Stop SQL Server.
Move the files as specified below:
OLD_PATH_TO_MASTER_MDFFILE\master.mdf --> NEW_PATH_TO_MASTER_MDFFILE\master.mdf
OLD_PATH_TO_MASTER_LOGFILE\Data\mastlog.ldf --> NEW_PATH_TO_MASTER_LOGFILE\mastlog.ldf
OLD_PATH_TO_SQL_DATA_LOCATION\BACKUP --> NEW_PATH_TO_SQL_DATA_LOCATION
(the entire folder with everything in it)
OLD_PATH_TO_SQL_DATA_LOCATION\JOBS --> NEW_PATH_TO_SQL_DATA_LOCATION
(the entire folder with everything in it)
OLD_PATH_TO_SQL_DATA_LOCATION\LOG --> NEW_PATH_TO_SQL_DATA_LOCATION
(the entire folder with everything in it)
OLD_PATH_TO_SQL_DATA_LOCATION\REPLDATA --> NEW_PATH_TO_SQL_DATA_LOCATION
(the entire folder with everything in it)

Make the following changes to the registry (using regedit):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
BackupDirectory = NEW_PATH_TO_SQL_DATA_LOCATION\BACKUP

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Replication
WorkingDirectory = NEW_PATH_TO_SQL_DATA_LOCATION\REPLDATA

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
SQLDataRoot = NEW_PATH_TO_SQL_DATA_LOCATION\

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent
ErrorLogFile = NEW_PATH_TO_SQL_DATA_LOCATION\LOG\SQLAGENT.OUT
WorkingDirectory = NEW_PATH_TO_SQL_DATA_LOCATION\JOBS

Note: This checklist does not cover servers using full-text search. If your server does use FT, then you will need to expand steps 6 and 7. Just move the FT directory in step 6 and search the registry for any references to it and change them as necessary.
Start SQL Server.
Finished!
If you are using SQL Agent on your server do not forget to check that it is running.
Conclusion
So there we are, all system databases moved. Again, please note that this was mainly meant as a checklist for myself, but feel free to use it as a base for your own checklist when you need to move system databases. I urge you to read through it several times so you are sure what it says and what you are doing. Most of the steps here come from the Microsoft article Moving SQL Server databases to a new location with Detach/Attach, which will probably help you more than this list. I simply compiled them into an easy-to-follow, step-by-step list that I could use to cut the down-time as much as possible.

By Christoffer Hedgate, 2004/11/30

Multi-statement Table Valued Function vs Inline Table Valued Function

remove orphan users

--create proc usp_remove_orphan_users as
set nocount on

-- Section 1: Create temporary table to hold databases to process

-- drop table if it already exists
if (select object_id('tempdb..#dbnames')) is not null
drop table #dbnames

-- Create table to hold databases to process
create table #dbnames (dbname varchar(128))

-- Section 2: Determine what databases have orphan users
exec master.dbo.sp_MSforeachdb 'insert into #dbnames select ''?'' from master..syslogins l right join ?..sysusers u
on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1
and (u.name <> ''INFORMATION_SCHEMA'' and u.name <> ''guest'' and u.name <> ''system_function_schema'')
having count(*) > 0'

-- Section 3: Create local variables needed
declare @CNT int
declare @name char(128)
declare @sid varbinary(85)
declare @cmd nchar(4000)
declare @c int
declare @hexnum char(100)
declare @db varchar(100)

-- Section 5: Process through each database and remove orphan users
select @cnt=count(*) from #DBNAMES
While @CNT > 0
begin

-- get the name of the top database
select top 1 @db=dbname from #DBNAMES

-- delete top database
delete from #DBNAMES where dbname = @db

-- Build and execute command to determine if DBO is not mapped to login
set @cmd = 'select @cnt = count(*) from master..syslogins l right join ' +
rtrim(@db) + '..sysusers u on l.sid = u.sid' +
' where l.sid is null and u.name = ''DBO'''
exec sp_executesql @cmd,N'@cnt int out',@cnt out

-- if DB is not mapped to login that exists map DBO to SA
if @cnt = 1
begin
print 'exec ' + @db + '..sp_changedbowner ''SA'''
-- exec sp_changedbowner 'SA'
end -- if @cnt = 1


-- drop table if it already exists
if (select object_id('tempdb..#orphans')) is not null
drop table #orphans

-- Create table to hold orphan users
create table #orphans (orphan varchar(128))

-- Build and execute command to get list of all orphan users (Windows and SQL Server)
-- for current database being processed
set @cmd = 'insert into #orphans select u.name from master..syslogins l right join ' +
rtrim(@db) + '..sysusers u on l.sid = u.sid ' +
'where l.sid is null and issqlrole <> 1 and isapprole <> 1 ' +
'and (u.name <> ''INFORMATION_SCHEMA'' and u.name <> ''guest'' ' +
'and u.name <> ''system_function_schema'')'
exec (@cmd)


-- Are there orphans
select @cnt = count(*) from #orphans

WHILE @cnt > 0
BEGIN

-- get top orphan
select top 1 @name= orphan from #orphans

-- delete top orphan
delete from #orphans where orphan = @name

-- Build command to drop user from database.
set @cmd = 'exec ' + rtrim(@db) + '..sp_revokedbaccess ''' + rtrim(@name) + ''''
print @cmd
--exec (@cmd)


-- are there orphans left
select @cnt = count(*) from #orphans
end -- WHILE @cnt > 0


-- are the still databases to process
select @cnt=count(*) from #dbnames

end -- while @cnt > 0

-- Remove temporary tables
drop table #dbnames, #orphans

SQL Server DATE/TIME Workbench

Reviewing AutoGrow events from the default trace

DECLARE
@path VARCHAR(255),
@cmd VARCHAR(300);

-- customize this path, of course, if necessary:
SELECT
@path = 'C:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\',
@cmd = 'dir /b "' + @path + '*.trc"';

DECLARE @files TABLE
(
fn VARCHAR(64)
);

INSERT @files
EXEC master..xp_cmdshell @cmd;

DELETE @files
WHERE fn IS NULL;

SELECT
e.DatabaseName,
e.[FileName],
e.SPID,
e.Duration,
e.StartTime,
e.EndTime,
FileType = CASE e.EventClass
WHEN 92 THEN 'Data'
WHEN 93 THEN 'Log'
END,
[TraceFile] = f.fn
FROM
@files f
CROSS APPLY
fn_trace_gettable(@path + f.fn, DEFAULT) e
WHERE
e.EventClass IN (92,93)
-- AND f.fn LIKE 'log[_][0-9]%.trc'
ORDER BY
e.StartTime DESC;

Excel - if a cell value exist in list on other column list

=IF(ISNA(VLOOKUP(A15,$E$1:$E$600,1,FALSE)),"",A15)

Cleanup duplicate record, another interesting method

SET ROWCOUNT 1
AGAIN:
DELETE member_mlc FROM
(SELECT memberid,mlc_status FROM member_mlc GROUP BY memberid,mlc_status HAVING COUNT(*) > 1) a
WHERE member_mlc.memberid = a.memberid and member_mlc.mlc_status = a.mlc_status
IF @@ROWCOUNT > 0 GOTO AGAIN
SET ROWCOUNT 0