• rightontarget (12/4/2013)


    Hi all,

    I was asked to create a test environment for one of our apps that is supported by 3 databases.

    I can restore latest backup of production version of each, but then I will have to edit every procedure to make sure that joins point to test instance of the database.

    I can create database script and edit it in text editor.

    I am looking for an optimal solution as number of stored procedures is growing and manual edit will become problematic with time.

    Thanks,

    I might be missing something here. You say you have to create a test environment with 3 databases, but then mention editing the instance referenced. Are you specifying the instance in your procedures? If not, then you shouldn't have to edit anything because the procedures will run in their own databases within their own instance. I prefer using the two-part naming convention (schema.object) and the three-part convention (db.schema.object) only where necessary. I would not want to rely on manually editing the procedures since you're going to miss something eventually, especially as the amount of code grows.

    As for how to copy the databases from production to test, I use the approach of backing up production, copying the backup files to the test server, then restoring the databases from the backup files into test. The only caveat here is that if you're using a different server then you'll have to check your SQL logins to make sure the SIDs match. If they don't match, you have to rebuild them because the SQL logins won't have the permissions they need. This doesn't apply to Windows logins unless you move from one domain to another.