Physically moving mdf/ldf files to different drive

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Physically moving mdf/ldf files to different...

19 posts, Page 1 of 2

1 2 »»

Physically moving mdf/ldf files to different drive

Rate Topic

Display Mode

Topic Options

Author

Message

Angelindiego

Posted Tuesday, August 07, 2012 3:14 PM

Old Hand

Group: General Forum Members
Last Login: Friday, April 12, 2013 11:22 AM
Points: 312, Visits: 386

Below is the code I am using to move files from C: drive to F: drive:

ALTER DATABASE MyDB 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.
ALTER DATABASE MyDB
MODIFY FILE ( NAME = PrimaryData_log,
FILENAME = 'F:\MSSQL.1\MSSQL\Data\MyDB _Log.ldf');
GO
ALTER DATABASE MyDB
MODIFY FILE ( NAME = PrimaryData,
FILENAME = 'F:\MSSQL.1\MSSQL\Data\MyDB.mdf');
GO

When I query sys.master_files, it shows that the files are now on the F: drive, but errors out in setting the db back online due to it can't find the files. I look on the server and they are not there. This has to be something simple I am missing....but can't see it. HELP PLEASE!!!

My head is telling me that it doesn't know where the existing files are....and isn't moving them. ???

Thank you in advance!!

Thank you!!,

Angelindiego

Post #1341567

Perry Whittle

Posted Tuesday, August 07, 2012 3:21 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:36 AM
Points: 5,201, Visits: 11,153

Angelindiego (8/7/2012)

Execute the modify statements first, take the db offline and then copy the files to the new location(s).
Once the db is online remove the old files.

Have you checked the f drive path has sufficient permissions for the SQL Server service account

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1341569

Angelindiego

Posted Tuesday, August 07, 2012 4:14 PM

Old Hand

Group: General Forum Members
Last Login: Friday, April 12, 2013 11:22 AM
Points: 312, Visits: 386

Thank you for the suggestion. I will switch things up and hit it again. Yes, the F: drive is where all my data files are, it is those renegage files I am trying to move, so that all are in the same place.

Thank you!!,

Angelindiego

Post #1341587

Angelindiego

Posted Tuesday, August 07, 2012 4:25 PM

Old Hand

Group: General Forum Members
Last Login: Friday, April 12, 2013 11:22 AM
Points: 312, Visits: 386

ALTER DATABASE MyDB MODIFY FILE (NAME = PrimaryData, NEWNAME = MyDB );
ALTER DATABASE MyDB MODIFY FILE (NAME = PrimaryData_log, NEWNAME = MyDB _log);
GO

ALTER DATABASE MyDB 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.
ALTER DATABASE MyDB
MODIFY FILE ( NAME = MyDB_log,
FILENAME = 'F:\MSSQL.1\MSSQL\Data\MyDB _Log.ldf');
GO
ALTER DATABASE MyDB
MODIFY FILE ( NAME = MyDB,
FILENAME = 'F:\MSSQL.1\MSSQL\Data\MyDB.mdf');
GO
ALTER DATABASE MyDB 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'MyDB')
--AND type_desc = N'LOG';

OK...above is the revamped code. At the end, where I verify the new location, it says it is moved to the F: drive!! Wussupwifdat!!

I am erroring out all over...says the files don't exist....until the end, and they show up in sys.master_files!

Thank you!!,

Angelindiego

Post #1341592

Lynn Pettis

Posted Tuesday, August 07, 2012 4:29 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 21,602, Visits: 27,431

Dumb question, have you physically copied the mdf/ldf files to the new location after running the t-sql code and taking the database offline?

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1341594

Lynn Pettis

Posted Tuesday, August 07, 2012 4:31 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 21,602, Visits: 27,431

If you undo what you did, in other words run the t-sql to put them back to their original location, does the database open successfully?

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1341595

Angelindiego

Posted Tuesday, August 07, 2012 4:37 PM

Old Hand

Group: General Forum Members
Last Login: Friday, April 12, 2013 11:22 AM
Points: 312, Visits: 386

Lynn....no, I haven't manually moved the files. I was hoping to be able to do it by script, as it has to be done on several servers. I thought that is code would do that work for me.

As to your second question, I have not restored back and opened. I will give it a shot though!!

Thank you!!,

Angelindiego

Post #1341600

Lynn Pettis

Posted Tuesday, August 07, 2012 4:40 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:11 AM
Points: 21,602, Visits: 27,431

Angelindiego (8/7/2012)

After you run your t-sql to move the files, you still have to physically move the files. SQL Server won't move them for you, it just looks for them in the new location.

That is why you need to take the database offline, to move the physical files from location a to location b. Best to copy them just to be safe. Once the database opens with the files in the new location, you can delete them from the old.

Cool

Lynn Pettis