[sims] Cannot login after SIMS SQL restore

[skunk]

I have beautifully crafted backup and restore policy for my SIMS database. The database is in Full Backup mode and I have a Maintenance plan that runs a weekly full backup, daily differential backups and hourly transaction log backups. The Weekly and Daily plans also run the T-SQL command "exec sims.sims.db_p_transfer_login" before the backup routine to ensure that the logins are moved into the sims database. These run successfully without any reported errors.

 

During disaster recovery testing, I can successfully use the backups to restore the database to a point in time of my choosing, which is very useful. However, the login details are not transferred so I cannot log into SIMS once it has been restored. I have run the sims.sims.db_p_transfer_login script after the restore to see if this made a difference, it didn't. The users are all sat there in the sims/Security/Users folder (Server Management Studio), but not achieving anything currently.

 

Turns out I have a beautifully crafted chocolate fireguard......can anyone out there offer any assistance on where I have gone wrong?

 

I don't wish to return to the scripted dbattach backup system in SQL Simple Backup mode that was running before (I lost all logins once before doing it that way). I am also reluctant to detach and re-attach the live database as I wouldn't be able to do this in a disaster recovery scenario.

[matt40k]

What's wrong with dbattach?

 

And when you restore, run exec sims.db_p_install

 

 

EDIT:

Ah just saw, all I do is run a exe that does the transfer logins - at least until Capita updates dbattach!

Edited May 11, 2012 by matt40k

[skunk]

All I had to do was run one little command................

 

Thank you @matt40k, the best way to end a Friday afternoon......actually achieving something!

[vikpaw]

So i take it running the db_p_insall worked?

How are you restoring your backups, using SQL studio? I would recommend that you use dbattach to do the restore, as this should run above script as part of the process. The only errors i've had when doing that is if using trusted logins as my test server wasn't on the domain.

[maestromasada]

Not sure if my experience can help you: I never had a problem with dbattach, and have restore SIMS several times and doing the SQL releases 2005 > 2008 all by myself on test environments without significant issues, before doing the upgrade on the production server.

 

What I have all the time though in the SIMS server is stop the SQL database with a batch file prior to the backup, so the mdf file is not in use at all

[vikpaw]

You shouldn't need to stop the service if you're using dbattach. Failing that, just use a standard SQL management studio backup, that's what i do.

It's just best to use dbattach to do the restore as it runs the logins script for you.

I put in a change request to add parameter options for running those scripts into the dbattach .exe but it fell on deaf ears. It's probably been deleted now.

[maestromasada]

but dbattach is after the sims.mdf file right? Maybe I shouldn't stop the service but I certainly feel more comfortable knowing that the sims.mdf file I'm restoring from a tape has been recorded there while the file was not in use

 

I guess in the 'kitchen' of IT anybody washed their dishes in their own way, following common sense standards of course ;-)

[jinnantonnixx]

Please don't just copy the mdf and ldf files. This isn't the way to do SQL backups. At the very least you'll run into truncation problems.

 

A proper backup is done with the SQL 'BACKUP' command, or a scheduled job which you can set up using SQL Server Manager. This way you can keep the database running, and the backup is a consistent, discrete file which you can use to restore to the same or another server.

 

http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups

 

http://msdn.microsoft.com/en-us/library/ms186865.aspx

 

 

Until you properly prove your backups by restoring them to a DR system and getting the recovered system working properly, you only have 'alleged backups'

Edited May 12, 2012 by jinnantonnixx

[matt40k]

Like what @jinnantonnixx says, a backup is better then copying the mdf\ldf. I've had a few problems with attaching mdf\ldf, i've always got the data back but having to do a rebuild isn't fun.

 

Problem with the loss of logins (or more to the point, the lack of current passwords) is normally just FMS. SIMS is pretty robust. Like I said, I just have a exe that runs the transfer procedure which I run before dbattach.

 

Again, whatever you go for, it's only good if you can restore from it. I've had a few people that haven't been able to restore data because they switched from Symantec to DPM, or the tape drive was aligned incorrectly, so when the tape drive got replaced, all the data on the tapes couldn't be restored. That's what I like about our LA backup solution - https://www.schoolsurf.suffolkcc.gov.uk/docs/unrestricted/ICT/Remote_Back-up_Service_for_Schools/index.aspx, it just works. Sure other LA have solutions that are based on Attix5. Just hope people realise how import it is to back their MIS database.

[matt40k]

I put in a change request to add parameter options for running those scripts into the dbattach .exe but it fell on deaf ears. It's probably been deleted now.

 

To transfer logins? Yer, I asked about that, I got the split mdf\ldf support in, but I didn't managed to get that in. Will have you do some moaning again

