Does a database restore remove existing stored procedures

Question

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?

• Dan Bracuk: probably. Maybe you should set up a test scenario and try it.
• TomTom: 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 ;)

Answer 1

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

Answer 2

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.

• Aaron Bertrand: 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.
• Cougar9000: @AaronBertrand - correct.