SQL Development and Testing magic, brought to you by NetApp

I started supporting and deploying NetApp storage systems about 5 months ago.  I still am *very* far from being an authority on the subject, but I have been playing around with the kit and am learning an immense amount daily.  That said, I never seize to be amazed at how trivially easy NetApp technology make traditionally time-consuming and difficult tasks.  Take the following situation:  You have a big old production SQL DB, and your developers need to test against an up-to-date instance of it.  I bet the tired old way that your business is doing it is by taking a 500GB backup, pushing it across the network, or sneaker-netting it via USB or even worse restoring from tape (blegh!).

No more.  With a NetApp SAN I can do this in exactly 23 seconds (and this on a low-end FAS2040!).  It costs me zero storage*** on my SAN and all it takes is you putting on your PowerShell hat and typing one single line.  The only assumption I make here is that you are running SnapManager for Microsoft SQL Server on your SQL box (and you should!).  This gives us access to all the PowerShell goodness that makes the magic happen.  The following command (watch for wrappage):  

clone-backup -svr prod-sql-001 -Database big_prod_db -TargetDatabase yourdatabase -TargetServerInstance dev-sql-001 Backup sqlsnap__big_prod_db__snapshots

1. What is happening behind the scenes, you ask?  Quite simple actually:
   
2. It created a FlexClone of the Snapshot of the production store
   
3. Created a DB on dev-sql-001
   
4. Mounted the FlexClone as a mountpoint
   
5. Attached the files to the database
   
6. Made toast and coffee

But that's not all.  This example brought our development box up to the same point in time as production, but you can also specify a previous a point in time to go back to.  Of course you need to have snapshots of your chosen point in time.

Wow.

***It will start consuming space once you start making changes in dev, because you are diverging from the cloned snapshot.

Truncating SQL 2005 Log Files

Had a fun scenario recently where a customer (a Casino!) ran out of disk space on their SQL 2005 Server.  This in turn caused all their slot / video poker / whatever gambling machines to stop working.  Turns out old Bill Shakespeare had it all wrong, because it turns out that Hell Hath No Fury Like A Gambler Scorned!

Long story short - their SQL log file grew to gargantuan proportions, and yours truly had to whip them back into shape.  Here's how it went down!

1. Run the following stored procedure: "use your_db_name"
2. Followed by: "exec sp_helpfile".  This will return the physical names and attributes of files associated with your DB.  Record the DB and log filenames, without the path and extension
3.  Enter the folowwing commands
1. USE your_db_name
2. GO
3. BACKUP LOG your_db_name WITH TRUNCATE_ONLY
4. GO
5. DBCC SHRINKFILE (your_dblog_filename, 1) 
6. GO
7. DBCC SHRINKFILE (your_db_filename, 1)
8. GO
9. exec sp_helpfile

Step 9 should output the same info has Step 2, you can now compare the filesizes to see if the process was succesfull.

You might get an error "Cannot shrink log file because all logical log files are in use".  In that case you can follow the instruction here to resolve.  I've detailed the steps below, if you're too lazy to follow the link.

1. Open SQL Enterprise Manager
2. Right-click on the database you want to shrink and click Properties
3. from the Data Properties go to Options.
4. Set the Recovery Model to Simple and click OK and try to shrink the database

Your Database and Database log files should now shrink succesfully!