[SIMS] [Discover] Database backup issue [Solved]

[Rawns]

When a discover database is backed up manually using the dbAttach.exe GUI, the backup completes successfully.

Nightly though, we script a backup to take place using the dbAttach command parameters to back up SIMS and FMS through a batch file. This always works perfectly and I have modified the script to also backup the new Discover database. The command is exactly the same for the SIMS and FMS backup minus the database name:

"D:\Program Files\Microsoft SQL Server\MSSQL10.SIMS2008\MSSQL\Binn\ dbattach.exe" /BACKUP /AUTO /SERVER=%COMPUTERNAME%\SIMS2008 /DATABASE=Discover /USER=****** /PASSWORD=******

When running this through the command prompt, this pops up:

dberror.bmp

Has anyone else come across this? Is anyone else successfully backing up Discover this way?

Edit: tested on my local XP machine and I get the same issue. Defiantly some kind of problem with dbAttach.

[vikpaw]

I remember getting that error when trying to use the commandreporter, i think it's just unhappy.
Are you sure all the parameters are correct? servername, instancename etc. I thought Discover was on it's own instance.

Incidentally, i think, you're supposed to use the different dbattach for sims and fms, even though they are exactly the same, it's best practise.

[Rawns]

I remember getting that error when trying to use the commandreporter, i think it's just unhappy.
Are you sure all the parameters are correct? servername, instancename etc. I thought Discover was on it's own instance.

Incidentally, i think, you're supposed to use the different dbattach for sims and fms, even though they are exactly the same, it's best practise.

All the parameters are 100% correct. Discover attaches itself to the SIMS2008 SQL instance. It says you can put it there in Capita's install guide. I tested it against my own test system and experienced the same issue.

Also, I told a slight porky in my first post where I said the script was exactly the same for SIMS and FMS. We use the Binn\dbAttach.exe for SIMS and Discover backups and Binn\FMSSQL\dbAttach.exe for the FMS backup.

[vikpaw]

Does it have it's own dbattach? I've not had a chance to play with it yet. Does it have to be done via dbattach, you could script one in SQL Management studio easy enough until you get the main problem fixed. I just do all mine that way, and it emails me when it's done.

EDIT: Hah! caught you porky pie :P didn't think you'd make a schoolboy error like that..

[Rawns]

No, I don't believe it does have it's own. It's easier to do it through DB attach (if you run dbAttach manually and use the user interface, the backup works perfectly - it's just when scripted)

We will most likely look at a SQL management studio script. The problem is that only our secondary schools have management studio installed and our backup script is used in every school in the county and rolled out daily from one central location. Having it work correctly in our backup routine would be a very quick, pain free method as more schools implement Discover.

[vikpaw]

For an interim measure an osql script would also do the job, or do you need management studio for that too.

[NorthernSands]

Out of interest, do we really need to backup the Discover DB? After all, it's contents can be recreated from scratch just by syncing to a fresh DB....

[vikpaw]

True but doesn't it contain the DUDGs plus history and saved analyses. Haven't used so don't know.

[Rawns]

Yes, any custom Discover aspects/groups etc would be lost so we will be treating this as any other database and backing it up daily. Also, I'm not sure what would happen if you had to roll back SIMS to an old backup and the Discover database had data in it that SIMS database did not.

[NorthernSands]

Fair enough. I just have some vague recollection of something said at a an early demo (some 14 months ago) by a Capita guy who said nothing would be stored in the Discover DB except a cache of the data, all the settings etc would be stored in SIMS.

[matt40k]

@Rawns have you tried adding the /path?

[Rawns]

@Rawns have you tried adding the /path?

Thanks Matt.

Just tried this and it seems to work. The only down side is that you have to specify a file name with the Path command so there will only ever be one backup as it will overwrite the previous one.

[matt40k]

/path:"S:\MSSQL10.SIMS2008\MSSQL\Backup\discover_%d ate:~-4,4%%date:~-7,2%%date:~0,2%.bak"

[Rawns]

/path:"S:\MSSQL10.SIMS2008\MSSQL\Backup\discover_%d ate:~-4,4%%date:~-7,2%%date:~0,2%.bak"

You are on FIRE today mate! Thanks!

I'd throw Rep your way but it won't allow me to.