Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Importance of Reading Comments

By Manie Verster,

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.

Total article views: 4839 | Views in the last 30 days: 5
 
Related Articles
SCRIPT

Move Physical Files

Script to move physical files to a new location

SCRIPT

Display Names and Locations of SQL Database Files and DB Owners

Displays the names of all of the SQL databases on a SQL Server instance, their data and transaction ...

FORUM

Database Default file Locations

Database Default Locations in Database Setting Under Server Properties

BLOG

Changing physical locations of all DBs

I got a request this week from an application engineer. The request was to move ALL physical databas...

BLOG

SQL Server – Changing Default Database Location for Server

When you create a new database in SQL Server without explicitly specifying database file locations, ...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones