MIS Systems Thread, Cannot login after SIMS SQL restore in Technical; I have beautifully crafted backup and restore policy for my SIMS database. The database is in Full Backup mode and ...
11th May 2012, 01:07 PM #1
Cannot login after SIMS SQL restore
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.
IDG Tech News
11th May 2012, 04:52 PM #2
What's wrong with dbattach?
And when you restore, run exec sims.db_p_install
Ah just saw, all I do is run a exe that does the transfer logins - at least until Capita updates dbattach!
Last edited by matt40k; 11th May 2012 at 04:54 PM.
11th May 2012, 05:20 PM #3
All I had to do was run one little command................
Thank you @matt40k, the best way to end a Friday afternoon......actually achieving something!
12th May 2012, 08:26 AM #4
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.
12th May 2012, 11:56 AM #5
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
12th May 2012, 12:05 PM #6
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.
12th May 2012, 12:14 PM #7
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 ;-)
12th May 2012, 05:20 PM #8
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.
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'
Last edited by jinnantonnixx; 12th May 2012 at 05:27 PM.
2 Thanks to jinnantonnixx:
maestromasada (12th May 2012), vikpaw (13th May 2012)
12th May 2012, 10:03 PM #9
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...ols/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.
12th May 2012, 10:04 PM #10
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
Originally Posted by vikpaw
12th May 2012, 10:37 PM #11
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.
Originally Posted by jinnantonnixx
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
Last edited by vikpaw; 13th May 2012 at 01:03 PM.
Reason: add end quote tag
13th May 2012, 09:16 PM #12
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
13th May 2012, 10:32 PM #13
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?
Last edited by Edu-IT; 13th May 2012 at 11:11 PM.
14th May 2012, 10:13 AM #14
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.
Last edited by jinnantonnixx; 14th May 2012 at 11:50 AM.
14th May 2012, 10:21 AM #15
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)
By Nik in forum MIS Systems
Last Post: 13th February 2011, 11:26 PM
By IKWeb in forum MIS Systems
Last Post: 12th February 2011, 09:25 AM
By Max_Power in forum MIS Systems
Last Post: 5th December 2010, 10:02 AM
By Jamie_a in forum MIS Systems
Last Post: 13th November 2010, 11:06 AM
By cookie_monster in forum MIS Systems
Last Post: 29th July 2010, 03:24 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)