SQL 2012 Express backup help.

[edutech4schools]

Hi, We have been asked to go from SQL 2008r2 to SQL 2012 Express by our LA. They have sent us migration instructions and pointed me to a backup script by Support.net. I have installed a test server and done a test migration and all is OK but when I run the backup script I have no idea where it is storing the backup.

 

This is the script

"C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn\DBATTACH.exe" /SERVER="WIN-R7C8IKOT6K0\sql2012" /DATABASE="SIMS" /BACKUP /AUTO /TRUSTED

if ERRORLEVEL 1 GOTO :ERROR

GOTO :END

1/7/2014 SupportNet - Solution Detail

http://support.capitaes.co.uk/websupport/RenderXSL.aspx?KB=KB29316.xmlFalse 2/3

:ERROR

ECHO BACKUP NOT CREATED

:END

 

 

Thanks

[edutech4schools]

I do not think the script is running. If I put a pause at the end of the script and then run it as a .bat file it says the database has not backed up.

[clareq]

We run a similar script, and the backup ends up in C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup

[edutech4schools]

OK, Just run a manual backup using dbattach and found the .bak file. So just need to work out why script is not working.

 

Thinking about it, should the script not have user accounts etc to login to do the backup?

[clareq]

Ours has user name and password. yes.

[edutech4schools]

Would that be the sa username and password?

 

Would you mind posting your script so I can check it against mine.

[clareq]

This is our current script:

REM -- Delete Last Sims SQL Database Backup --
DEL /q "D:\Sims Backups\Live\Sims.BAK"
DEL /q "D:\Sims Backups\Live\Discover.BAK"

REM -- Transfer Sims Login Info back to Database --
osql -S(local)\sims2008 -E -Q sims.sims.db_p_transfer_login

REM -- Create SQL Backup (*.Bak) of Sims SQL Database --
"D:\Program Files\Microsoft SQL Server\MSSQL10.SIMS2008\MSSQL\Binn\DbAttach.exe" /SERVER=(local)\Sims2008 /DATABASE=sims /USER=sa /PASSWORD=*** /AUTO /BACKUP /PATH="D:\Sims Backups\Live\Sims.BAK"

REM -- Create SQL Backup (*.Bak) of Sims Discover SQL Database --
"D:\Program files\Microsoft SQL Server\MSSQL10.SIMS2008\MSSQL\Binn\DbAttach.exe" /SERVER=(local)\Sims2008 /DATABASE=discover /USER=sa /PASSWORD=*** /AUTO /BACKUP /PATH="D:\Sims Backups\Live\Discover.BAK"

REM -- End Of Script --

[edutech4schools]

Thanks.

REM -- Transfer Sims Login Info back to Database --

osql -S(local)\sims2008 -E -Q sims.sims.db_p_transfer_login

Does this move the users into the database before backing up the database?

[clareq]

I don't think so - it was explained to me that the line was to fix an error in SIMS. The script was prepared by our LEA Support.

[matt40k]

Thanks.

 

Does this move the users into the database before backing up the database?

 

Yes. It copies the users from the MASTER database into the SIMS database, if you have FMS or other SIMS databases you'll need to run it against each database. Also you'll need to change the first SIMS if your database isn't called SIMS.

[edutech4schools]

The backup runs, YAY. Just few questions.

 

If I run the backup with or without that code to import users from the master database, the backed up file size is exactly to the KB the same size which implies nothing is actually getting added.

 

I thought that if you used dbattach to backup the database this did the master user import anyway, or was that just using dbattach to detach a database.

 

Thanks for all the info so far, not sure what I would do without Edugeek forums.

[matt40k]

How big do you think your passwords are? Lol. You'll have a table already it'll just update it with the latest password hash.

 

DBattach when restoring will sync with the MASTER database, it just doesn't do it when you backup. Some SIMS upgrades will trigger it to sync the master with the database, but not backing up. There is a CR for it, but... well, lets put it this way, with over 1,000 active CR logged this year alone and a workaround I doubt it'll get fixed anytime soon.

[edutech4schools]

Thanks

[edutech4schools]

I have been using the sql 2008r2 manager to backup all my schools and then import into a test sims environment and this has worked fine for all but one.

 

This one school gives me an invalid user / pass error (not a reason 0) so I tried using the backup code in this thread but it still gives me the same error.

 

Does the code work for SQL2008r2?

 

any other ideas??

[edutech4schools]

I have created a new thread as my latest issue is not based on SQL2012 express.

 

http://www.edugeek.net/forums/mis-systems/139296-very-stressed-sims-invalid-username-password-all-users.html