August 7, 2012 at 3:14 pm
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!!
August 7, 2012 at 3:21 pm
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" 😉
August 7, 2012 at 4:14 pm
August 7, 2012 at 4:25 pm
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!
August 7, 2012 at 4:29 pm
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?
August 7, 2012 at 4:31 pm
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?
August 7, 2012 at 4:37 pm
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!!
August 7, 2012 at 4:40 pm
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.
August 7, 2012 at 4:41 pm
August 7, 2012 at 4:51 pm
August 7, 2012 at 11:55 pm
Cough cough!!
Perry Whittle (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.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 8, 2012 at 6:47 am
If you want to automate this, create a series of jobs that set the DB offline and run the MODIFY FILE command int he first step, call a Robocopy script in the 2nd step to copy the file in the second step, and set the DB back online in the last step.
August 8, 2012 at 9:11 am
OK...breaking down the script into sections worked. I modified the name, took DB offline, changed path of the files, moved the files, brought DB back online, checked for proper path in sys.master_files. ALL IS WELL......
Now...I will put some thought into that "robocopy" stuff...see what that is all about.
Thank you all for the help!! I appreciate it so much!
August 8, 2012 at 9:24 am
Perry Whittle (8/7/2012)
Cough cough!!Perry Whittle (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.
Yes, Perry, you did say it as well. I probably should have said something to that affect when I mentioned it again.
August 8, 2012 at 9:43 am
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply