SQLTeam.com | Weblogs | Forums

Creating new database from saved database scripts

I am new to DBA role. I have requirement below. I have a database environment named SystemApp deployed on Development DB Server. In production server, the database with same name exists which is more updated with latest table definitions & other DB changes. I need to replicate all these changes to our development database. I am doing the following tasks in the order in which it is listed. Kindly look into whether I am doing the correct way. Please correct if I am doing wrong way or something more needs to be added to this list. Thanks.

  1. First I will take the DB backup of the database SystemApp deployed on Development DB Server.

  2. Take the scripts of DB & DB objects of the database SystemApp deployed on Production DB Server using Tasks >> Generate Scripts option.

  3. DROP the database SystemApp deployed on Development DB Server.

  4. Run the database scripts of Production DB in Development DB Server.

Will this work ? Kindly guide me on this.

Yes that could work but there might be an easier .

Restore production backup to development server. Does it have any sensitive data? Is prod backup maybe too big?

I don't want the Production DB data, only schema is required.
Production DB backup won't be too big, will be roughly around 10 GB.

Then what you have planned should work. You will run into a few small issues though. Try it and you will find out.

What will be those small issues? Please guide me.

for example you might have some foreign keys. the script generated by SQL might not respect the sequence of tables' creation. not sure why you do not want to just restore from prod as it seems the source of truth at this moment.
also some things you might just find by trial and error because there could be unique things to your database. install sql developer locally and try your script there would be my recommendation. once you have sorted these out then move to development server.