January 2, 2014 at 8:43 am
Hi,
I am trying to moving the database files while it is online, instead of detach and attach method. I have a database test_copy. I have test_copy data & log files in G-Drive. I want to move to C-Drive.
Database logical & File names are
test, test_log and the file name is test_copy.mdf, test_copy_log.ldf
I tried the following code
use test_copy
alter database test_copy
MODIFY FILE ( NAME = test, FILENAME = 'C:\Program Files\Microsoft SQL Server')
alter database test_copy
MODIFY FILE ( NAME = test_log, FILENAME = 'C:\Program Files\Microsoft SQL Server')
The query results are below
The file "test" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "test_log" has been modified in the system catalog. The new path will be used the next time the database is started.
When I refresh the database I didn't see any changes. Even when I restart the server also I didn't see that database files in new location but after the restart of the server when I tried to use the database, it is showing that database is inaccessible.
Please let me know how can we move the database files if the disk is full when the database is in use & if the database is larger database.
Thanks.
January 2, 2014 at 9:13 am
You can't physically move files whilst the db is in use. You can set the db to offline then move the physical files and combine it with the method you've already used. You don't have to detach the db.
Also SQL Server wont move the files for you. You need to do that.
January 2, 2014 at 9:15 am
ramana3327 (1/2/2014)
Please let me know how can we move the database files if the disk is full when the database is in use & if the database is larger database.Thanks.
Just spotted this.
In this situation you can add a new a new file to the filegroup on a different drive and set the old files to not Autogrow.
January 2, 2014 at 11:42 am
Thanks for the quick answer.
January 2, 2014 at 2:38 pm
You also have to specify the full path and name of the file.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
January 2, 2014 at 11:39 pm
ramana3327 (1/2/2014)
Hi,I am trying to moving the database files while it is online, instead of detach and attach method. I have a database test_copy. I have test_copy data & log files in G-Drive. I want to move to C-Drive.
Database logical & File names are
test, test_log and the file name is test_copy.mdf, test_copy_log.ldf
I tried the following code
use test_copy
alter database test_copy
MODIFY FILE ( NAME = test, FILENAME = 'C:\Program Files\Microsoft SQL Server')
alter database test_copy
MODIFY FILE ( NAME = test_log, FILENAME = 'C:\Program Files\Microsoft SQL Server')
The query results are below
The file "test" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "test_log" has been modified in the system catalog. The new path will be used the next time the database is started.
When I refresh the database I didn't see any changes. Even when I restart the server also I didn't see that database files in new location but after the restart of the server when I tried to use the database, it is showing that database is inaccessible.
Please let me know how can we move the database files if the disk is full when the database is in use & if the database is larger database.
Thanks.
Here is what you did: You told SQL Server to use the new paths of the database and log files.
The resulting message after you executed those commands contains the word 'started' which means the database restart which can either be the database restart (by taking it offline and then online) or rebooting the SQL Server completely.
You chose the second option to bounce the entire server which is not a good practise.
What you should do: After running those commands, take the individual database offline( you need to make sure it does not have any active connections and anything running)
Then copy the data and log files to the new desired location and then bring the database online( make sure that the new location is accessible to the service account of SQL Server, if you are running it with local admin access, no problem).
Let us know if you are able to do this successfully.
Thanks
Chandan Jha
January 3, 2014 at 12:25 am
That's a good point. You will need to change the paths back to what they were or next time you restart the db will fail to come online.
January 3, 2014 at 2:25 am
For more on moving database files in SQL Server see my guide at this link[/url], now to the issue at hand
ramana3327 (1/2/2014)
I tried the following codeuse test_copy
alter database test_copy
MODIFY FILE ( NAME = test, FILENAME = 'C:\Program Files\Microsoft SQL Server')
alter database test_copy
MODIFY FILE ( NAME = test_log, FILENAME = 'C:\Program Files\Microsoft SQL Server')
The query results are below
The file "test" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "test_log" has been modified in the system catalog. The new path will be used the next time the database is started.
You've specified the path only, no filename. A word of warning, SQL Server will happily accept whatever rubbish you supply for the FILENAME clause (it doesn't validate it in any way). If that path\filename does not exist next time the database starts, it will be inaccessible!
ramana3327 (1/2/2014)
When I refresh the database I didn't see any changes. Even when I restart the server also I didn't see that database files in new location but after the restart of the server when I tried to use the database, it is showing that database is inaccessible
These are the steps you should perform immediately
use masteralter database [test_copy]
MODIFY FILE ( NAME = test, FILENAME = 'C:\Program Files\Microsoft SQL Server\mydb.mdf')
alter database [test_copy]
MODIFY FILE ( NAME = test_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\mydb.ldf')
alter database [test_copy] SET ONLINE-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 3, 2014 at 7:05 am
Thanks for the reply.
So there is no option to move the database online. Then instead of issuing those commands, we can do the detach & attach method right? Please let me know.
Thanks in advance.
January 3, 2014 at 7:12 am
you can detach by all means.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 3, 2014 at 11:31 am
ramana3327 (1/3/2014)
Thanks for the reply.So there is no option to move the database online. Then instead of issuing those commands, we can do the detach & attach method right? Please let me know.
Thanks in advance.
As Perry sir mentioned, yes you can detach but let me inform you that a couple of years back or may be more, I had read in this forum only that moving database files using alter command is a better approach than detaching it. If I remember correctly ,detaching 'may' cause Orphaned users in the database(can't recall how it can happen) and there were few more drawbacks mentioned which I am not able to recall at the moment.
Also whichever method you choose, if you get any errors, you can easily determine from windows logs(Event Viewer) about what went wrong. Mostly you can get stuck if the files could not get copied on new location or due to security reasons where service account is not able to read from the new location.
Good Luck!!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply