SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Physically moving mdf/ldf files to different drive


Physically moving mdf/ldf files to different drive

Author
Message
Angelindiego
Angelindiego
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1354 Visits: 448
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

Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54001 Visits: 17681
Angelindiego (8/7/2012)
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!!

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" ;-)
Angelindiego
Angelindiego
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1354 Visits: 448
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

Angelindiego
Angelindiego
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1354 Visits: 448
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

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94805 Visits: 38957
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)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94805 Visits: 38957
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

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)
Angelindiego
Angelindiego
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1354 Visits: 448
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

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94805 Visits: 38957
Angelindiego (8/7/2012)
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!!


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

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)
Angelindiego
Angelindiego
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1354 Visits: 448
OK Lynn, restored all back to original....and the database opens fine!!


Thank you!!,

Angelindiego

Angelindiego
Angelindiego
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1354 Visits: 448
Gotcha!! Ok, that is next!! Revamp code again, re run and move files.....CHECK!!

Thank you Lynn!!


Thank you!!,

Angelindiego

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search