October 6, 2010 at 11:10 am
what is the alter tempdb command to move the tempd.mdf file from D:\MSSQL2005\MSSQL$Instance\MSSQL.1\MSSQL\Data to the H:\SQL directory
October 6, 2010 at 11:40 am
Assuming tempdb logical names are the defaults (which they likely are):
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'H:\SQL\tempdb.mdf'); -- as in 'c:\tempdb.mdf'
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'H:\SQL\templog.ldf'); -- as in 'c:\templog.ldf'
After this you'll have to stop and restart your SQL instance.
Rob Schripsema
Propack, Inc.
October 6, 2010 at 11:48 am
Then the tempdb.mdf file in original D:\ drive can be deleted?
October 6, 2010 at 11:59 am
Yes, after you're restarted the server instance. You'll know it is no longer in use when the OS allows you to delete it; as long as it's attached by SQL Server, you won't be able to.
Rob Schripsema
Propack, Inc.
May 17, 2012 at 12:49 am
Thanks for you reply.. but after executing this alter statement...
can we do to move .mdf,.ldf to other location? or not ?
May 17, 2012 at 12:55 am
You can move the files, after the service has been stopped but there is no need as new ones are created
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply