Allen White

SMO making life easier

This was kind of cool. I was answering a question in the MSDN SMO forum about copying database objects from one database to another. I'd had trouble with this myself so I "took on" the question to try and resolve it for myself as well as for the person posing the question.

I have an application for which I need to build a new database for each new website we create, and the schema and some basic data remain the same for each site, so we've got a default database with everything we need to start a new site. I needed a tool to copy the contents of that database to a new database, but wanted to set the size of the new database per anticipated usage.

I showed in a previous post how to create a new database in a specified location with defined sizes. The code I show here allows me to copy an existing database to the newly created database.

        Dim db As Database

        db = srv.Databases("AdventureWorks")
        Dim strDBName As String
        strDBName = "TestDatabase"

        'Define a Transfer object and set the required options and properties.
        Dim xfr As Transfer
        xfr = New Transfer(db)
        xfr.CopyAllObjects = True
        xfr.CopyAllUsers = True
        xfr.Options.WithDependencies = True
        xfr.Options.ContinueScriptingOnError = True
        xfr.DestinationDatabase = strDBName
        xfr.DestinationServer = srv.Name
        xfr.DestinationLoginSecure = True
        xfr.CopySchema = True
        'Script the transfer.
        xfr.TransferData()

I'm now well on the way to finishing my application code to auto-generate new databases when I get a request for a new site.

Allen