Keeping db structure and procs in sync

Question

I am trying to setup a test database for automated tests to run against. I know ideally we should be using mock objects, but this is an old app and doing so isn't as easy as it should be. There's simply too much logic in the database.

Anyhow, enough backstory. I'm getting a clean db setup that the tests can load with known data so that results are thus known as well.

At the start of the test, a .sql script will be run to empty the tables and then fill them with test data. The problem I see, is not so much keeping the database structures in sync, as they don't change much (though that would be nice too) but to move all the procs/functions etc across from dev into test at the start of the test.

Is there SQL commands that can be executed to do this? To read a proc from the dev db, and copy it to the test db?

EDIT: I've actually found that using SQL Management Objects works pretty good.

The only problem is that if someone renamed an item, internal references aren't updated by sql server, and it's created under the old name. Is there a way to fix this?

Answer 1

You said that keeping "database structures" in synch wasn't a problem but then you go on to say you needed to move procs/functions from dev to test. These are part of the database structure! IAC, I would suggest a tool like Red-Gate's SQL Compare. It will move any database structure that exists on dev that doesn't exist on test. Very nice tool. I've used it for years.

Answer 2

Chad, I'm not sure how automated your looking to get with this. If you wanted to script all the procs at once you can do it from SQL enterprise manager.

Before each test script all the procs from Dev for CREATE. Drop your procs in TEST and run the script from the dev file. That will make sure your procs are in sync.

Answer 3

Here's a quick-and-dirty SQL script you can use to copy stored procedures from one database to another:

USE DestinationDatabase

GO

DECLARE
    @SchemaName sysname,
    @ProcName nvarchar(4000),
    @ProcDefinition nvarchar(max),
    @DropSql nvarchar(4000)

SET @SchemaName = 'dbo'

DECLARE crProcedures CURSOR FAST_FORWARD FOR
    SELECT r.ROUTINE_NAME, r.ROUTINE_DEFINITION
    FROM SourceDatabase.INFORMATION_SCHEMA.Routines r
    WHERE ROUTINE_SCHEMA = @SchemaName
    AND ROUTINE_NAME IN ('Procedure1', 'Procedure2', 'Procedure3')
OPEN crProcedures
FETCH NEXT FROM crProcedures INTO @ProcName, @ProcDefinition
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @DropSql = 'IF  EXISTS ' +
        '(SELECT * FROM sys.objects ' +
        'WHERE object_id = OBJECT_ID(N''' + @ProcName + ''') ' +
        'AND type in (N''P'', N''PC'')) ' +
        'DROP PROCEDURE ' + @ProcName
    EXEC sp_executesql @DropSql
    EXEC sp_executesql @ProcDefinition

    FETCH NEXT FROM crProcedures INTO @ProcName, @ProcDefinition
END
CLOSE crProcedures
DEALLOCATE crProcedures

Just make sure you don't confuse the SourceDatabase and DestinationDatabase, otherwise you'll be in trouble!

Answer 4

With SMO, the following works great! And copies in ~40 seconds.

    private static void CopyDBSchema(string sourceDBServer, string sourceDBName, string targetDBServer, string targetDBName)
    {
        Server server = new Server(sourceDBServer);
        server.SetDefaultInitFields(true);

        Database db = server.Databases[sourceDBName];
        Transfer t = new Transfer()
        {
            Database = db,

            CopyData = false,
            CopyAllObjects = false,
            CopyAllUsers = false,
            CopyAllLogins = false,

            CopySchema = true,
            CopyAllTables = true,
            CopyAllViews = true,
            CopyAllStoredProcedures = true,
            CopyAllDatabaseTriggers = true,
            CopyAllUserDefinedAggregates = true,
            CopyAllUserDefinedDataTypes = true,
            CopyAllUserDefinedFunctions = true,
            CopyAllUserDefinedTypes = true,

            DropDestinationObjectsFirst = true,
            UseDestinationTransaction = true,
            DestinationServer = targetDBServer,
            DestinationDatabase = targetDBName
        };

        t.Options.WithDependencies = true;
        t.Options.IncludeIfNotExists = true;
        t.Options.DriPrimaryKey = true;
        t.Options.DriForeignKeys = true;
        t.Options.DriIndexes = true;
        t.Options.DriNonClustered = true;
        t.Options.DriUniqueKeys = true;
        t.Options.DriAllKeys = true;
        t.Options.Triggers = true;

        t.TransferData();
        server = null;
    }