SQLServerCentral Article

The Importance of Reading Comments

,

I just had to laugh at myself, I was battling to get a job done and could not understand why it wasn't working. Here's what happened.

I needed to move database files (data and log) to a new location due to disk space constraints, so I turned myself to Google and searched: "sql server move database files" and came upon this article that explained nicely what to do. It gave me this script to run and that's where I stopped reading:

USE master;
GO
-- Return the logical file name.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'WeatherData')
AND type_desc = N'ROWS';
GO
ALTER DATABASE WeatherData SET OFFLINE;
GO
ALTER DATABASE WeatherData 
MODIFY FILE ( NAME = WeatherData, 
FILENAME = 'C:\Orisys\WeatherData.mdf');
GO
ALTER DATABASE WeatherData SET ONLINE;
GO
--Verify the new location.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'WeatherData')
AND type_desc = N'ROWS';

I thought to myself heck, this is fantastic, I don't even need to stop the SQL Server service. I went on my merry way and ran the script (fortunately I always test first), but it didn't work. My database would not come online. It said it couldn't find the database files at the location. What now?

I thought maybe I should not take the database offline. Nothing! I could not get it going! I searched again and then found the following script. Still doing the same thing but look at the comments! I will only post the comments here because the script above is working perfectly.

ALTER DATABASE WeatherData SET OFFLINE;
GO
-- Physically move the file to a new location.
-- In the following statement, modify the path specified in FILENAME to
-- the new location of the file on your server.

Note! You have to, after setting the database offline, physically move the database files to the new destination and THEN only run the rest of the script. I thought SQL Server will magically move the physical files for me! The article had correctly told me exactly what to do, I just hadn't read through the comments in the code.

You may laugh now, it was a pretty stupid mistake, but the lesson here is READ. I have the dirty habit to just scan over an article and then expect it to do the job at once. Jeff Moden once talked about writing questions in such a way that those who answer it can figure out what it is you want answered. Well Jeff, I think you will agree with me that READING is just as important! Come and tell us of your embarrasing moments that you experienced in the quiet of your office and nobody knows about it.

Source: The script I got from http://msdn.microsoft.com/en-us/library/ms345483.aspx. The story, however embarrasing, is my own.

Rate

3.36 (25)

You rated this post out of 5. Change rating

Share

Share

Rate

3.36 (25)

You rated this post out of 5. Change rating