Scripting an SQL DB

Lately I've been busy maintaining old systems. The famous .NET 1.1 and alike.
While enjoying a bit of "retro" motion I also saw the giant leaps the new tools did. What impressed me most was the scripting of DB.

Just a few years ago I was in a need for DB structure copy, which would be in the form of a script. The reason for this is simple: automatic creation of the latest DB after the installation without the need for building a new setup script each time an object had changed.
I did the job by building a small utility which ran a "SELECT * FROM sysobjects" with the appropriate flags for tables, keys, procedures and all the other needful stuff.
Lately I needed that ability again, we did some changes to the DB but forgot what they where... So I thought here goes a "deja vue", do a 3 days work again in order to produce a utility like the old one. Luckily for me times changed and so I've found myself ending with a much simpler code:

System.IO.StreamWriter sw = new System.IO.StreamWriter("d:\\diffs.lst");
Microsoft.SqlServer.Management.Smo.Server sServer = new Microsoft.SqlServer.Management.Smo.Server("DBServer1");
Microsoft.SqlServer.Management.Smo.Database sDB = sServer.Databases["DB1"];
Microsoft.SqlServer.Management.Smo.Server dServer = new Microsoft.SqlServer.Management.Smo.Server("DBServer2");
Microsoft.SqlServer.Management.Smo.Database dDB = dServer.Databases["DB2"];
foreach (Microsoft.SqlServer.Management.Smo.Table tbl in sDB.Tables)
{
if (!tbl.IsSystemObject)
{
Console.WriteLine(tbl.Name);
if (dDB.Tables.Contains(tbl.Name))
{
Microsoft.SqlServer.Management.Smo.Table tbl2 = dDB.Tables[tbl.Name];
string scr1 = Decollect(tbl.Script());
string scr2 = Decollect(tbl2.Script());
if (scr1 != scr2)
{
sw.WriteLine(tbl.Name + " - differs");
}
}
else
{
sw.WriteLine(tbl.Name + " - missing");
}
}
else
{
Console.WriteLine("Skipping system table: " + tbl.Name);
}
}
foreach (Microsoft.SqlServer.Management.Smo.StoredProcedure sp in sDB.StoredProcedures)
{
if (!sp.IsSystemObject)
{
Console.WriteLine(sp.Name);
if (dDB.StoredProcedures.Contains(sp.Name))
{
Microsoft.SqlServer.Management.Smo.StoredProcedure sp2 = dDB.StoredProcedures[sp.Name];
string scr1 = Decollect(sp.Script());
string scr2 = Decollect(sp2.Script());
if (scr1 != scr2)
{
sw.WriteLine(sp.Name + " - differs");
}
}
else
{
sw.WriteLine(sp.Name + " - missing");
}
}
else
{
Console.WriteLine("Skipping system proc: " + sp.Name);
}
}

Plus a small helping function to convert the arrays (collections) to strings:

private static string Decollect(System.Collections.Specialized.StringCollection sc)
{
StringBuilder res = new StringBuilder();
foreach (string s in sc)
{
res.Append(s);
}
return res.ToString();
}

Now all I need is something to replace the old backup script:

Option Explicit

Const DB = "DB"
Const User = "sa"
Const Pass = "sa"
Const Server = "(local)"

Call Main()

Sub Main()
If WScript.Arguments.Count=1 Then
Call Restore(WScript.Arguments(0))
Else
Call Backup()
End If
End Sub

Function Restore(BackupFile)
Dim fso
Set fso = CreateObject("scripting.filesystemobject")
If Not fso.FolderExists("D:\backup") Then
fso.CreateFolder("D:\backup")
End If
Dim cmdFile
Set cmdFile = fso.CreateTextFile("c:\backup.sql",True)
cmdFile.WriteLine "DROP DATABASE " & DB
cmdFile.WriteLine "GO"
cmdFile.WriteLine "RESTORE DATABASE " & DB & " FROM DISK = '" & BackupFile & "'"
cmdFile.Close
Set cmdFile = Nothing
Call Exec("osql -U " & User & " -P " & Pass & " -S " & Server & " -i c:\backup.sql -o " & BackupFile & ".log")
Set fso = Nothing
End Function

Function Backup()
Dim BackupFile
BackupFile = "D:\backup\" & DB & DateAsString(Now()) & ".bak"
Dim fso
Set fso = CreateObject("scripting.filesystemobject")
If Not fso.FolderExists("D:\backup") Then
fso.CreateFolder("D:\backup")
End If
Dim cmdFile
Set cmdFile = fso.CreateTextFile("c:\backup.sql",True)
cmdFile.WriteLine "BACKUP DATABASE " & DB & " TO DISK = '" & BackupFile & "'"
cmdFile.Close
Set cmdFile = Nothing
Call Exec("osql -U " & User & " -P " & Pass & " -S " & Server & "-i c:\backup.sql -o " & BackupFile & ".log")
Set fso = Nothing
End function

Function Exec(pCommandLine)
On Error Resume Next
Dim WshShell : Set WshShell = WScript.CreateObject("WScript.Shell")
Exec = WshShell.Run(pCommandLine,,True)
If Err.number <> 0 Then
Exec = 255
Err.clear
End If
End Function

function DateAsString(ReqDate)
dim res
res = ""
res = res & year(ReqDate)
res = res & month(ReqDate)
res = res & day(ReqDate)
res = res & hour(ReqDate)
res = res & minute(ReqDate)
res = res & second(ReqDate)
DateAsString = res
end function