How to move tempdb database or logs in MSSQL

Guide To Move Tempdb, Database or Logs Within SQL Server

Moving Tempdb

Because Tempdb is created each time sql server is started we do not need to move the physical files.  The old tempdb will continue to be used until sql server is restarted.  Once change is made restart sql server for the tempdb location change to take effect.  It is recommended tempdb goes on it's own set of disks (preferably raid 10) for the best performance.  However if cost is high then it can share the same disks as the database if the log files can be put on it's own set of disks.  Like most things however, it depends. There are factors to consider such as transactional rate and the amount tempdb is being used for cursors / temporary databases etc.
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '<newpath>\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '<newpath>\templog.ldf');
GO

Moving Binary Logs / Database files

To physically move these files the database will need to be brought offline or start the instance in single user mode.
In SSMS right click the database or log you intend to me and choose detach.
A screen will appear with a status against the database.  If it states "ready" then the database can be detached.  If not then you probably still have connections and can force them off by ticking the drop option to disconnect the users.
Click on OK to complete the detach.  The database should no longer show in SSMS.
You can then copy your logs and database files to the new location using explorer.  Be careful to copy and paste.  It's better practise to delete the old ones afterwards rather than drag and drop or cut/paste.
To reattach, right click on databases in SSMS then select "Attach"
Click the add button then navigate to the database location and select.  Where you see the log file you can then select the "..." box and navigate to the new logfile location
Click ok twice to implement change.  The database and log file locations can now be verified in the database properties.

Comments

Popular posts from this blog

SQL SERVER – Event ID 107- Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.

SQL Server Builds Information

Using DBCA silent install and disabling automatic memory management