asked
need to move tempdb from c drive to g drive
I need to move tempdb currently residing on c drive. I need to move it to g drive. How do I do it (command wise). I have a ticket open and can take outage.
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
Hi there, ALTER DATABASE tempdb MODIFY FILE (name = tempdev, filename = 'G:\NewTempDB\tempdb.mdf') go ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'G:\NewTempDB\templog.ldf') go If you need the logical names for the tempdb files run sp_helpfile You will need to alter where the mdf and ldf files are going. Once this has been completed you will need to restart SQL Server
ALTER DATABASE tempdb MODIFY FILE (name = tempdb, filename = 'G:\NewTempDB\tempdb.mdf') go ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'G:\NewTempDB\templog.ldf') go this is what I am going to run. Wish me luck.
Msg 5041, Level 16, State 1, Line 1 MODIFY FILE failed. File 'tempdb' does not exist. Msg 5121, Level 16, State 1, Line 2 The path specified by "G:\NewTempDB\templog.ldf" is not in a valid directory.
sp_helpfile results are as below: name fileid filename filegroup size maxsize growth usage tempdev 1 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf PRIMARY 107734976 KB Unlimited 10% data only templog 2 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\templog.ldf NULL 2241984 KB Unlimited 10% log only
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started. The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.
Lets start from the beginning. 1. You need to create the G:\NewTempDB\ directory\folder (or whatever name you choose to move the files too). 2. After you created NewTempDB folder, then you need to right click -> properties -> security and add your SQL Service account, set full control permissions for it. 3. Now you can run the alter database command, make sure that the files end up in the created folder in the command. 4. Restart the SQL Service 5. Delete the old tempdb files on the C:\ drive **NOTE** Up to SQL 2008 R2 there is a local computer group named "SQLServerMSSQLUser$YOURSERVERNAME$MSSQLSERVER" (default instance), your SQL Service account is a member of this group. SQL uses this group for folder permissions, so the best choice is to add folder permissions to this group instead of the actual service account. The advantage of using this group is if you change your service account then you won't have to add permissions on the NewTempDB folder again for the new account.
17 People are following this question.
