SQL-Server-Tools/move_tempdb_files.sql at master · rich-howell/SQL-Server-Tools · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
--Find the location of the files currently

SELECT mf.name [File Name],
       mf.physical_name [Physical Name],
       d.name AS [Database Name]
FROM sys.master_Files mf
     INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.name = 'tempdb';

--Move this for all files for the database, mdf, ldf & ndf files

USE master; --do this all from the master
ALTER DATABASE [tempdb]
MODIFY FILE
(
	name='tempdev',
    filename='D:\tempdb.mdf'
); --Filename is new location

USE master; --do this all from the master
ALTER DATABASE [tempdb]
MODIFY FILE
(
	name='temp2',
    filename='D:\tempdb_mssql_2.ndf'
); --Filename is new location

USE master; --do this all from the master
ALTER DATABASE [tempdb]
MODIFY FILE
(
	name='temp3',
    filename='D:\tempdb_mssql_3.ndf'
); --Filename is new location

USE master; --do this all from the master
ALTER DATABASE [tempdb]
MODIFY FILE
(
	name='temp4',
    filename='D:\tempdb_mssql_4.ndf'
); --Filename is new location

USE master; --do this all from the master
ALTER DATABASE [tempdb]
MODIFY FILE
(
	name='temp5',
    filename='D:\tempdb_mssql_5.ndf'
); --Filename is new location

USE master; --do this all from the master
ALTER DATABASE [tempdb]
MODIFY FILE
(
	name='temp6',
    filename='D:\tempdb_mssql_6.ndf'
); --Filename is new location

USE master; --do this all from the master
ALTER DATABASE [tempdb]
MODIFY FILE
(
	name='temp7',
    filename='D:\tempdb_mssql_7.ndf'
); --Filename is new location

USE master; --do this all from the master
ALTER DATABASE [tempdb]
MODIFY FILE
(
	name='temp8',
    filename='D:\tempdb_mssql_8.ndf'
); --Filename is new location


USE master; --do this all from the master
ALTER DATABASE [tempdb]
MODIFY FILE
(
	name='templog',
    filename='L:\templog_log.ldf'
); --Filename is new location