August 20, 2009 at 12:31 pm
I am trying to change location of server for logs and data. Some one placed everything on the c drive and now I want to move to the e drive. I change server in the database default to:E:\MSSQL\DEV_OLP. Now I need to change each db, logs and data how should I do it, I can't change in options in the database. Thank you
August 20, 2009 at 1:23 pm
You could detach each database, move the mdf/ndf(if any)/ldf files to their new location, then attach the databases.
August 20, 2009 at 1:45 pm
do I need to stop sql server
August 20, 2009 at 2:17 pm
August 20, 2009 at 2:29 pm
As Lynn suggested, this is probably the easiest method. You do not need to stop SQL server but whatever is accessing the application cannot be running. Be sure there are no active threads into the database.
-- You can't be late until you show up.
August 20, 2009 at 2:31 pm
Krasavita
use the following to generate a modify file command for each database
declare @sql nvarchar(max)
set @sql = ''
select 'alter database [' +db.name + '] modify file (name = ' + al.name + ', filename = ''' +
'PUT PATH & FILENAME HERE' + ''')' + char(13) + char(10)
from sysdatabases db inner join sysaltfiles al on db.dbid = al.dbid
where db.dbid > 4
PRINT @sql
paste the output from this into a new query window and substitute PUT PATH & FILENAME HERE for the new path and filename for each database. Once you execute the command for each database take the db offline, move the files to the new location and bring dataabse back online
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 21, 2009 at 6:24 am
Thank you for providing me a code, I still need help.
1. I went to the server property and change database settings for Data and Logs to:E:\MSSQL\DEV_OLP
2. I need to go to the db1 and take ofline
3.Detach db
4.Change location at the options
5.Put back on line
Questions: do I do for system db first or after for my db?
My old location is c:programfiles/microsoft SQL Server/mssql5\Data and new E:\MSSQL\DEV_OLP.
Can some tell me if I am correct on my steps or right me sep be step, this is my first time doing it and I am a littele worry.Thank you
August 21, 2009 at 6:40 am
I would go for the solution Perry Whittle provided.
Use the alter database methode because this will always have you db defined at your sqlinstance, it will also keep all security in place, ...
So ... ultimately.. all you need to do is:
- run the alter statements
- take the db offline (alter database yyy set offliine;) (do not detach them !!!)
- move the files at OS level
- bring the db online.
This move at OS level may take some time, so plan DB downtime !
(If you only move userdatabases, your instance will always be online !)
Keep in mind for system databases you would better stick to the procedure described in BOL !
PS Also keep in mind you have to grant windows folder level auth to the sqlserver service account !
Do do this I always use Xcopy to copy the folder structures and their authority settings.
e.g.
rem /T Creates directory structure, but does not copy files. Does not include empty directories or subdirectories.
rem /T /E includes empty directories and subdirectories.
rem /O Copies file ownership and ACL information.
xcopy e:\MSSQL.1\MSSQL F:\MSSQL.1\MSSQL /E /T /O
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 21, 2009 at 6:53 am
USE master
GO
ALTER DATABASE dbname
MODIFY FILE (NAME = logical_name, FILENAME = ‘new_path\os_file_name’)
GO
What is logical_name mean?
August 21, 2009 at 7:10 am
I have another stuped question, in my directions it says:
Physically move the files and full-text catalogues of the database. Is this mean That I have to do ctrl X from c drive a whole folder and ctl P to my E drive? Thank you
August 21, 2009 at 8:02 am
Krasavita (8/21/2009)
USE masterGO
ALTER DATABASE dbname
MODIFY FILE (NAME = logical_name, FILENAME = ‘new_path\os_file_name’)
GO
What is logical_name mean?
If you excute sp_helpfile in a database you get to see the logical filename and the pfysical filename that is connected to it.
Perry Whittle's reply contains a script that will compose these statements for you !
It will only generate the alter statements ! It will not execute them !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 21, 2009 at 8:10 am
Krasavita (8/21/2009)
I have another stuped question, in my directions it says:Physically move the files and full-text catalogues of the database. Is this mean That I have to do ctrl X from c drive a whole folder and ctl P to my E drive? Thank you
ctrl+X= cut (it will copy the file and remove it from the original location)
ctrl+C = copy (it will only copy the file to the new loction) leaving you an extra file backup! (if you need to fall back !)
ctrf + V = paste (put the file in this new location)
you can also use the dos commands :
copy
or
xcopy
robocopy
I always copy the files, and remove the ones at the original location, if the migration has succeeded.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 21, 2009 at 8:39 am
Ok, I ran this statment
1.declare @sql nvarchar(max)
set @sql = ''
select 'alter database [' +db.name + '] modify file (name = ' + al.name + ', filename = ''' +
'PUT PATH & FILENAME HERE' + ''')' + char(13) + char(10)
from sysdatabases db inner join sysaltfiles al on db.dbid = al.dbid
where db.dbid > 4
PRINT @sql
I got this:
alter database [OLP_CustomData] modify file (name = OLP_CustomData, filename = 'PUT PATH & FILENAME HERE')
alter database [OLP_CustomData] modify file (name = OLP_CustomData_log, filename = 'PUT PATH & FILENAME HERE')
alter database [MSCS_Admin] modify file (name = MSCS_Admin, filename = 'PUT PATH & FILENAME HERE')
alter database [MSCS_Admin] modify file (name = MSCS_Admin_log, filename = 'PUT PATH & FILENAME HERE')
alter database [Janzoon_profiles] modify file (name = Janzoon_profiles, filename = 'PUT PATH & FILENAME HERE')
alter database [Janzoon_profiles] modify file (name = Janzoon_profiles_log, filename = 'PUT PATH & FILENAME HERE')
So I need to paste this to new query window and put new location for data and log, right?
Like this:
alter database [OLP_CustomData] modify file (name = OLP_CustomData, filename = 'E:\MSSQL\DEV_OLP & OLP_CustomData.mdf ')
alter database [OLP_CustomData] modify file (name = OLP_CustomData_log, filename = 'E:\MSSQL\DEV_OLP & OLP_CustomData.ldf')
alter database [MSCS_Admin] modify file (name = MSCS_Admin, filename = 'E:\MSSQL\DEV_OLP & MSCS_Admin.mdf')
alter database [MSCS_Admin] modify file (name = MSCS_Admin_log, filename = 'E:\MSSQL\DEV_OLP & MSCS_Admin.ldf'')
alter database [JF_profiles] modify file (name = Janzoon_profiles, filename = 'E:\MSSQL\DEV_OLP & JF.mdf'')
alter database [JF_profiles] modify file (name = Janzoon_profiles_log, filename = 'PUT PATH & E:\'MSSQL\DEV_OLP & JF_profiles.ldf')
which files exactly do I move? to new drive and can I do all system db and databases together?
August 21, 2009 at 10:03 am
Questions:
1.How to - move the files at OS level?
2.Do you have a link how to move system db? Is master db has a different process?
August 21, 2009 at 10:07 am
Krasavita (8/21/2009)
1.How to - move the files at OS level?
Are you serious? Do you not know how to move a file using window explorer?
2.Do you have a link how to move system db? Is master db has a different process?
Google is your friend. So is Books Online. The full details are in Books online and available on the msdn site.
Edit: "E:\MSSQL\DEV_OLP & MSCS_Admin.ldf" doesn't look like a valid path....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply