declare @bksource varchar(100)
select @bksource=(
select a.physical_device_name
from [msdb].[dbo].[backupmediafamily] a join [msdb].[dbo].[backupset] b on b.media_set_id = a.media_set_id
where b.backup_start_date =
(
  select max( backup_start_date ) from [msdb].[dbo].[backupset]
   where database_name = b.database_name and type = 'D'
)
and database_name = '<database_name>'
)

-- print @bksource

restore database [<Database_Name2>] from disk = @bksource with replace, stats = 10,
MOVE '<Database_Name>' TO 'i:\mssql\data\test\<Database_Name2>.MDF',
MOVE '<Database_Name>_log' TO 'j:\mssql\xlog\test\<Database_Name2>_log.LDF'
GO

Replace anything inside the <> with the proper names and set the correct drive letters.

How do I get all my SQL server healthy?

During one of the sessions there was a brief discussion about DBCC CHECKDB.  The long and short of it:  It is important, do them, it is not an option to skip this check.  It is right up there with Index maintenance and updating statistics.  But, no one ever said that you had to run them on your production database.  I repeat, no one said you had to run them on your production database.  Here's a couple of thoughts.  First when you do your weekly backup/restore test, notice I didn't say 'if', run the DBCC CHECKDB against your restored copy.  If you have inconsistencies in your production database, it will also exist in an exact copy of you database.  But remember to fix it on your production database and not you copy that you will drop later.  Another option is if you use backup/restore to create a report server.  This is an excellent way to do the checks and verify your backups are good.  We have a couple of systems that we do this every night when the SQL backup is complete, we restore it the report server.  Proved the backups are good.  I then look for a time that Crystal Info is not pounding the report server and run the dbcc checks against that database copy.  I have it e-mail me if it finds any errors.  Kills two birds with one stone.  Now for the truth in my shop.  With over 120 instances and over 1600 databases, a weekly backup test and DBCC CHECKDB is just not practical.  So I use a hybrid approach.  The servers that refresh a report server daily are easy perfect candidates, our larger (high priority) databases get a restore to a test server and DBCC CHECKDB there, and the other databases get an occasional backup/restore test and DBCC CHECKDB run against the production database once per week during slow times.  So think outside the box.  Find a way to verify your database's consistancy.

Dep

Secondly, I decided that I wanted to change my twitter handle and I thought about it for quite a while and thought blending my last name with my job as a SQL Server DBA.  So I morphed into SQLLawman.  I'm following suit and changed my blog to follow suit.

Finally, I found a new theme for my blog and I really like the simple nature, so I think I'll work from here to edit and add features as time goes by.

1. Every Gold is worth 3 points
2. Every Silver is worth 2 points
3. Every Bronze is worth 1 point

Yep, more points for the winners.  But this is where my scoring becomes interesting.  I divide the number of points by the number of athletes.  I found the number of athletes from http://www.vancouver2010.com/.  What I find cool about this is that figures out which country sent the best athletes.  That's my idea of who wins.  Since this my blog I get to make my own rules.  So without further ado, here's the current top 10: Final Standings Updated 2/28/10 2:55 pm EST

Well, this has absolutely nothing to do with that, but it will be fun and energetic all the same.  If you are going to the PASS Summit click here for more information and to register for the event. If you want to ensure there is enough bacon for everyone, please register before the event.  Unfortunately you will not get to opportunity to meet me this  year at the summit, but I'm really hoping for next year.  So register, get some free stuff and bacon.

Declare @UserID varchar(25), @status nvarchar(5), @Command varchar(255), @command1 varchar(255)
Declare Account_cursor Cursor
for select name from sys.syslogins where name not like '<DOMAIN>\%'
open Account_cursor
FETCH NEXT FROM Account_cursor
INTO @UserID
WHILE @@FETCH_STATUS = 0
BEGIN
select @status= convert(nvarchar,(loginproperty(@userID,N'IsLocked')))
if @status = '1'
BEGIN
print '-- ' + @userID + ' is locked'
select @command = 'ALTER LOGIN [' + @userID + '] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF, NO CREDENTIAL'
select @command1 = 'ALTER LOGIN [' + @userID + '] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON, NO CREDENTIAL'
print @command
print @command1
-- exec (@command)
-- exec (@command1)
END
fetch next from Account_Cursor into @userID
END
Close Account_cursor
Deallocate Account_cursor

Thank you @unclebiguns for pointing me toward the function that makes this all possible.

I am guessing that this is not the easiest or most efficient way to do this, so if anyone would like to educate me on a better way, I am very open to learning your techniques.

select a.name as 'Trigger', b.name as 'Table'
from sysobjects a
join sysobjects b
on a.parent_obj=b.id
where a.xtype = 'TR'

This gave me a nice little result set of each trigger and what table it was associated.  Maybe I won't have to re-invent the wheel the next time they ask.

Please consider donating.  Whole blood can be donated every 56 days.

CREATE PROCEDURE [dbo].[LockOutAdministrators]
WITH EXECUTE AS 'EXECUTIONER'
AS
BEGIN
BLAH, BLAH, BLAH
END

I then granted execute to the stored procedures to the application group.  So the application team can only update the table via the stored procedure that does only what it needs to do.  They are happy they have an easy way to do this without me becoming the bottle neck.  If this was a more sensitive system, I'd probably add some auditing, but it is not necessary at this time.