My office (unfortunately) still uses MS Access for our website's database. We use a mini CMS that I wrote to update the database, and once it's been updated, we use Windows Explorer to copy it to the live site.
Soon, Explorer won't be an option. We're using a sort of pseudo SSH client to access both our development and production servers, so we'll have to copy files to our local machine, edit them, then copy them back to the server. Once they're checked, the files get copied from the dev server to the live server, but we're running into a problem when copying the Access MDB files. It appears that if one of our web pages accesses the database while the database is being copied, we get some sort of corruption problem. No one's been able to figure out how to solve it.
Since I've been trying to get Access out of here for over a year, I'm looking at this like another salvo to that end. I've been trying to get them to buy a copy of MS SQL Server, so I'm trying to figure out how you would copy a database from a development server to a production server using MSSQL. If I can prove that MSSQL will get us past this copying problem, it'll be another nail in Access's coffin.
Does anyone here use MSSQL in a dev/production environment, and if so, how do you copy your database from one server to the other?