SQL Server 2005 has officially been released and like many others, I was anxious to install and get rid of the bloated beta so I could start working on the official release version.  The problem I ran into was that I couldn’t upgrade to the release, rather I had to completely uninstall ALL beta versions of SQL and Visual Studio.  For my development machine, this was no problem –I just reformatted my machine.  Our development server where we had the database for our project that the entire team used was a different story altogether.  For this machine, I couldn’t just upgrade nor could I reformat (well, I could’ve but I opted not too because of the work involved).  What I wanted to do was this; keep the .mdf (data files) and .ldf (log files) for all of our databases, without having to back up each one, and simply open them back up once the install was complete.  By doing this, I didn’t have to mess with backups and I knew that all the database users would be there as well.  Further, due to a recent drive space issue where all of our database data and log files were stored on the C: drive rather than the almost empty and rather large E: drive, I wanted to move the files to a directory on the E: drive so that the space issue would no longer be, well, an issue.  So that’s what I set out to do and after a days worth of work, without much documentation online, I finally figured it out… and it was pretty easy.

First and foremost, I backed up the .mdf and .ldf files.  Please, in case anything goes wrong, always have a back up!

Next, I completely uninstalled SQL and Visual Studio betas then reinstalled SQL 2005 release.  FYI, this step-by-step process for uninstalling was very useful.  Even though it says SQL Express, just substitute your installed instance of SQL and everything should work out.  Key to note, you have to basically uninstall SQL then Visual Studio then the framework.

The tricky part I found was with getting SQL to correctly read the data files.  After trying a few methods that clearly didn’t work (I won’t go into detail) and googling for answers, I came up blank and was close to giving up.  But I knew I could do it and quite frankly, I had to do it.  So I began looking at database properties when I noticed something rather intriguing that I never noticed before.  After right clicking on the database to select the properties, one of the selections caught my eye titled “Attach…”.  I found that attaching a database uses sp_detach_db and sp_attach_db system stored procedures and should be used when:

Detaching and attaching databases can be used when you want to move the database files to a different physical disk from the disk that has run out of disk space and you want to expand the existing file rather than add a new file to the database on another disk.

After researching a bit more I found that this in fact an option with SQL 2000 I had just never used it but I could think of many occasions where it would have come in handy.  One of the biggest advantages I found was that all the objects and users came over, no scripting needed just attach and go. 

Side note: Another problem I ran into was trying to restore a SQL 2000 database on a SQL 2005 server; it doesn't want to work.  Attaching the .mdf file is a great work around to this annoyance and does successfully restore the file as a 2005 database.

As with my prior tests, I used a test database so as not to screw up any of the actual data and after seeing this I tried to attach one of my test databases with much success. 

Basically, I did the following (for this example, I’ll use Foo as my database):

Please note, the steps below make the following assumptions:

• SQL 2005 beta or SQL 2000 instances have been removed leaving the data and log files in their default locations.
• SQL 2005 release has been successfully installed.
• The location of the data and log files are being moved to a new location.
• If the location of the files is not an issue, skip ahead to step 3.

Step 1 - Cut Foo.mdf and Foo_Log.ldf for moving to new location.



Step 2 - Paste Foo.mdf and Foo_Log.ldf into E:\SQL Server Data\ (use what ever name and location you’d like)



Step 3 - Back in the SQL Server Management Studio, right click on “Databases” and select “Attach…”



Step 4 - Click “Add” and navigate to where you put your Foo.mdf data file (in my case it would be E:\ SQL Server Data\Foo.mdf)



Finished - Once selected, click “Ok” and your done.

For the handful of database I had to update, I found this to be the easiest and cleanest.  I’m sure some of you have opted or will opt to use back ups then restore them, which is fine and will work; I just didn’t feel the need to go thru all of those unnecessary steps when all I wanted was to move and reattach the original data files.