Mirroring live data base on dev server

[mit99mh]

I've been given the task of sorting out a dev server - the live version of the database and the dev version are very different how would anyone recommend updating the dev version so everything (roles table designs etc) is identical to the live server - I was going to fully back the live server up and then restore the dev version using the backup file - is this a reasonable way to achieve what I want?

Any other ideas very welcome.

 

[hmckillop]

If you backup and restore, it will also move all the live data onto you development server, also removing all existing data on the development server.
if this isnt an issue for you then, it is the simplest.

Alternatively, if you need to port from one to the other whilst keeping the existing data in you dev server, you can use a tool like Red Gate's SQL compare, which also creates scripts for moving from one database to the other.
(including roles, users et al)


"I'm living so far beyond my income that we may almost be said to be living apart

 

[mit99mh]

Cheers for the reply - the data isn't a problem what I need to save are all the permissions etc.

 

[hmckillop]

If I was going to do that I would script the database roles and permissions using Enterprise manager and keep it updated.
i.e.
If you open Enterprise manager, select a database on your live server, right click on it, select all tasks and then generate SQL script.
Select the last tab and select the following options:
Script Database Users and roles
Script SQL Server Logins (Windows & Sql logins)
Script Object level permissions.



"I'm living so far beyond my income that we may almost be said to be living apart