A SQL Backup Option Just for Transferring a Database

Here’s an interesting option when copying a database just to transfer it.  Say to bring down a copy of production to troubleshoot or test.  This allows you to backup a database without affecting the regular backup history or restore ability.

Copy-Only Backups (http://msdn.microsoft.com/en-us/library/ms191495(v=sql.105).aspx)

Resetting Your Windows Local Administrator Password

OK, we try to setup our dev/test servers with a well known, common password but sometimes you’ve got an older virtual machine or something setup by someone outside you group who didn’t use it.  And then maybe your having an issue so you decide to remove said machine from the domain and then re-add it.  Only to discover you have no idea what the administrator password was.  Anyway, I’m sure this is posted in a billion other places on the interweb, but one of my colleagues used this method to get us out of just such a pickle and I’d like to remember where to find it next time.

1. Boot onto DVD of Windows Server 2008
2. Choose “Repair your computer”
3. Launch cmd
4. Go to c:\windows\system32
5. Rename Utilman.exe to Utilman.exe.bak
6. Copy cmd.exe to Utilman.exe
7. Reboot on Windows
8. Click the Ease of Access button on lower left corner of login screen (Should open a command prompt)
9. net user administrator Newpass123 inside the command prompt
10. Reboot using the DVD
11. Go to c:\windows\system32
12. Delete Utilman.exe
13. Rename Utilman.exe.bak to Utilman.exe
14. One final reboot and its back online to normal.

Why ISNUMERIC Returns More Than Just Strictly Numeric Strings and What to do Instead

This article (Why doesn’t ISNUMERIC work correctly?) explains why the the SQL ISNUMERIC function returns true for strings with more than just digits and decimal points.  The short answer is that it considers formatted numbers, such as those with currency symbols or commas among others as numeric.

So how do you test for just digits and decimal points?  Use the following comparison:

NOT LIKE '%[^0-9.]%'

This is using a kinda sorta regular expression with the LIKE operator.  '%[^0-9.]%' expression matches the pattern if there is any character that does not match(the ^ character) a digit between 0 and 9 or the decimal point anywhere in the string.  Since this is searching for a pattern that doesn’t match a number you then need to search for values that are NOT LIKE it.

If you want to exclude decimal numbers then just remove the '.' from the character list.

Avoiding NOLOCK

Here’s an interesting article (http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/) on the affects of using the NOLOCK hint in your SQL queries and how even data not being directly affected by an update can be messed up in your query.

I’ve always tried to avoid using it but have to admit to being a bit more liberal about it recently to solve some sticky deadlocking issues.  Those places where it’s being used could happily show bad data and not have any real affects, but it’s easy to see where they could, especially where aggregates are being calculated.  I’ve also not yet made the jump to snapshot isolation as that presents it’s own set of issues but will be looking into it more in the future.

Database Updates without Downtime

I was forwarded in interesting article, Developing Low-Maintenance Databases by Alex Kuznetsov, that talked about good database design and implementation practices.  Many I already adhere to, such as limiting access to the database through stored procedures, views and functions.  However, there was one section in the article that I found particularly intriguing, How to Refactor a Table without Downtime.

In many of the systems I work on we have many components on many servers accessing the same database, coordinating the simultaneous upgrade of those components can be tricky at best.  Although in some cases access can be controlled through a web service and thus limiting upgrade points, performance factors have led us to have many of the components access the database directly.

I definitely intend on looking at incorporating Alex’s pattern into our future upgrade procedures.

COALESCE vs. ISNULL in T-SQL

A colleague of mine forwarded the following article along to me:

Deciding between COALESCE and ISNULL in SQL Server (http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/)

I switched over from ISNULL to COALESCE a couple of years ago and haven’t looked back, primarily to standardize and avoid nested ISNULLs.  This article seems to affirm my thought that there are few disadvantages to COALESCE except in certain unusual circumstances.  It’s worth a quick read.

Tracing WCF Calls

I ran into an issue with one of my WCF services and was having the quite a time trying to track it down, especially since it was failing before it was getting to the actual service.  I came across a nice way of tracing the call all the way through the process.  Add the following section to your web.config file:

<system.diagnostics>
  <sources>
    <source name="System.ServiceModel"
            switchValue="Information, ActivityTracing"
            propagateActivity="true">
      <listeners>
        <add name="traceListener"
            type="System.Diagnostics.XmlWriterTraceListener"
            initializeData= "C:\Logs\Traces.svclog" />
      </listeners>
    </source>
  </sources>
</system.diagnostics>

This will create a trace file that you can open up in the Microsoft Service Trace Viewer.  You can read about the details here.

Quick note – Don’t forget to remove it when you’re done!