Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Physically moving mdf/ldf files to different drive Expand / Collapse
Author
Message
Posted Tuesday, August 7, 2012 3:14 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:53 PM
Points: 320, Visits: 413
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
Posted Tuesday, August 7, 2012 3:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:46 AM
Points: 6,370, Visits: 13,706
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"
Post #1341569
Posted Tuesday, August 7, 2012 4:14 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:53 PM
Points: 320, Visits: 413
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
Posted Tuesday, August 7, 2012 4:25 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:53 PM
Points: 320, Visits: 413
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
Posted Tuesday, August 7, 2012 4:29 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 20,677, Visits: 32,273
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?


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
Posted Tuesday, August 7, 2012 4:31 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 20,677, Visits: 32,273
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?



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 #1341595
Posted Tuesday, August 7, 2012 4:37 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:53 PM
Points: 320, Visits: 413
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
Posted Tuesday, August 7, 2012 4:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 20,677, Visits: 32,273
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.



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 #1341601
Posted Tuesday, August 7, 2012 4:41 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:53 PM
Points: 320, Visits: 413
OK Lynn, restored all back to original....and the database opens fine!!


Thank you!!,

Angelindiego

Post #1341602
Posted Tuesday, August 7, 2012 4:51 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:53 PM
Points: 320, Visits: 413
Gotcha!! Ok, that is next!! Revamp code again, re run and move files.....CHECK!!

Thank you Lynn!!



Thank you!!,

Angelindiego

Post #1341606
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse