Database Attach/Detach Batch file

[Jack_Rowland]

Hi,

I have been trying to figure out how to create a Batch file to automatically detach, delete and reattach a fresh sql2012 database. We use the databases for training and after each use we re-attach the fresh database. it is a tedious process as this needs to be done almost every day. I decided to try and automate this process, so far i have been able to detach, delete and attach a new database successfully but it drops the usernames and passwords and i do not understand why.

 

"C:\Program Files\Microsoft SQL Server\MSSQL11.SIMS2012\MSSQL\Binn\Dbattach.exe" /SERVER=".\sims2012" /DATABASE="Engwe0" /USER="SA" /PASSWORD="Password1" /AUTODETACHMODE /AUTO

 

pause

 

call "C:\Program Files\Microsoft SQL Server\MSSQL11.SIMS2012\MSSQL\DATA\delete.bat"

 

pause

 

"C:\Program Files\Microsoft SQL Server\MSSQL11.SIMS2012\MSSQL\Binn\Dbattach.exe" /SERVER=".\sims2012" /USER="SA" /Password="Password1" /DATABASE="ENGwe0" /SOURCEDB="D:\Keystone\november15traindata_sims7.166_engwe_sql/ENGwe0.mdf" /AUTOATTACHMODE /AUTO

 

pause

 

this is the first time i have attempted a script and also the first time I've posted here, i would greatly appreciate any thoughts you may have

Thanks In Advance!

[vikpaw]

The usernames are stored in the master db. Some functions should copy them into the actual sims database. A restore will then replace the database file and I believe will also update the usernames.

 

Why don't you do a restore? You could schedule it with SQL studio and it's less steps.

 

If usernames are still a problem, search the forum for some SQL code to transfer logins, it's a stored procedure. I think it's called sp_db_transfer logins or something like that.

 

A transfer should pull the logins from Master into the sims database and the other procedure pushes them from the db into the master

 

You should be able to achieve what you want easily. It depends which usernames you want to use. You should be able to save then into the fresh (dummy) database and then they should stay there every time you do a restore.

[vikpaw]

A detach / reattach cycle on the same database file should push what is stored in the db to the master , they need to be stored there first.

So running the stored procedure should do this for you, and then you can use your script or do a restore like I said.

 

Procedure here but was for a different context, so usual caveats apply - check it yourself and make sure it's relevant, do a backup and don't play with a live system:

http://www.edugeek.net/forums/mis-systems/78287-passwords-reset-when-attaching-database.html

[Jack_Rowland]

thank you very much for your comments, i now have it restoring instead of attaching the MDF and LDF and it is working perfect. thank you!!! you have saved me a lot of time!

[vikpaw]

to EduGeek