I have a PROD and NON-PROD system

NON-PROD already has a copy of an application PROD database

I have also created new stored procedures on the NON-PROD application database and made changes to a few old ones

If I were to take a fresh backup of the PROD application database, and were to restore it on the NON-PROD server, will this:

  1. remove the new stored procedures which I created on the NON-PROD application database?

  2. remove the changes made to the stored procedures (NON-PROD application database) which have the same name as on PROD application database, and replace them with the original one's from PROD application database?

migrated from stackoverflow.com Jun 3 '13 at 8:19

This question came from our site for professional and enthusiast programmers.

  • 1
    probably. Maybe you should set up a test scenario and try it. – Dan Bracuk Jun 3 '13 at 2:05
  • 3
    Maybe a little more professional workflow is in order - one where dev databases are checked into source control (the geneation code), so that you are not relying on a non-prod database to maintain changes ;) – TomTom Jun 3 '13 at 8:37

A restore will completely wipe the target database (if it exists, otherwise created of course).

Any changes to tables, code security and data will be lost in the target database, which will be 100% identical to the source database at the time the backup happened

  1. Yes it will
  2. Yes it will

I will add the caveat that if you have created the Stored procedures in a system database they will remain, but yes, if you create and house the stored procedure in the user database in question, they will be gone after the restore.

  • 2
    Though usually not a good idea to create stored procedures in system databases, since you want databases to generally be as "contained" as possible - eliminating external dependencies like procedures in other databases should those other databases go away (imagine having to rebuild the system) or the user database gets moved to a new location. – Aaron Bertrand Jun 3 '13 at 13:03
  • @AaronBertrand - correct. – Cougar9000 Jun 3 '13 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.