SQL Server Performance

refresh only database objects

Discussion in 'SQL Server 2005 General DBA Questions' started by raagi2000, May 13, 2009.

  1. raagi2000 New Member

    Hi ,
    Need data refresh production to development including objects like tables, stored procedures , views etc. we DONT need complete database restore (backup and restore will overwrite existing development users, roles and permissions which we don't want). what is the best solution.
    it is in SQL 2005
    thank you
  2. FrankKalis Moderator

    One way would be to export the data from prod and import into dev and script all objects in prod and apply that script onto dev.
    However, I would have a look at some third party comparison tool that handles objects and data synchronization for you. Have a look at the software section here to see what's available in that regard. although it costs some money, in the long-run you save plenty of time with such a tool.
  3. Sandy New Member

    [quote user="raagi2000"]
    Hi ,
    Need data refresh production to development including objects like tables, stored procedures , views etc. we DONT need complete database restore (backup and restore will overwrite existing development users, roles and permissions which we don't want). what is the best solution.
    it is in SQL 2005
    thank you [/quote]
    As Frank said for using Script method, I also suggest the same way. But adding to that just takes a Back up of your DEV Server Database and then applies the Prod DB script. Script option you can choose also (Table, view, SP, UDF...etc). Then using Import and Export just dump the Prod data to Dev Server.
    NOTE: If in future you need to refer some thing from Old DEV Db then you can check from the back up DB.
    Hope it will help you.
    Thanks,
    Sandy.
  4. prabhabk New Member

    Hi Better option is script out the users,roles and permissions of dev database and refresh the database using the production backup then reapply the permissions
  5. raagi2000 New Member

    We have about 500+ tables. Most of them are Dependant objects have reference integrity or some contraints won't let overwrite a object unless you fix the master table. in otherwords the import and export wizard will fail eventually. it is very hard to get the sequence of the objects.
    i have also tried scripting the users , roles , permissions in dev and restore the whole database but in practice it does not do the good job. in otherwords we still loose some permissions. we want something 100% solid refresh.
    Can anyone suggest is there any straight forward way or the way we can do..
    thank you. been struggling and unable to give ETA. scripting is hell as it involves more objects and contraints complexity.
  6. MohammedU New Member

Share This Page