Women Over 60 In Girdles

Moving System databases SQL Server 2005 ...

Hello everyone!

Here is a post that should not leave indifferent those who carried out the laborious movement of all databases of a SQL instance, in particular the bases system!

While the movement of these bases is well documented in MSDN, it is nevertheless extremely tedious to undertake such an operation ... However, there
the significant effort that Microsoft has agreed to streamline the movement between the SQL Server 2000 and SQL Server 2005.

For the record, you will find here documentation for moving files from system databases in SQL Server 2000 and SQL Server 7.
Under SQL Server 2005, only moving databases and Master mssqlsystemresource, new database that did not exist in SQL Server 2000, requires special treatment.

I therefore propose here a customizable script that should delight young and old who want to move their databases SQL Server 2005 system with one click!

To exploit this code, save the file in a Visual Basic Script (eg c: \\ movedb.vbs) and run it!

Function newline (s)
newline = Replace (s, "\\ n", vbCrLf) End Function


Sub order (s)
shcmd.Run "cmd /C " & s,1,true
End Sub

Dim base(4)
base(0) = array("ma","","","","master.mdf","mastlog.ldf")
base(1) = array("rs","mssqlsystemresource","data","log","mssqlsystemresource.mdf","mssqlsystemresource.ldf")
base(2) = array("mo","model","modeldev","modellog","model.mdf","modellog.ldf")
base(3) = array("db","msdb","MSDBData","MSDBLog","MSDBData.mdf","MSDBLog.ldf")
base(4) = array("tp","tempdb","tempdev","templog","tempdb.mdf","templog.ldf")

Function baseray(s)
baseray = array () For Each row in
basis
If row (0) = s Then
baseray
= row End If End Function
Next


'Moves a database other than "master" and "resource"
Sub movenormal (row, s) =
database row (1)
If s = "d" Then
name = row (2) = File
row (4)
ElseIf s = "j" Then
name = row (3 )
file = row (5) End If

order ("sqlcmd-S" & instance & "-Q" ALTER DATABASE "& database &" MODIFY FILE (name = "& name &", FILENAME = '"& dest & "\\" & file & "')""")
command ("net stop" & & serviceinstance "/ yes)
command (" move "& src &" \\ "& file &" "" "" "& Dest &" "" ")
order (" net start "& serviceinstance)


End Sub 'Moves" master "and" resource "
Sub movemaster ()
row = base (0) =
fichierd row (4)
fichierj = row (5)
order (" net stop "& & serviceinstance" / yes)

'search and replace parameters
key = "HKEY_LOCAL_MACHINE \\ SOFTWARE \\ Microsoft \\ Microsoft SQL Server \\ Instance Names \\ SQL \\" & keyinstance
cleinstance = shcmd.RegRead(cle)
cle = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\" & cleinstance & "\MSSQLServer\Parameters"
If s = "d" Then
shcmd.RegWrite cle & "\SQLArg0", "-d" & dest & "\" + fichierd, "REG_SZ"
commande("move """ & src & "\" & fichierd & """ """ & dest & """ ")
Elseif s = "j" Then
shcmd.RegWrite cle & "\SQLArg2", "-l" & dest & "\" + fichierj, "REG_SZ"
commande("move """ & src & "\" + fichierj & """ """ & dest & """ ")
End If
commande("net start " + serviceinstance + " /f /T3608")
row = base(1)
database = row(1)
nomd = row(2)
nomj = row(3)
fichierd = row(4)
fichierj = row(5)
If s = "d" Then
commande("sqlcmd -S " & instance & " -Q ""ALTER DATABASE " & database & " MODIFY FILE (name=" & nomd & ", FILENAME='" & dest & "\" & fichierd & "')""")
commande("move """ & src & "\" & fichierd & """ """ & dest & """ ")
Elseif s = "j" Then
commande("sqlcmd -S " & instance & " -Q ""ALTER DATABASE " & database & " MODIFY FILE (name=" & nomj & ", FILENAME='" & dest & "\" & fichierj & "')""")
commande("move """ & src & "\" & fichierj & """ """ & dest & """ ")
End If
commande("sqlcmd -S " & instance & " -Q ""ALTER DATABASE " & database & " SET READ_ONLY""")
commande("net stop " & serviceinstance & " /yes")
commande("net start " & serviceinstance)
End Sub

set shcmd = WScript.CreateObject ("WScript.Shell")

'Variables to redefine or manually by InputBox:
' SQL Server name and instance bases which are moved
server = InputBox ("Name Server ") If
server =" "Then WScript.Quit
instance = InputBox (" Instance Name (For the default instance, leave it blank) ")
If Replace (body," "," ") =" "Then
instance = server
serviceinstance =" MSSQLSERVER "
keyinstance =" MSSQLSERVER "
Else
instance = server &" \\ "& instance
serviceinstance =" MSSQL $ "& body
keyinstance = body End If


src = InputBox ("Source Path")
dest = InputBox ("Destination Path")

line = "Base (s) to move: \\ nmr: master + resource \\ ndb: MSDBData \\ NMO: model \\ ntp: tempdb \\ n (eg 'mr, tp') "
db = InputBox (newline (line)) line =

" Move (d) ata, (j) ewspapers \\ n or both (d, j) "
dj = InputBox (newline (line))

'line =" Parameters: \\ n source: "+ src +" \\ n-destination: "+ dest +" \\ n-base " db + + "\\ n-objects:" + dj
'msgbox (newline (line)) =

dbpar Split (Replace (db, "",""),",")
djpar = Split (Replace (dj," ",""),",")

For Each db In dbpar
If db <> "mr "Then r =
baseray (db)
If ubound (r)> -1 Then For Each
in djpar
movenormal dj r dj

Next End If Else

movemaster
End If Next



Good day!