· 8 years ago · Dec 14, 2017, 02:10 PM
1#load SMO
2Add-PSSnapin SqlServerCmdletSnapin100
3Add-PSSnapin SqlServerProviderSnapin100
4#Added line if using SQL Server 2012 or later
5Import-module SQLPS
6[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
7
8#Create server object and output filename
9$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server "localhost"
10$outputfile=([Environment]::GetFolderPath("MyDocuments"))+"FileMover.ps1"
11
12#set this for your new location
13$newloc="X:NewDBLocation"
14
15#get your databases
16$db_list=$server.Databases
17
18#build initial script components
19"Add-PSSnapin SqlServerCmdletSnapin100" > $outputfile
20"Add-PSSnapin SqlServerProviderSnapin100" >> $outputfile
21"Import-Module SQLPS" >> $outputfile
22"[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') `"localhost`" | out-null" >> $outputfile
23"`$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server " >> $outputfile
24
25foreach($db_build in $db_list)
26{
27 #only process user databases
28 if(!($db_build.IsSystemObject))
29 {
30 #script out all the file moves
31 "#----------------------------------------------------------------------" >> $outputfile
32 "`$db=`$server.Databases[`""+$db_build.Name+"`"]" >> $outputfile
33
34 $dbchange = @()
35 $robocpy =@()
36 foreach ($fg in $db_build.Filegroups)
37 {
38 foreach($file in $fg.Files)
39 {
40 $shortfile=$file.Filename.Substring($file.Filename.LastIndexOf('')+1)
41 $oldloc=$file.Filename.Substring(0,$file.Filename.LastIndexOf(''))
42 $dbchange+="`$db.FileGroups[`""+$fg.Name+"`"].Files[`""+$file.Name+"`"].Filename=`"$newloc`"+$shortfile+"`""
43 $robocpy+="ROBOCOPY `"$oldloc`" `"$newloc`" $shortfile /copyall /mov"
44
45 }
46 }
47
48 foreach($logfile in $db_build.LogFiles)
49 {
50 $shortfile=$logfile.Filename.Substring($logfile.Filename.LastIndexOf('')+1)
51 $oldloc=$logfile.Filename.Substring(0,$logfile.Filename.LastIndexOf(''))
52 $dbchange+="`$db.LogFiles[`""+$logfile.Name+"`"].Filename=`"$newloc`"+$shortfile+"`""
53 $robocpy+="ROBOCOPY `"$oldloc`" `"$newloc`" $shortfile /copyall /mov"
54 }
55
56 $dbchange+="`$db.Alter()"
57 $dbchange+="Invoke-Sqlcmd -Query `"ALTER DATABASE ["+$db_build.Name+"] SET OFFLINE WITH ROLLBACK IMMEDIATE;`" -Database `"master`""
58
59 $dbchange >> $outputfile
60 $robocpy >> $outputfile
61
62 "Invoke-Sqlcmd -Query `"ALTER DATABASE ["+$db_build.Name+"] SET ONLINE;`" -Database `"master`"" >> $outputfile
63 }
64}
65
66Add-PSSnapin SqlServerCmdletSnapin100
67 Add-PSSnapin SqlServerProviderSnapin100
68 Import-Module SQLPS
69 [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') "localhost" | out-null
70 $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
71 #----------------------------------------------------------------------
72 $db=$server.Databases["AdventureWorks2012"]
73 $db.FileGroups["PRIMARY"].Files["AdventureWorks2012_Data"].Filename="X:NewDBLocationAdventureWorks2012_Data.mdf"
74 $db.LogFiles["AdventureWorks2012_Log"].Filename="X:NewDBLocationAdventureWorks2012_log.ldf"
75 $db.Alter()
76 Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012] SET OFFLINE WITH ROLLBACK IMMEDIATE;" -Database "master"
77 ROBOCOPY "C:DBData" "X:NewDBLocation" AdventureWorks2012_Data.mdf /copyall /mov
78 ROBOCOPY "C:DBFilesLog" "X:NewDBLocation" AdventureWorks2012_log.ldf /copyall /mov
79 Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012] SET ONLINE;" -Database "master"
80 #----------------------------------------------------------------------
81 $db=$server.Databases["AdventureWorks2012DW"]
82 $db.FileGroups["PRIMARY"].Files["AdventureWorksDW2012_Data"].Filename="X:NewDBLocationAdventureWorksDW2012_Data.mdf"
83 $db.LogFiles["AdventureWorksDW2012_Log"].Filename="X:NewDBLocationAdventureWorks2012DW_log.ldf"
84 $db.Alter()
85 Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012DW] SET OFFLINE WITH ROLLBACK IMMEDIATE;" -Database "master"
86 ROBOCOPY "C:DBData" "X:NewDBLocation" AdventureWorksDW2012_Data.mdf /copyall /mov
87 ROBOCOPY "C:DBData" "X:NewDBLocation" AdventureWorks2012DW_log.ldf /copyall /mov
88 Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012DW] SET ONLINE;" -Database "master"
89
90...
91
92SET NOCOUNT ON
93
94DECLARE @datafile VARCHAR(255)
95 ,@logfile VARCHAR(255)
96 ,@dbid TINYINT
97 ,@SQLText VARCHAR(max)
98 ,@dbname VARCHAR(255)
99 ,@sqltext1 VARCHAR(max)
100 ,@SQLText2 VARCHAR(max)
101
102--2. Prepare for modify
103IF EXISTS (
104 SELECT 1
105 FROM tempdb..sysobjects
106 WHERE NAME LIKE '%#filetable%'
107 )
108BEGIN
109 DROP TABLE #filetable
110END
111
112CREATE TABLE #filetable (
113 mdf VARCHAR(255)
114 ,ldf VARCHAR(255)
115 ,dbid TINYINT
116 ,dbname VARCHAR(100)
117 ,fileid TINYINT
118 ,logicalname SYSNAME
119 )
120
121--
122INSERT #filetable (
123 mdf
124 ,dbid
125 ,fileid
126 ,logicalname
127 )
128SELECT physical_name
129 ,database_id
130 ,data_space_id
131 ,NAME
132FROM sys.master_files
133WHERE data_space_id = 1
134
135INSERT #filetable (
136 ldf
137 ,dbid
138 ,fileid
139 ,logicalname
140 )
141SELECT physical_name
142 ,database_id
143 ,data_space_id
144 ,NAME
145FROM sys.master_files
146WHERE data_space_id = 0
147
148UPDATE u
149SET u.dbname = s.NAME
150FROM #filetable u
151INNER JOIN master..sysdatabases s ON u.dbid = s.dbid
152
153UPDATE #filetable
154SET mdf = replace(mdf, 'C:', 'D:')
155 ,ldf = replace(ldf, 'C:', 'D:')
156FROM #filetable
157
158SELECT @dbid = min(dbid)
159FROM #filetable
160WHERE dbid > 4
161
162WHILE @dbid IS NOT NULL
163BEGIN
164 SELECT @SQLText = 'alter database [' + dbname + '] MODIFY FILE (Name = ' + logicalname + ' , FileName = N''' + ldf + ''');'
165 FROM #filetable
166 WHERE dbid = convert(VARCHAR, @dbid)
167 AND fileid = 0 -- Log file
168
169 PRINT @SQLText
170
171 --Exec(@SQLText)
172 SELECT @SQLText2 = 'alter database [' + dbname + '] MODIFY FILE (Name = ' + logicalname + ' , FileName = N''' + mdf + ''');'
173 FROM #filetable
174 WHERE dbid = convert(VARCHAR, @dbid)
175 AND fileid = 1 -- data file
176
177 PRINT @SQLText2
178
179 --Exec(@SQLText)
180 SELECT @dbid = min(dbid)
181 FROM #filetable
182 WHERE dbid > 4
183 AND dbid > @dbid
184END
185
186DECLARE @datafile VARCHAR(255)
187 ,@logfile VARCHAR(255)
188 ,@dbid TINYINT
189 ,@SQLText VARCHAR(8000)
190 ,@dbname VARCHAR(255)
191 ,@SQLText2 VARCHAR(8000)
192
193--2. Detach All Local Databases and prepare for Attach
194IF EXISTS (
195 SELECT 1
196 FROM tempdb..sysobjects
197 WHERE NAME LIKE '%#filetable%'
198 )
199BEGIN
200 DROP TABLE #filetable
201END
202
203CREATE TABLE #filetable (
204 mdf VARCHAR(255)
205 ,ldf VARCHAR(255)
206 ,dbid TINYINT
207 ,dbname VARCHAR(100)
208 ,fileid TINYINT
209 )
210
211--
212INSERT #filetable (
213 mdf
214 ,dbid
215 ,fileid
216 )
217SELECT physical_name
218 ,database_id
219 ,data_space_id
220FROM sys.master_files
221WHERE data_space_id = 1
222
223INSERT #filetable (
224 ldf
225 ,dbid
226 ,fileid
227 )
228SELECT physical_name
229 ,database_id
230 ,data_space_id
231FROM sys.master_files
232WHERE data_space_id = 0
233
234UPDATE u
235SET u.dbname = s.NAME
236FROM #filetable u
237INNER JOIN master..sysdatabases s ON u.dbid = s.dbid
238
239UPDATE #filetable
240SET mdf = replace(mdf, 'C:', 'D:')
241 ,ldf = replace(ldf, 'C:', 'D:')
242FROM #filetable
243
244SELECT @dbid = min(dbid)
245FROM #filetable
246WHERE dbid > 4
247
248WHILE @dbid IS NOT NULL
249BEGIN
250 SELECT @SQLText = 'alter database [' + dbname + ']'
251 FROM #filetable
252 WHERE dbid = convert(VARCHAR, @dbid)
253
254 SELECT @SQLText = @SQLText + CHAR(10) + ' set single_user with rollback immediate;'
255
256 SELECT @SQLText = @SQLText + CHAR(10) + ' exec master..sp_detach_db ' + dbname
257 FROM #filetable
258 WHERE dbid = convert(VARCHAR, @dbid)
259
260 PRINT @SQLText
261
262 --Exec(@SQLText)
263 SELECT @SQLText2 = 'exec master..sp_attach_db ''' + dbname + ''''
264 FROM #filetable
265 WHERE dbid = @dbid
266
267 SELECT @SQLText2 = @SQLText2 + ',''' + mdf + ''''
268 FROM #filetable
269 WHERE dbid = @dbid
270 AND mdf IS NOT NULL
271
272 SELECT @SQLText2 = @SQLText2 + ',''' + ldf + ''''
273 FROM #filetable
274 WHERE dbid = @dbid
275 AND ldf IS NOT NULL
276
277 PRINT @SQLText2
278
279 --Exec(@SQLText)
280 SELECT @dbid = min(dbid)
281 FROM #filetable
282 WHERE dbid > 4
283 AND dbid > @dbid
284END
285
286DROP TABLE #filetable
287
288ALTER DATABASE database_nameA SET OFFLINE WITH ROLLBACK IMMEDIATE;
289ALTER DATABASE database_nameB SET OFFLINE WITH ROLLBACK IMMEDIATE;
290ALTER DATABASE database_nameC SET OFFLINE WITH ROLLBACK IMMEDIATE;
291-------
292
293-------
294ALTER DATABASE database_nameA MODIFY FILE ( NAME = logical_name, FILENAME = 'new_pathos_file_name' );
295ALTER DATABASE database_nameB MODIFY FILE ( NAME = logical_name, FILENAME = 'new_pathos_file_name' );
296ALTER DATABASE database_nameC MODIFY FILE ( NAME = logical_name, FILENAME = 'new_pathos_file_name' );
297
298ALTER DATABASE database_nameA SET ONLINE;
299ALTER DATABASE database_nameB SET ONLINE;
300ALTER DATABASE database_nameC SET ONLINE;
301
302------------------------------
303--erezbensimon@gmail.com - July 2016
304
305
306use master;
307go
308
309SET NOCOUNT ON
310
311print '----------------------------------------------------------------------------------'
312print '--Script for Moving Multiple database files to a new drive / ' + CONVERT(varchar(256),getdate() )
313print '----------------------------------------------------------------------------------'
314print ''
315
316
317DECLARE @dbname nvarchar(128)
318DECLARE @DestPath nvarchar(256)
319
320
321--Set here the new destination path of the file
322set @DestPath = 'T:Data'
323
324------------------------------------------------
325--Filter: HD Databases
326------------------------------------------------
327DECLARE DBList_cursor CURSOR FOR
328
329Select name from sys.databases
330
331--where name like '<FIlter Something>'
332
333----------------------------------------------
334
335OPEN DBList_cursor
336
337FETCH NEXT FROM DBList_cursor
338 INTO @dbname
339
340WHILE @@FETCH_STATUS = 0
341BEGIN
342
343 declare @output_script varchar(max) --Output of the generated script
344 declare @mdf_orig_path nvarchar(256) --Original datbase file path
345 declare @cmdstring nvarchar(256) --Command String
346 declare @CursorDeclare varchar(max) --Cursor declaration command
347 declare @Originalfilename varchar(max) -- local @CursorDeclare command
348 declare @filename varchar(max) -- local @CursorDeclare command
349 declare @LogicalFileaame varchar(max) -- Logical FileName
350
351 --Set null into @output script
352 set @output_script=''
353 --Generate Databse Cursor declaration command
354 set @CursorDeclare='DECLARE DBFiles_cursor CURSOR FOR select [filename], [name] from '+ @dbname + '.sys.sysfiles'
355 --Cursor Declaration
356 execute (@CursorDeclare)
357
358
359 OPEN DBFiles_cursor
360 FETCH NEXT FROM DBFiles_cursor INTO @filename, @LogicalFileaame
361
362 --For RollBack Option
363 select @Originalfilename = @filename
364
365 --Modify Physical FileName
366 if (@filename like '%.mdf') begin
367 select @mdf_orig_path = @filename
368 IF(CHARINDEX('', @filename) > 0)
369 select @filename = RIGHT(@filename, CHARINDEX('', REVERSE(@filename)) -1)
370
371 select @filename = @DestPath + @filename
372 select @cmdstring = ' ''copy' + ' ' + '"'+ @mdf_orig_path + '"' + ' ' + '"' + @filename +'"' + ''''
373 --Get Logical FileNAme
374
375
376 end
377
378
379
380
381print CHAR(10)
382print '-----------------------------------------'
383print @dbname
384print '-----------------------------------------'
385print CHAR(10)
386print 'print ''Start'' + CONVERT(varchar(256), getdate() ) '
387print '---Offline Database' + @dbname
388print 'ALTER DATABASE ' + @dbname + ' SET OFFLINE WITH ROLLBACK IMMEDIATE' + CHAR(10) + 'GO'
389print 'exec master..xp_cmdshell' + ' ' + @cmdstring + CHAR(10)
390print '--For RollBack Use this:ALTER DATABASE ' + @dbname +' MODIFY FILE ( NAME =' + @LogicalFileaame +', FILENAME =' + @Originalfilename + ')' + CHAR(10)
391print 'ALTER DATABASE ' + @dbname +' MODIFY FILE ( NAME =' + @LogicalFileaame +', FILENAME =' + '''' + @DestPath + @dbname + '.mdf'' )' +CHAR(10)
392
393
394print '---ONline Database' + @dbname
395print 'ALTER DATABASE ' + @dbname + ' SET ONLINE WITH
396ROLLBACK IMMEDIATE
397GO'
398
399 WHILE @@FETCH_STATUS = 0
400 BEGIN
401 set @output_script=@output_script+' (FILENAME = '''+ @filename +'''),'
402 FETCH NEXT FROM DBFiles_cursor INTO @filename, @LogicalFileaame
403 END
404
405set @output_script=SUBSTRING(@output_script,0,len(@output_script))
406
407CLOSE DBFiles_cursor
408DEALLOCATE DBFiles_cursor
409
410FETCH NEXT FROM DBList_cursor
411INTO @dbname
412
413END
414
415
416CLOSE DBList_cursor
417DEALLOCATE DBList_cursor