Create a database from a stored procedure in an existing database, then access the new database from the old? RRS feed

  • Question

  • I have a stored procedure that creates a backup of a production database with sensitive data mangled so it can be restored into lower environments for development and testing.  The stored procedure exists in the production database to be copied, and the essential steps are:

    1. Make a copy_only backup of the production database
    2. Restore the backup under a temporary name (rolldown)
    3. Alter certain data in the rolldown database so sensitive data does not get copied into the lower environment
    4. Create a backup of the rolldown database
    5. Copy and restore the rolldown backup on another server

    The proc used to work, but I made some changes to it and it no longer works, though it compiles fine.  In the area where the data is updated in the rolldown database I am getting the error "database does not exist", which is true when the proc begins execution, but I don't believe the database ever existed in the past and I never got the error.  So in the lines that are something like "UPDATE rolldown.dbo.user_table SET SSAN = ''...", I get a runtime error saying the rolldown database does not exist without any of the preceding code executing.

    Is the some setting that may have gotten changed that allows for deferred database name resolution?  Is the trick to create an empty database by that name, execute the proc once, then drop the database until the next time the proc has to be recompile?

    Thanks in advance for any advice you can offer.

    Tuesday, December 18, 2012 8:59 PM


  • Can you try

    update user_table set ...

    FROM rolldown.dbo.user_table

    Also, are you sure that the name is rolldown (in lower case)? Just want to exclude possibility of the case-sensitive server.

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    • Marked as answer by Iric Wen Wednesday, December 26, 2012 9:30 AM
    Tuesday, December 18, 2012 9:22 PM