The title of this post is a tribute to the fantastic Conor vs. SQL blog.

I was working on a database with several triggers on each table. The triggers audited INSERTs, UPDATEs and DELETEs by inserting records to shadow tables in a separate logging database. The database was backed up from one environment and restored to another in which the name of separate logging database was different. I wrote the following script to automatically update all the triggers, replacing any occurrence of the old logging database name with the correct new name.

Heads up: you could really do some damage with this script. Backup and test a restore from that backup before using it!

DECLARE @replaceThis VARCHAR(8000)
DECLARE @withThis VARCHAR(8000)
SET @replaceThis = 'OldLogDatabaseName'
SET @withThis = 'NewLogDatabaseName'
DECLARE c Cursor
FOR
    SELECT
        object_id,
        SCHEMA_NAME(schema_id) AS schema_name,
        name AS object_name
    FROM sys.objects
    where type_desc = 'SQL_TRIGGER'
    ORDER BY modify_date;
OPEN c
DECLARE @triggerObjectId AS INT
DECLARE @triggerSchema AS NVARCHAR(4000)
DECLARE @triggerName AS NVARCHAR(4000)
DECLARE @triggerDefinition AS NVARCHAR(MAX)
DECLARE @sql AS NVARCHAR(MAX)
FETCH NEXT FROM c INTO @triggerObjectId, @triggerSchema, @triggerName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    SELECT    @triggerDefinition = m.definition
    FROM    sys.sql_modules m, sys.triggers t
    WHERE    m.object_id = t.object_id
        AND t.object_id = @triggerObjectId
    PRINT 'Dropping ' + @triggerName + '...'
    SET @sql = 'DROP TRIGGER ' + @triggerSchema + '.' + @triggerName
    EXEC sp_ExecuteSql @sql;
    PRINT 'Recreating ' + @triggerName + '...'
    SET @sql = REPLACE(@triggerDefinition, @replaceThis, @withThis)   
    EXEC sp_ExecuteSql @sql;
    FETCH NEXT FROM c INTO @triggerObjectId, @triggerSchema, @triggerName
END
CLOSE c
DEALLOCATE c

i was on a client-of-a-client-of-a-client-site yesterday, parachuted in to address some issues with a .NET thick client application i hadn't seen before. the thing was a VB WinForms client running on 2000 and XP, making (predominantly) stored procedure calls to a SQL Server 2000 back end. Two versions of the client had been deployed:

• a older version, running on five client machines in .NET 1.1 using the Crystal "9" (i believe) bundled with VS2003
• a newer version, running on a single client machine in .NET 2.0 using Crystal 10 (bundled with VS 2005) and including some new functionality.

My problem (one of my problems, for there were many) was how to establish a dev environment on my XPSP2 notebook given that I had only SQL Server Express 2005. After a quick survey of the web, it seemed as though restoring SQL Server 2000 backups to SQL Server Express 2005 should be possible, but that people out there had run into a dauntingly wide range of disturbingly un-informative error messages while attempting to do so.

So, the first big find that I'm really happy with is Microsoft's SQL Server Management Studio Express (currently available in CTP release). It provides (in my opinion) a nicer UI for dev/admin-y work than Enterprise Manager or Query Analyser, and is free, as far as I can tell. For a CTP, it was quite robust. I didn't run into any crashes or odd behaviours other than in the area of restoring backups, which brings me back to our topic.

Having used the client's SQL Server's licensed Enterprise Manager, on their network, to perform a full backup of the database in question, and transferred it to my laptop using a USB key, i tried using SSMSE to restore it directly:

 

but i got this error:

 

Or, for those about to google: System.Data.SqlClient: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\Data\DBName_Data.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Express.Smo)

anyway, the fix i found was to use mark russinovich of sysinternals' junction tool to set up an NTFS reparse point (kind of like a hard link in Unix) which causes requests to the MSSQL\Data folder the SqlClient was looking in on behalf of SMO above to be directed to the same location as requests to the folder the actual data turned out to be stored in (which i determined using filemon) by running this command-line:

 

C:\>junction "C:\program files\Microsoft SQL Server\MSSQL"
   "C:\program files\Microsoft SQL Server\MSSQL.1\MSSQL"
Junction v1.03 - Win2K junction creator and reparse point viewer
Copyright (C) 2000-2002 Mark Russinovich
Systems Internals - http://www.sysinternals.com
Created: C:\program files\Microsoft SQL Server\MSSQL
Targetted at: C:\program files\Microsoft SQL Server\MSSQL.1\MSSQL

after which, i was able to restore the database successfully and get some actual work done. hooray for kludgey hacks to deal with CTPs, semi-supported scenarios and high-pressure client environments!