November 2, 2008 at 12:49 pm
Hi,
Iam moving the system databases to from one drive to other.
By default all the databases mdf and ldf are in D drive.Now iam moving olny the ldf files to F drive.
So I moved msdb,model and temp succesfully. So my questions after moving master database ldf file to F drive do I need to folow this procedure:(I want place the resource database mdf n ldf on D drive itself). They saying that we need to move resource database where the master database datafile exist. So in my case master database datafile is On D drive, So the resource database should be on D drive right? or or do I need to move the resource database ldf to F drive?
NET START MSSQLSERVER /f /T3608
NET START MSSQL$instancename /f /T3608
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');
GO
November 2, 2008 at 3:20 pm
Hi,
can we move resource database mdf to one drive and ldf to other drive(resource database mdf to master database mdf location n resource database ldf to master database's ldf location)
or mdf n ldf of resource database should be on the drive where master database datafile is there?
plz clarify me?
November 2, 2008 at 3:34 pm
resource database needs to be where master database is or upgrades will fail.
do not bother to sperate system databases data and log files there is no point, they are not updated enough, keep all those files together (excepting perhaps tempdb)
---------------------------------------------------------------------
November 2, 2008 at 9:40 pm
madhu.arda (11/2/2008)
Hi,can we move resource database mdf to one drive and ldf to other drive(resource database mdf to master database mdf location n resource database ldf to master database's ldf location)
or mdf n ldf of resource database should be on the drive where master database datafile is there?
plz clarify me?
It is not necessary to have both the .mdf and .ldf files on the same path, you can change the path by using the database properties > Options. but for the maintenance of database it is advisable to keep both the files on the same path
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 2, 2008 at 10:56 pm
Do we need to do this:
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');
GO
I remember before when I move system database files, I just move master, msdb, model, but for resource file, I just copy together with other system db files, I didn't use any alter database or detach and attach.. such like this...
And the SQL server is working fine..
is there any impact if I didn't ALTER DATABASE mssqlsystemresource MODIFY FILE ...?
November 2, 2008 at 11:56 pm
You can't alter the system resourse database. It's not visible and can't be modified. In SQL 2005, the files must be in the same location as master.
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
November 3, 2008 at 6:46 pm
This is covered in SQL Server 2005 Books Online (September 2007) under "Moving System Databases" at
http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx
There is an old TechNote out there for SQL 7.0 and 2000 that says it also works for 2005. It uses trace flags and detaches and reattaches the database. This works, but is not the preferred way under 2005. If you do use this method under 2005, make sure you do an EXEC AS LOGIN = SA before doing the attach; if you forget you will be the proud owner of those system databases :Whistling:.It doesn't seem to do any harm (unless you leave), but ownership of system databases can only be changed by detaching and reattaching them as SA.
David Lathrop
DBA
WA Dept of Health
November 3, 2008 at 11:53 pm
It is possible to change the location of the resource database using the command ALTER DATABASE MODIFY FILE. After executing this command, you need to shutdown the SQL Service, move the .mdf & .ldf files manually to the new location, re-start SQL Service.
But please be forewarned that it is best recommended to leave resource database .mdf & .ldf files at the location where the master database .mdf file is location. Reason being the chances of cumulative updates on SQL Server failing is quite high because it won't find the .ldf file as it looks for it only in the location where the master database .mdf file is located.
November 10, 2008 at 5:01 am
Move master and mssqlsystemresource if you wish, but be prepared for future Service Pack and even CU updates to fail. Also don't expect an upgrade in place to the next SQL Server version to work.
Because of the issues people have found when moving these databases, I have to assume there will be an ongoing risk of SQL maintenance failing if they are moved. There is no performance or data integrity gain to be made if the databases are moved, so leave them in their default locations.
So far, I have not seen any issues in moving model, but I definitely believe that watching paint dry is a better use of your time than moving model to a new location. So leave model in the default location.
A lot of installations make extensive use of msdb, and my advice is to treat this in the same way as any user database. If you have separate disks for user mdf and ldf files, use these disks for the msdb mdf and ldf.
tempdb should normally be put on separate drives to your other databases, as this is critical to overall SQL Server performance.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 10, 2008 at 5:28 am
EdVassie (11/10/2008)
There is no performance or data integrity gain to be made if the databases are moved, so leave them in their default locations.
A lot of installations make extensive use of msdb, and my advice is to treat this in the same way as any user database. If you have separate disks for user mdf and ldf files, use these disks for the msdb mdf and ldf.
quote]
Ed.
when you say 'default' location, this could be read as program files on the C drive, so do you mean the 'original' location defined when setup was run. semantics I know but not quite the same thing.
To my mind you would have to be updating msdb a lot to warrant splitting its ldf from the mdf, and if you are it is being used for processes it should not be.................
---------------------------------------------------------------------
November 10, 2008 at 5:34 am
As George pointed out, by 'default' location I mean the original location they are placed in by the SQL Server install.
Regarding msdb, if your installation has a lot of CRUD activity on DTS or SSIS packages or SQL Agent jobs, or anything else that gets stored in msdb, then IMHO you should treat msdb in the same way as a user database and separate the data and log files to different disks. If msdb is near enough empty at your place, there is no need to do this.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 19, 2010 at 2:56 pm
I am in a similar situation to the above discussion... Because of a Hardware configuration change, we must move ALL our SQL data from one drive to another.
the procedures seem almost frighteningly straightforward...
* detach all the databases
* stop the server instance
* (Windows) copy the MSSQL folder from the source to destination drive
* reconfig the server properties to point at the new MSSQL folder
* restart the server instance
* attach the databases
* reconfig the maintenance plans
Am I missing anything?
Thanx!
October 19, 2010 at 11:45 pm
Please post new questions in a new thread.
Thanks
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
November 17, 2017 at 8:59 am
can we move mdf and ldf after the replication set is done ?
November 17, 2017 at 9:02 am
9-year old thread, and master can't be replicated.
Please ask new questions in a new thread.
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 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply