Home Forums SQL Server 7,2000 Administration I need to Copy/refresh the "data only" to Test from prod RE: I need to Copy/refresh the "data only" to Test from prod

  • Michael L John (2/9/2016)


    How many actual users exist, and how are the permissions configured?

    It seems as if generating a script to remove or disable users that exist in production, and the same for the users an permissions in dev, would be far easier than going through all of the work required to use the import wizard, an ssis package, or a third party tool.

    Once those scripts are created, you restore a backup of production to development and run the scripts.

    I'm with Michael on this one. Another approach to the permissions issue is to write a script for all the user permissions in dev and save it. Restore a backup from production into dev. Then drop all the database users from dev (inherited from the production restore) and run your script to create users and permissions like they were before.

    One caveat: Unless you have sysadmin privs, don't forget to leave your database user intact so you can run your script to apply the permissions. If you have sysadmin privs, you don't need to worry about it.