+ Post New Thread
Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 46
MIS Systems Thread, Sims Backup in Technical; Interesting read Vik, thanks...
  1. #31

    GREED's Avatar
    Join Date
    Mar 2008
    Location
    Portsmouth
    Posts
    2,976
    Thank Post
    367
    Thanked 359 Times in 293 Posts
    Blog Entries
    8
    Rep Power
    173
    Interesting read Vik, thanks

  2. #32

    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
    Footnote on above findings:

    I went back to snapshot of clean install of spring 10. There were no logins created under security tab on SQL.

    Restored my summer 10 backup. - Looking at the error message, the page full i could see was all related to the trusted logins, and then just starts on about a repair to another account. Might try to run from command line to see if error could be redirected to a file..

    Anyway, closed that and found full list of logins in SQL, so just tried to login with a known account and it worked So don't need to even update one account to push settings as i thought, they were all pushed on as part of the restore which is really good to know. Had the machine been domained or i had no trusted logins, i probably would not even receive any errors.

    Would be good if anyone else is testing that can confirm these findings. It looks like, it's not all that important to detach the db. Still i'm unconvinced, so tried to delete all users from sysman and start again, but have managed to bork the install, and cleared all users from sims, but not from SQL. Might need to consult jinnantonnix reincarnate for some sql tips tomorrow before i can know for sure.

  3. #33

    GREED's Avatar
    Join Date
    Mar 2008
    Location
    Portsmouth
    Posts
    2,976
    Thank Post
    367
    Thanked 359 Times in 293 Posts
    Blog Entries
    8
    Rep Power
    173
    Keep us informed as to the progress Vik

  4. #34

    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
    I never fully updated my findings because i ran out of time and over Easter needed to get the server up and running. I did a detach and reattach of my databases and didnt' lose any logins, as should be the case.

    I think, that from now on, if i restore my new backup files, to a test server the logins will be pushed there, as i first thought, but the best test would be a brand new server with a clean master db. I'll see if i get time to do those tests later.

  5. #35
    NorthernSands's Avatar
    Join Date
    Mar 2011
    Location
    Qatar
    Posts
    134
    Thank Post
    21
    Thanked 19 Times in 19 Posts
    Rep Power
    10
    Quote Originally Posted by arthur231283 View Post
    I have 2 batch files. One that stops the SQL service of the database before the backup starts and one that starts it again when its finished.

    net stop "SQL Server (SIMS2008)"
    net start "SQL Server (SIMS2008)"

    You will need to replace "SQL Server (SIMS2008)" with the name of your sims databse.

    I am using Backup exec and put these in the pre and post command sections
    I've run into situations where a restoration following this method of backup has left the SIMS DB corrupt meaning a hefty Capita bill to sort it out (if they can). Just stopping the SQL service and copying the MDF LDF doesn't always work. The DB will need detaching correctly (using DBAttach) or use the DBAttach script to backup on the fly.

    Seriously: I can confirm that this is not a safe backup method!

  6. #36

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,386
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    Quote Originally Posted by NorthernSands View Post
    I've run into situations where a restoration following this method of backup has left the SIMS DB corrupt meaning a hefty Capita bill to sort it out (if they can). Just stopping the SQL service and copying the MDF LDF doesn't always work. The DB will need detaching correctly (using DBAttach) or use the DBAttach script to backup on the fly.

    Seriously: I can confirm that this is not a safe backup method!
    Could you expand on this statement?

  7. #37

    Join Date
    Apr 2010
    Posts
    2,036
    Thank Post
    83
    Thanked 187 Times in 154 Posts
    Rep Power
    83
    Why not use SQL2008 to backup and copy the files as a maintenance plan? Although once in a while I do the dbattach method.

  8. #38

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,386
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    ...Which requires SQL Agent, which isn't include in the Express version.

  9. #39
    NorthernSands's Avatar
    Join Date
    Mar 2011
    Location
    Qatar
    Posts
    134
    Thank Post
    21
    Thanked 19 Times in 19 Posts
    Rep Power
    10
    Quote Originally Posted by matt40k View Post
    Could you expand on this statement?
    Certainly though I don't know all the ins-and-outs as it was a colleague who dealt with it. A school backed up using this method (not BackUp Exec, but to tape). The server died taking the live data with it. On restoration, the DB had not only dropped logins, but also lost some recent data, with some data corruption. The previous backups were the same (a rolling weeks worth). Everything else from the tapes recovered fine. File recovery on the HDD gave us the live DB, but with similar problems. I do not know the exact nature of some of these problems, the DB was known good prior to the server dying. Anyway, Capita became involved, charged a hefty fee, and left the school with a DB that was mostly OK (well, a lot better than before).

    Need less to say a thorough survey was done of all the backup methods used by the school we were involved with and the scripts to stop / start the service were removed and new scripts based on DBAttach were used instead. Recovery testing was also conducted to ensure that worked. It did.

    Some online backup solutions will call DBAttach themselves to ensure the DB is backed up correctly. Smart move!

  10. #40
    NorthernSands's Avatar
    Join Date
    Mar 2011
    Location
    Qatar
    Posts
    134
    Thank Post
    21
    Thanked 19 Times in 19 Posts
    Rep Power
    10
    Quote Originally Posted by vikpaw View Post
    I never fully updated my findings because i ran out of time and over Easter needed to get the server up and running. I did a detach and reattach of my databases and didnt' lose any logins, as should be the case.

    I think, that from now on, if i restore my new backup files, to a test server the logins will be pushed there, as i first thought, but the best test would be a brand new server with a clean master db. I'll see if i get time to do those tests later.
    To add to this, I have experience with trusted logins. When we migrated to a new Server 2008 R2 and SQL 2008 R2, I installed SIMS from scratch on the new server, which was already attached to the domain, and used DBAttach to bring in the SIMS MDF / LDF (not the master DB, or any other) that I'd correctly unattached from the old server. I used the raw DB files rather than a backup 'cause I wanted to change the name of the DB from SIMS2008 to SIMS. I did not receive an error message on attachment regarding the trusted logins and they all worked fine.

    I have, on other occasions, pulled a live backup from the server and restored it to my workstation for testing. Despite being on the same Domain, I did receive the error message regarding trusted logins that @vikpaw is referring to. I can't tell you if this is because it's a workstation and not a server, or because of the method used to bring the DB over, but in both instances the account used to login into Windows was the same and has local administrator access only, not domain admin access (in case that's relevant).

  11. #41

    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
    As NorthernSands says, stopping the SQL service and backing up the files is a terrible idea.
    You should backup the database, not the underlying files.

    You can do this with relatively simple piece of TSQL coding, using a job running on the server's scheduler.

    This might be worth a look: Download Details - Microsoft Download Center - Microsoft SQL Server 2008 Management Studio Express

    It might be possible to use the 'generate script' feature in the above studio to give you the TSQL code which you can call from an OSQL command script.
    Last edited by jinnantonnixx; 28th June 2011 at 10:31 AM.

  12. #42

    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
    If anybody's interested, here's a bit of code I wrote to do a manual backup (I must have had a reason).

    You could set this to run in the server's regular scheduler, (as matt pointed out there's no SQL server agent in Express which is a shame).

    Don't forget to put the full file path to OSQL, as there's no 'PATH' as far as the scheduler's concerned. Every file must be called or referred to by its full file path.

    Code:
    @echo off
    cls
    rem script to backup SIMS sql database
    rem v1.1 2/2/05
    for /f "tokens=1" %%a in ('date /t') do set Today=%%a
    set Today=%Today:/=-%
    set file=C:\Program Files\Microsoft SQL Server\MSSQL$SIMS\BACKUP\%1_%Today%_Backup.BAK
    if $%1 NEQ $ (
    	echo Backing up database %1...
    	OSQL -S <insertSQLserverhere>\SIMS -U sa -P <insert_sa_password_here> -d master -Q "BACKUP DATABASE [%1] TO DISK = '%file%'  WITH  NOUNLOAD ,  NAME = '%1',  NOSKIP ,  NOFORMAT"
    	echo Backup file on server is: %file%
    ) else (
    	echo Usage SQLBack [name of SIMS database]
    	echo e.g. SQLBack ABC
    )
    It takes the database name as a parameter. You could strip out the guts and just use the main OSQL bit if you just needed to backup one or two databases.
    Last edited by jinnantonnixx; 28th June 2011 at 01:31 PM.

  13. #43

    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
    There's a bit of buzz about this, but I haven't tried it. The feature chart says the free version will work with up to two databases, so in a small set-up it might be ideal.

    Free MySQL database backup software

  14. #44

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,386
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    What's wrong with dbAttach?

    The free version doesn't encrypt and it doesn't FTP securely. So for god sake don't FTP to a external site!

  15. #45

    Join Date
    Apr 2010
    Posts
    2,036
    Thank Post
    83
    Thanked 187 Times in 154 Posts
    Rep Power
    83
    why does the browser tab say mysql backup? but the site says sql backup.

SHARE:
+ Post New Thread
Page 3 of 4 FirstFirst 1234 LastLast

Similar Threads

  1. [SIMS] sims.net backup
    By itgeek in forum MIS Systems
    Replies: 2
    Last Post: 3rd April 2011, 05:12 AM
  2. [SIMS] Sims backup regimes
    By creese in forum MIS Systems
    Replies: 6
    Last Post: 13th September 2010, 09:52 AM
  3. sims backup
    By arthur231283 in forum MIS Systems
    Replies: 20
    Last Post: 20th May 2009, 06:45 PM
  4. Sims Backup - Advice
    By pooley in forum MIS Systems
    Replies: 15
    Last Post: 27th March 2009, 12:33 PM
  5. SIMS Backup Time
    By ninjabeaver in forum MIS Systems
    Replies: 6
    Last Post: 16th January 2009, 10:40 AM

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
  •