Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
1  
probably. Maybe you should set up a test scenario and try it. – Dan Bracuk Jun 3 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 at 8:37

migrated from stackoverflow.com Jun 3 at 8:19

2 Answers

up vote 10 down vote

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
share|improve this answer
up vote 1 down vote

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.

share|improve this answer
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 at 13:03
@AaronBertrand - correct. – Cougar9000 Jun 3 at 13:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.