System Manager Backups Unreliable - Fifth Element Story

[Ueline]

After consultation with Capita about my test/backup SIMS SQL Server I want to make everyone aware that System Manager Backups are unreliable when you try to use them on another server. Since migration to SQL2005 there is an issue with passwords when trying to restore from a backup to a new server. The passwords are no longer part of the backup from system manager as they're in the master.mdf file. You can get locked out of your own database as it resets all the passwords.

There is a way round this though, I wanted to share it, apologies if you all already know this.

 

dbAttach does not normally include the option to restore a database unless you do the following.

 

Go to Start, All Programs, SIMS Applications and right click on dbAttach.

Click Properties. Copy the line in the target box already highlighted, something like...

"E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SIMSSQLLoad.exe" "E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\dbAttach.exe"

 

Start and run and paste the line in adding a space then /restoreui onto the line. Click ok.

 

dbAttach opens.

 

Use this to back up your data on your live server. It's better to use this method whatever your intention as for the time being I'm told - there are 'issues' with backing up with System Manager.

 

You can then copy to your secondary server/test server and use the same method to restore your data.

 

This summer I had the disaster of all disasters and had to rebuild data like ...well imagine The Supreme Being Lee-Lou and The Fifth Element...I was left with a fragment of bone from which I had to rebuild everything. It made me very stressed and I can't tell you how much it's worth making sure your backups work.

[sahmeepee]

Am I to assume that the backup which executes as part of an upgrade (like the November one I've not got around to running yet) is a "good" backup?

 

I suppose I should do my own homework and go on SupportNet!

[Ueline]

No I think not. All the other methods produce one zipped file with one bak file contained within. This method produces no zipped files at all, just three bak files, sims, master and msdb.

[Ueline]

Also there's nothing on Supportnet about this. I post only in emergency situations!!!