[maestromasada]

Please don't just copy the mdf and ldf files. This isn't the way to do SQL backups. At the very least you'll run into truncation problems.

 

Thanks for the massive tip! Was always so confident that a good copy of mdf/ldf was enoguh that never gave another thought about it.

 

Done RDP and an emergency backup using Systems Manager, have to reconsider now the strategy use to backup SIMS, as we all know the data in there can-not be lost

Edited May 13, 2012 by vikpaw
add end quote tag

[jinnantonnixx]

Perhaps the easiest way (as you have the Manager console) is to set up a scheduled job to backup your database. From here, you can move the .bak files to a safe place.

 

And TEST IT! Make sure it works.

 

This might be of use:

How to schedule a database backup operation by using SQL Server Management Studio in SQL Server 2005

[Edu-IT]

Just out of interest, what's the general consensus on backing up SIMS/FMS? Is once daily enough or should it be done hourly?

 

Also, I'm using BackupExec to backup the SQL database. I've not specified "exec sims.sims.db_p_transfer_login" anywhere as a pre command. I'm guessing I should?

Edited May 13, 2012 by Edu-IT

[jinnantonnixx]

It all depends on what you're prepared to lose. If you back up every day, worst case is you've lost a full day's work.

I would write it in to your SLA. If you state that the backups are done daily, and that you can restore from the night before, then if that's in your SLA that's what you do.

 

Personally? Id' go for a daily or weekly full with hourly log backups. If you do this, your database should be in 'full' recovery mode, not 'simple'. 'Simple' restricts you to full backups; you can't make differential backups on a 'simple' database. Doing hourly full backups are likely to generate huge amounts of backup data and stress your system.

 

 

In my case, we have two (large) virtual servers - one live, hosting about 100 databases (central hosting). The other server is a warm standby, with copies of the databases in restoring mode.

 

I set up log shipping between the two; every 15 minutes the transaction logs are shipped from the live to the standby. I wrote the scripts to do this, it was a lot of work but it means that we can fail-over a database (or 100 database) in under a minute. Worst case we lose 15 minutes of work.

Edited May 14, 2012 by jinnantonnixx

[jinnantonnixx]

SQL 2008 has database mirroring. I haven't really looked at it, but it might be worth a look.

How to: Configure a Database Mirroring Session (SQL Server Management Studio)

[skunk]

I would avoid doing hourly differential backups as each differential contains all of the changes since the previous full backup. Transaction log backups remain small as they only contain the changes since the last transaction log backup was taken.

 

Now that I have confirmed the process works successfully, I was going to pop my documentation onto the edugeek wiki, but editing has been turned off.

 

@vikpaw Yes, all I had to do was run the sims.db_p_install script

Edited May 14, 2012 by skunk

[vikpaw]

I do daily FULL backups. It's too much space to do more than that. Haven't tackled the logs, though used to. Mostly the requirement is restoring for testing or pulling back / checking on some old data. If it truly was a disaster, one day's loss wouldn't be that bad in the grand scheme of things.

 

If anyone is doing differential or incremental, be careful that you only have one backup system (i.e. backupExec, or NT backup, or manual, or SQL studio) talking to the database else you will corrupt the backup regime. Two different systems which is what i had at one stage, each going in and doing their own thing, means only the full backups will work. I learnt the hard way, when i was doing my own system and the NM had BackupExec with an SQL agent going in and doing something else.

[jinnantonnixx]

If anyone is doing differential or incremental, be careful that you only have one backup system (i.e. backupExec, or NT backup, or manual, or SQL studio) talking to the database else you will corrupt the backup regime. Two different systems which is what i had at one stage, each going in and doing their own thing, means only the full backups will work. I learnt the hard way, when i was doing my own system and the NM had BackupExec with an SQL agent going in and doing something else.

 

Good point, Vik.

 

Backup operations in tandem, whether it's an SQL script or a third party agent like Veritas will affect the log file, and these competing backup operations are certain to ruin the backup chain.

 

Sometimes however, you will need to make an ad-hoc backup, perhaps to test something. In this case it's possible to make a backup with the COPY_ONLY flag set either from an SQL statement or through the manager console. In either case, this 'COPY_ONLY' backup has absolutely no effect on the log files, which is very, very useful if you need to make a one-off backup without ruining your backup set.

 

Copy Only Backup for SQL Server 2005 and SQL Server 2008

 

 

Copy-Only Backups (SQL Server)

Edited May 14, 2012 by jinnantonnixx

[skunk]

We had a four hour loss a few months back and the hassle was immense, so down to an hour now, could try 15mins as per @jinnantonnixx, but that will have to wait for another day.