+ Post New Thread
Page 1 of 4 1234 LastLast
Results 1 to 15 of 46
MIS Systems Thread, Sims Backup in Technical; Hi, Quick question really. How do you backup sims ? the database is obviously in use so I need to ...
  1. #1

    Join Date
    May 2009
    Posts
    241
    Thank Post
    32
    Thanked 10 Times in 10 Posts
    Rep Power
    12

    Sims Backup

    Hi, Quick question really. How do you backup sims ? the database is obviously in use so I need to stop the services?? and backup. I need to make sure this is working by the end of the day because you can never tell and we have just had a new server installed. Windows backup is running on the shared network drives but i dont think this is adequate for sims
    Last edited by squeeky; 28th February 2011 at 08:27 AM.

  2. #2
    Jamman960's Avatar
    Join Date
    Sep 2007
    Location
    London/Kent
    Posts
    987
    Thank Post
    186
    Thanked 194 Times in 156 Posts
    Rep Power
    45
    Using DBAttach you can run backups while the database is in use, I currently backup the database daily, compress the file and upload it to our backup server.

    You also need to backup the DocStorage folder, I just compress and upload daily.

    Code:
    del d:\SIMSBackup\today\*.rar
    "D:\Program Files\Microsoft SQL Server\MSSQL10.<instancename>\MSSQL\Binn\DbAttach.exe" /BACKUP /SERVER="<servername>\<instance>" /USER="sa" /PASSWORD="passwordhere" /DATABASE="sims" /AUTO /PATH="d:\SIMSBackup\today\sims.bak"
    "D:\Program Files\Microsoft SQL Server\MSSQL10.<instancename>\MSSQL\Binn\DbAttach.exe" /BACKUP /SERVER="<servername>\<instance>"  /USER="sa" /PASSWORD="passwordhere" /DATABASE="fms" /AUTO /PATH="d:\SIMSBackup\today\fms.bak"
    "c:\program files\winrar\rar.exe" a d:\SIMSBackup\today\databasebackup.rar -ri1 -mt2 -ag[yyyy-mm-dd] -p<password> -rv10 -m5 d:\SIMSBackup\today\*.bak
    "c:\program files\winrar\rar.exe" a d:\SIMSBackup\today\Docstoragebackup.rar -ri1 -mt2 -ag[yyyy-mm-dd] -p<password> -rv10 -m5 d:\docstorage
    copy d:\simsbackup\today\*.rar \\<backupserver>\share\wherever
    del d:\SIMSBackup\today\sims.bak
    del d:\SIMSBackup\today\fms.bak
    The above script creates the backups, compresses them into a date stamped & passworded rar files and then uploads to wherever your backups are.

    James
    Last edited by Jamman960; 28th February 2011 at 08:38 AM.

  3. #3
    arthur231283's Avatar
    Join Date
    Sep 2008
    Location
    Dartford
    Posts
    164
    Thank Post
    34
    Thanked 37 Times in 23 Posts
    Rep Power
    18
    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

  4. #4
    cromertech's Avatar
    Join Date
    Dec 2007
    Location
    Cromer by the coast
    Posts
    731
    Thank Post
    177
    Thanked 109 Times in 97 Posts
    Rep Power
    54
    I'm a little paranoid on this and I back up the whole system with windows server backup on a daily incremental. The DBAttach runs as well to give me a backup of the database too.

  5. #5
    superfletch's Avatar
    Join Date
    Nov 2007
    Location
    South
    Posts
    434
    Thank Post
    146
    Thanked 77 Times in 61 Posts
    Rep Power
    31
    Quote Originally Posted by arthur231283 View Post
    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.
    That last line should read:

    You will need to replace "SQL Server (SIMS2008)" with the named instance running the SIMS and FMS Databases.


    You also need to add some lines to the start of your script that transfer logins from the master.mdf into the SIMS.mdf (I think). Assuming you are running this scheduled task on the SQL Server in question itself as "administrator" and your databases are actually called "sims" and "fms"then these lines will do the job.

    osql -S%computername%\sims2008 -E -n -Q"exec sims.sims.db_p_transfer_login"
    osql -S%computername%\sims2008 -E -n -Q"exec FMS.sims.db_p_transfer_login"
    Last edited by superfletch; 28th February 2011 at 09:24 AM. Reason: Add commands

  6. #6
    Cools's Avatar
    Join Date
    Jan 2009
    Location
    Bedfordshire
    Posts
    498
    Thank Post
    24
    Thanked 62 Times in 57 Posts
    Rep Power
    25
    i use this command line in a batch script takes seconds to export the DB
    just added it to system scheduling to run with admin privs to export every night


    "D:\Program Files\Microsoft SQL Server\MSSQL10.SIMS2008\MSSQL\Binn\DbAttach.exe" /BACKUP /AUTO /SERVER=%computername%\sims2008 /DATABASE=SIMS /USER=sa /PASSWORD=%simspassword% /PATH=d:\backup\SIMS_Backup.BAK

  7. #7

    bladedanny's Avatar
    Join Date
    May 2009
    Location
    Sheffield
    Posts
    1,268
    Thank Post
    188
    Thanked 298 Times in 224 Posts
    Rep Power
    130
    Quote Originally Posted by Cools View Post
    i use this command line in a batch script takes seconds to export the DB
    just added it to system scheduling to run with admin privs to export every night


    "D:\Program Files\Microsoft SQL Server\MSSQL10.SIMS2008\MSSQL\Binn\DbAttach.exe" /BACKUP /AUTO /SERVER=%computername%\sims2008 /DATABASE=SIMS /USER=sa /PASSWORD=%simspassword% /PATH=d:\backup\SIMS_Backup.BAK
    I also use this method. With an added robocopy script to copy it to our backup NAS drive so we have it in two places..

  8. #8
    timbo343's Avatar
    Join Date
    Dec 2005
    Location
    Leeds/York area, North Yorkshire
    Posts
    2,991
    Thank Post
    287
    Thanked 281 Times in 195 Posts
    Rep Power
    117
    I use the following:

    Batch File:
    Code:
    sqlcmd -S HERA\SQL2008 -i E:\SIMSBackupScripts\SQLFiles\SIMSBackup.sql -o E:\SIMSBackupScripts\SQLFiles\SIMSBackup.txt
    This batch file calls the following SQL Command:

    SQL Command
    Code:
    DECLARE @filename nvarchar(128)
    
    SET @filename=N'E:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup\DATABASENAME_cp361_mnw_' + convert(varchar(8), getdate(), 112) + '_' + replace(convert(varchar(8), getdate(), 108), ':', '') + '.bak'
    
    BACKUP DATABASE [DATABASENAME] TO  DISK = @filename WITH NOFORMAT, INIT,  NAME = N'DATABASENAME Full Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    
    GO
    Once that has run, i run an exe called tidybackups which compresses the bak files into zip files.

    We keep the zipped bak files for about 10 days.

    Also, we run Backup Exec every week day on a full backup.

  9. #9
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Burgh Heath, Surrey
    Posts
    4,076
    Thank Post
    812
    Thanked 353 Times in 277 Posts
    Blog Entries
    60
    Rep Power
    280
    Supportnet has a PDF file detailing how to backup files. Resource number is 14931.

    Regarding backing up logins, if you detatch and reattatch the database using dbAttatch it will store logins if the database has been detatched before.

    Aloso remember if you run FMS, You must backup SIMS and FMS at the same time! If one needs to be restored, the other also has to be restored to the same point in time.

  10. 2 Thanks to CAM:

    superfletch (1st March 2011), vikpaw (1st March 2011)

  11. #10
    superfletch's Avatar
    Join Date
    Nov 2007
    Location
    South
    Posts
    434
    Thank Post
    146
    Thanked 77 Times in 61 Posts
    Rep Power
    31
    Quote Originally Posted by CAM View Post
    You must backup SIMS and FMS at the same time!
    Brilliant point that nobody else picked up on which we should all be aware of.

  12. #11
    Jobos's Avatar
    Join Date
    Apr 2007
    Posts
    1,133
    Thank Post
    178
    Thanked 49 Times in 42 Posts
    Rep Power
    24
    Quote Originally Posted by CAM View Post
    You must backup SIMS and FMS at the same time!
    When you say at the same time do you mean backup SIMS and then FMS one after the other?

  13. #12

    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
    Quote Originally Posted by CAM View Post
    Supportnet has a PDF file detailing how to backup files. Resource number is 14931.

    Regarding backing up logins, if you detatch and reattatch the database using dbAttatch it will store logins if the database has been detatched before.

    Aloso remember if you run FMS, You must backup SIMS and FMS at the same time! If one needs to be restored, the other also has to be restored to the same point in time.
    Unless this has changed wildly, this is only needed if you integrate between the two with a particular routine (which I cannot remember, apologies!). This is not needed unless you have this routine in place.

  14. #13

    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
    Quote Originally Posted by GREED View Post
    Unless this has changed wildly, this is only needed if you integrate between the two with a particular routine (which I cannot remember, apologies!). This is not needed unless you have this routine in place.
    Quite right Graham, i think this is if you are using FMS linked to Personnel in SIMS for salary projections, or it may be another finance related link. I will check shortly to confirm.

    Suffice it to say, you just need to back up both DBs at roughly the same time, so one after the other is fine, you just don't want a gap of hours or days where changes made to one wont be reflected in the other. If you're backing up during down time, i.e. overnight this shouldn't be an issue, it doesn't need to be contemporaneous.

    As for how to backup, i use the built-in SQL backups via SQL management studio, as i don't like the idea of scripting the services to stop and start. It also emails me on success or failure. There is another thread with a detailed explanation of how to set this up, but it's not in the MIS forum, but should be searchable.

    On the issue of logins, can one of our resident DB experts confirm whether a dbattach backup actually transfers those logins to the mdf as well, or is that only part of the detach routine. This is a key point. Also, i'd be interested to know if those logins are kept in the mdf upon reattach or if the table is cleared...? Again, a vital point depending on your strategy.

    I need to research these for when i move to a new Windows 2008 server. For now, i'm happy that if i restore to my current server the logins are already stored there, and that process is only required if you are moving the db to a new SQL installation.

    However, for failover, as well as backing up the sims and fms i also backup master and temp. I believe restoring master too in case of corruption would be sufficient. Not sure what happens if you restore a master from one server to another though...

    Worst case you lose your logins, in a disaster recovery scenario, it's a pain, but more important that you have a working backup that brings your data back. You can always reset passwords.

    So whatever strategy you choose, i would periodically pick a file, uncompress if packed, and then restore to a test server. Even if you have to reset passwords, just make sure your data is there, and it works.

    I get confirmation of this when i send data in to Capita for fixes, but otherwise would do this to a virtual test server.

  15. Thanks to vikpaw from:

    GREED (2nd March 2011)

  16. #14

    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
    This is taken from Autumn release notes, but is present in all the recent ones i can remember:

    Backing up SIMS and FMS
    Where SIMS Personnel is used to generate Salary Projections in FMS, SIMS
    and FMS backups should be carried out at the same time. If one of the
    databases needs to be restored, the other database must be restored to the
    same point, i.e. if you restore the SIMS database, you must restore the FMS
    database to the same point and vice versa.

  17. #15

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,386
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    SIMS must be backed up first, then FMS. Chaos will follow if not, come restore time - assuming your using FMS. dbAttach, I believe, doesn't transfer the logins when it backs up - it will. If your database is set to simple, the logs aren't an issue, but if your doing diff backups - ie the database is set to full, you'll need to truncate the log file if your using SQL maintainance task (after the backup of course!).

    FYI: WinRar isn't free. Use 7zip or tidybackups.

  18. Thanks to matt40k from:

    superfletch (2nd March 2011)

SHARE:
+ Post New Thread
Page 1 of 4 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
  •