I have a Access 2003 FE with a MS SQL 2005 backend. I need to be able to copy the backend database at night to be able to update the data in an developmental database that we use to test against. The developmental database can be on the same or a different server. I have tried to use the replication (snapshot) but it is read only, I need to be able to have read write capabilities.
Currently I am taking the database offline at night and manually copying the file group to a different server and renaming it and then attaching/renaming it to the developmental database, then putting the production database back online.
I know that there is a way to automate this process (Scripts, Maintenance plan) but I am not sure were to begin. I have very limited knowledge of SQL as we just upsized from a Access backend.