I had to move an extranet application, including the databases, to a new server. The application has just over 300 sites, so of course I had to write code! There is a lot of file copies, but the script does include one interesting piece: creating a database on a server and creating an empty database.

The solution was written in VBScript and runs on a Windows XP machine. The script uses SQL-DMO to perform the database admin steps. I am including the script below as an example.

Now, I am sure that many of you will have an opinion about the code. That is fine. But do not flame about database best practices -- I am simply moving an existing application with specific requirements. And do not flame about the hungarian notation -- VB Script does not have strong typing and the entire script is almost 400 lines. Feel free to flame anything else.

Sub CreateDB(DBServer, SiteName)
    Dim oDBDataFile, sDataPath, oDBLogFile, sLogPath
    Dim oSvr, oDB

    Set oSvr = CreateObject("SQLDMO.SQLServer")
    oSvr.Connect DBServer, "user", "password" ' Could use WinNT login

    Set oDB = CreateObject("SQLDMO.Database")
    Set oDBDataFile = CreateObject("SQLDMO.DBFile")
    Set oDBLogFile = CreateObject("SQLDMO.LogFile")

    'Set new database name
    oDB.Name = SiteName

    'Define the PRIMARY data file.
    oDBDataFile.Name = SiteName
    sDataPath = oSvr.Registry.SQLDataRoot & "\DATA\" & SiteName & ".mdf"
    oDBDataFile.PhysicalName = sDataPath
    oDBDataFile.PrimaryFile = True
    oDBDataFile.Size = 2 'Set initial size (optional)

    oDBDataFile.FileGrowthType = SQLDMOGrowth_MB 'fixed size - other options available
    oDBDataFile.FileGrowth = 1

    'Add the DBFile object
    oDB.FileGroups("PRIMARY").DBFiles.Add oDBDataFile

    'Define the database transaction log.
    oDBLogFile.Name = SiteName & "Log"
    sLogPath = oSvr.Registry.SQLDataRoot & "\DATA\" & oDBLogFile.Name & ".ldf"
    oDBLogFile.PhysicalName = sLogPath
    oDBLogFile.Size = 2
    oDB.TransactionLog.LogFiles.Add oDBLogFile

    'Create the database as defined.
    oSvr.Databases.Add oDB
    
    'Add a user to the database
    Set oUser = CreateObject("SQLDMO.User")
    oUser.Login = "ExNet"
    oUser.Role = "db_owner" ' Make sure this is appropriate for you!
    oDB.Users.Add(oUser)
    

    ' Now, build the schema from the file of SQL statements
    Const ForReading = 1, ForWriting = 2
    Dim oFSO, oFile, sFile, sCmdBatch
    
    SET oFSO = CreateObject("Scripting.FileSystemObject")

    sFile = "C:\Visual Studio Projects\ExNetMove\CreateDatabase2_05.sql"
    SET oFile = oFSO.OpenTextFile(sFile, ForReading)

    sCmdBatch = oFile.ReadAll
    
    oDB.ExecuteImmediate sCmdBatch, SQLDMOExec_ContinueOnError    

    oFile.Close

End Sub