+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
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 ...
  1. #1
    skunk's Avatar
    Join Date
    Mar 2006
    Location
    North West
    Posts
    311
    Thank Post
    88
    Thanked 40 Times in 33 Posts
    Rep Power
    29

    Question 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.

  2. #2

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,382
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    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!
    Last edited by matt40k; 11th May 2012 at 03:54 PM.

  3. Thanks to matt40k from:

    skunk (11th May 2012)

  4. #3
    skunk's Avatar
    Join Date
    Mar 2006
    Location
    North West
    Posts
    311
    Thank Post
    88
    Thanked 40 Times in 33 Posts
    Rep Power
    29
    All I had to do was run one little command................

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

  5. #4

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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.

  6. #5
    maestromasada's Avatar
    Join Date
    Apr 2009
    Posts
    166
    Thank Post
    93
    Thanked 14 Times in 13 Posts
    Rep Power
    12
    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

  7. #6

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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.

  8. #7
    maestromasada's Avatar
    Join Date
    Apr 2009
    Posts
    166
    Thank Post
    93
    Thanked 14 Times in 13 Posts
    Rep Power
    12
    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 ;-)

  9. #8

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,954
    Thank Post
    109
    Thanked 482 Times in 331 Posts
    Blog Entries
    2
    Rep Power
    281
    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/backu...r-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'
    Last edited by jinnantonnixx; 12th May 2012 at 04:27 PM.

  10. 2 Thanks to jinnantonnixx:

    maestromasada (12th May 2012), vikpaw (13th May 2012)

  11. #9

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,382
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    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.

  12. #10

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,382
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    Quote Originally Posted by vikpaw View Post
    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

  13. #11
    maestromasada's Avatar
    Join Date
    Apr 2009
    Posts
    166
    Thank Post
    93
    Thanked 14 Times in 13 Posts
    Rep Power
    12
    Quote Originally Posted by jinnantonnixx View Post
    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
    Last edited by vikpaw; 13th May 2012 at 12:03 PM. Reason: add end quote tag

  14. #12

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,954
    Thank Post
    109
    Thanked 482 Times in 331 Posts
    Blog Entries
    2
    Rep Power
    281
    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

  15. #13

    Edu-IT's Avatar
    Join Date
    Nov 2007
    Posts
    7,114
    Thank Post
    403
    Thanked 619 Times in 566 Posts
    Rep Power
    180
    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 10:11 PM.

  16. #14

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,954
    Thank Post
    109
    Thanked 482 Times in 331 Posts
    Blog Entries
    2
    Rep Power
    281
    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 10:50 AM.

  17. #15

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,954
    Thank Post
    109
    Thanked 482 Times in 331 Posts
    Blog Entries
    2
    Rep Power
    281
    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)

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. [SIMS] SIMS SQL 2008 Migration
    By Nik in forum MIS Systems
    Replies: 8
    Last Post: 13th February 2011, 10:26 PM
  2. [SIMS] SIMS SQL Version...
    By IKWeb in forum MIS Systems
    Replies: 30
    Last Post: 12th February 2011, 08:25 AM
  3. [SIMS] SIMS SQL 2008 - How much memory do you have allocated?
    By Max_Power in forum MIS Systems
    Replies: 15
    Last Post: 5th December 2010, 09:02 AM
  4. moved sims sql database but logins not moved
    By Jamie_a in forum MIS Systems
    Replies: 3
    Last Post: 13th November 2010, 10:06 AM
  5. [SIMS] SIMS SQL 2005 --> 2008 migration
    By cookie_monster in forum MIS Systems
    Replies: 7
    Last Post: 29th July 2010, 02:24 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •