Moving system databases

Question

Moving system databases

ramana3327

SSCoach

Points: 19722
More actions
January 5, 2014 at 5:04 pm

#280923

Hi,
I installed my sql server named instance in C-Drive but I want to move that server path to G-Drive. Is there any rule to move system databases. I mean any order we have to follow. I heard that there will be an order to move system databases. Please give me the order
Master--> tempdb--->MSDB--->model (or) Master--> tempdb--->model---->MSDB

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Christian Graus SSC Eights! Points: 963 More actions · Answer 1

Hear where, on what basis ?

http://www.mssqltips.com/sqlservertip/1604/move-all-sql-server-system-databases-at-one-time/ was the best looking of the first page of google hits I got from typing in your question. A quick scan does not indicate that the order matters, but I could be wrong.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Open Books Online, browse (or search) to the page "Moving System Databases"

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 3

be aware your resource database will stay on the C drive and there is nothing you can do about that short of a reinstall.

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

george sibbald (1/6/2014)
be aware your resource database will stay on the C drive and there is nothing you can do about that short of a reinstall.

and it must stay there because on 2008 and above it's treated as if it were a dll, part of the installed binaries.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ramana3327 SSCoach Points: 19722 More actions · Answer 5

ramana3327

SSCoach

Points: 19722

January 6, 2014 at 6:39 am

#1677732

Thank you.

Meet George Jetson SSChampion Points: 11608 More actions · Answer 6

master, tempdb, msdb, model

Chris Powell

George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.

david.alcock SSCertifiable Points: 6021 More actions · Answer 7

I tend to use the following article:

http://technet.microsoft.com/en-us/library/ms345408.aspx

D

'Only he who wanders finds new paths'

ramana3327 SSCoach Points: 19722 More actions · Answer 8

Hi,

Thanks for the replies.

I try to change the file path but mistakenly I gave the wrong path. In the middle I forgot to give one sub folder name. So when I try to restart the server I am not able to start. But I am pretty sure that in the master database I gave the start up parameters right. So I saw error log. Now how can I change the path. I am not able to use alter command through SSMS. Please let me know how cam we do through command prompt.

2014-01-07 12:44:50.92 spid10s Starting up database 'model'.

2014-01-07 12:44:50.93 spid6s Server name is 'VENKATARAMANA-P\CONFIGUREREPORT'. This is an informational message only. No user action is required.

2014-01-07 12:44:50.94 spid10s Error: 17207, Severity: 16, State: 1.

2014-01-07 12:44:50.94 spid10s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'G:\Program Files\Microsoft SQL Server\MSSQL10_50.CONFIGUREREPORT\MSSQL\DATA\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.

2014-01-07 12:44:50.94 spid10s File activation failure. The physical file name "G:\Program Files\Microsoft SQL Server\MSSQL10_50.CONFIGUREREPORT\MSSQL\DATA\modellog.ldf" may be incorrect.

2014-01-07 12:44:50.94 spid10s Error: 945, Severity: 14, State: 2.

2014-01-07 12:44:50.94 spid10s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

2014-01-07 12:44:50.94 spid10s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

2014-01-07 12:44:50.94 spid10s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

george sibbald SSC Guru Points: 104210 More actions · Answer 9

temporary fix - move the modellog.ldf file to where SQL expects it to be so SQL starts, then correct the values in master_files via alter database with care, restart (ensure modellog.ldf also in correct place)

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

http://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass