Copying SIMS to a test server

Hi fellow edugeeks

I'm trying to set up a brand new server with SIMS. Initally this is just for testing the process alongside our current SIMS server, but will eventually replace the old server. I've seen a few threads on here with details of using DBAttach to detach the SQL database, copy the SQL files to the new server then re-attach.

However, if possible I'd like to use the .bak file created as part of our nightly backup to restore the DB on the new server. I can do this using dbattach with the /restoreui switch but it doesn't copy the user logins. Detaching, then reattaching the DB doesn't help :confused: Has anyone successfully copied an entire SIMS DB, including logins? If so how did you do it? Is a db detach/reattach the only way?

The reason I want to use the .bak file is it doesn't affect the current system

Cheers

the .bak file not copy the passwords as they are now stored in the master of the SQL server.

There is a routine you can, and should run which will copy the passwords.

The other way is to create a backup using dbattach of the database and then extract the .bak file from within the zip file and dbattach using the /restoreui switch

@familyguy:

Is this of any use:

How to transfer logins and passwords between instances of SQL Server ;) :) :)

That sounds like just the ticket.

I actually just managed to get in working using a backup of the master.mdf file from an old backup.
Just dropped it in the SQL data directory and we were good to go after restarting the service. Looks like tranferring the logins is the best way though.

Cheers :D

I'm intrigued - I regularly use dbattach with restoreUI to restore data from my main server onto a test setup and I've never had a problem with passwords, In fact when I have changed passwords on the test setup to allow me to play with other users details I find that restoring the backup sets them back to the users original password. It even works on a clean database (ie a brand new SIMS installation). I'm wondering what is different about my system

I think I've answered my own question we use dbattach to create the backups in the first place

It's a bug, in short. If you open up the MSSQL, you'll notice the logins are within the MASTERS and also within SIMS. Sometimes they don't appear in the SIMS database. Capita's offical response is to deattach, reattach the database, basically poking it, the exec sims.db_p_transfer_login should force the MASTERS passwords to be copied to the database.

You can also get problems if you reattach the database with another name. Also if you have trusted logins and your not attaching it to the same domain.