October 27, 2008
@ 06:34 PM
Comments [0]
Moving SQL Databases to a New Drive

I run across this from time to time and can never remember the specifics, so I figured I'd document it here:

To move a SQL database from one drive to another drive (or just move it to a new folder):

Inside of Microsoft SQL Management Studio execute this script (of course, replace "NameOfDatabase" with the real database name):

  USE master
  GO
  sp_detach_db 'NameOfDatabase'
  GO

Copy the database (usually NameOfDatabase.MDF and NameOfDatabase_log.LDF, assuming they haven't been renamed) to the new location.

  NOTE: The paths are relative to the server SQL Server is running on.

  USE master
  GO
  sp_attach_db 'NameOfDatabase', 'E:\SQLServerDataFolder\NameOfDatabase.mdf', 'E:\SQLServerDataFolder\NameOfDatabase_log.ldf'
  GO

To move the TempDB system database to a new location:

USE master
  GO
  ALTER DATABASE tempdb modify file (name = tempdev, filename = 'E:\SQLServerDataFolder\tempdb.mdf')
  GO
  ALTER DATABASE tempdb modify file (name = templog, filename = 'E:\SQLServerDataFolder\templog.mdf')

Restart SQL Server for the changes to take effect. One it's restarted you can delete the tempdb.mdf and templog.mdf from the old location.

Some instructions for shrinking the tempdb database:

http://support.microsoft.com/kb/307487


 
Categories: SQL

« VFP Intellisense | Home | Moving TempDB, a Cautionary Tale »
Name
E-mail
(will show your gravatar icon)
Home page

Comment (Some html is allowed: a@href@title, b, i, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

Enter the code shown (prevents robots):
Live Comment Preview