The server has two drives, and the default location for tempdb is c:\mssql7\data, can we change it to somewhere in D drives?
Some one said that he used T-SQL to move it before. While someone told me that it can not be moved. Do you know how?
Thank you!
As the question asked above, you may want to move your database to a new location for better performance. Besides Detach/Attach database, you can simply follow the steps below using ALTER DATABASE MODIFY FILE:
-- Step1: put the database offline
ALTER DATABASE [Products] SET OFFLINE;
GO
-- Step2: move the file to the new location
ALTER DATABASE [Products] MODIFY FILE (NAME = [Products],
FILENAME = 'd:\mssql\data\Products.mdf')
GO
-- Step3: bring the database online
ALTER DATABASE [Products] SET ONLINE
GO