[sims] Sims Backup

[squeeky]

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 Edited February 28, 2011 by squeeky

[Jamman960]

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.

 

del d:\SIMSBackup\today\*.rar
"D:\Program Files\Microsoft SQL Server\MSSQL10.\MSSQL\Binn\DbAttach.exe" /BACKUP /SERVER="\" /USER="sa" /PASSWORD="passwordhere" /DATABASE="sims" /AUTO /PATH="d:\SIMSBackup\today\sims.bak"
"D:\Program Files\Microsoft SQL Server\MSSQL10.\MSSQL\Binn\DbAttach.exe" /BACKUP /SERVER="\"  /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 -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 -rv10 -m5 d:\docstorage
copy d:\simsbackup\today\*.rar \\\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

Edited February 28, 2011 by Jamman960

[arthur231283]

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

[cromertech]

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.

[superfletch]

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"

Edited February 28, 2011 by superfletch
Add commands

[Cools]

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

[bladedanny]

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

[timbo343]

I use the following:

 

Batch File:

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

DECLARE @filename nvarchar(128)

SET @filename=N'E:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Backup\[i]DATABASENAME[/i]_cp361_mnw_' + convert(varchar(8), getdate(), 112) + '_' + replace(convert(varchar(8), getdate(), 108), ':', '') + '.bak'

BACKUP DATABASE [[i]DATABASENAME[/i]] TO  DISK = @filename WITH NOFORMAT, INIT,  NAME = N'[i]DATABASENAME[/i] 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.

[CAM]

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.

[superfletch]

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.

[Jobos]

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?

[GREED]

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.

[vikpaw]

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.

[vikpaw]

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.

[matt40k]

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.

[vikpaw]

Reading the really useful document that CAM linked to has answered my questions. It's so useful i think it's worth attaching here for those that can't be bothered to access SupportNet or for various reasons have no access or are not allowed access.

 

I recommend everyone to read this really useful document.

 

In short, regarding my queries:

 

A dbattach backup, does not move the logins into the mdf.

Previously moved logins, will stay in the mdf and are not cleared.

 

I don't think any of our users ever change their passwords unless they have forgotten them and I do it. So a once detached DB will pretty much have all logins stored within it apart from any new users. So there is no fear if you are using dbattach for backups, most users will be retained. The only pain is if your users regularly change passwords through choice or your enforcement.

 

Does anyone know how a trusted login is stored?

BackupSIMSandFMS_14931.pdf

[vikpaw]

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.

Good to know about the order, not sure what order my maintenance task runs them in as i select all DBs in the dropdown. I'll have to check file times now!

Not clear on your point about dbattach backups, but i've confirmed it doesn't transfer logins.

I didn't think the log file was used anymore, or am i getting terminology mixed up again... I'm thinking about the ldf..

[matt40k]

ldf (mssql logs) shouldn't grow if it's set to SIMPLE, but if your using something like RedStor, it won't be.

 

I'm moaning at Capita about the transferring of logins when you do a dbattach backup, I suggest others do as well, might be too late for Spring, but hopefully they can do it by Summer.

[vikpaw]

ldf (mssql logs) shouldn't grow if it's set to SIMPLE, but if your using something like RedStor, it won't be.

 

I'm moaning at Capita about the transferring of logins when you do a dbattach backup, I suggest others do as well, might be too late for Spring, but hopefully they can do it by Summer.

 

I understood, the ldf is not used anymore period, but i guess thats based on being in SIMPLE mode which is their recommendation right.

 

I've just been saying in another thread, about moving sims server, with GREED and others, that we should log a change request for dbattach backup to automatically do the logins transfer, and also have that command available as a command line option on it's own so it's more useful to us techs. Running the stored proc itself will never be acceptable by Capita but if they compile it into the utility we can all stay on the right side of the line

[matt40k]

What's the change request no?

 

PS: I hope a certain person is reading this.

[vikpaw]

What's the change request no?

 

PS: I hope a certain person is reading this.

 

Haven't done it yet, was just knocking about the idea. At home now. Wanna do the honours...?

 

I know what you mean though as sometimes a tweak like this doesn't need a change request, just that the right person gets wind of it...

 

PerHaps If Luck was oN our sidE we might be AbLe to get some joy sooner.

 

[superfletch]

PerHaps If Luck was oN our sidE we might be AbLe to get some joy sooner.

 

Awesome!!

 

If you do set up a CR let me know the number and I'll vote for it.

[vikpaw]

What's the change request no?

 

PS: I hope a certain person is reading this.

 

Awesome!!

 

If you do set up a CR let me know the number and I'll vote for it.

 

PLEASE GO VOTE FOR CHANGE REQUEST # 1103-855974

 

http://support.capitaes.co.uk/ChangeRequest/DetailChangeRequest.aspx?id=%c3%97%c3%a1%c3%92%c3%9c%c3%a8%c3%a1%c3%90%c3%9f%c3%9f%c3%9d%c3%95

 

and ignore the blatant typo in first line.

 

PerHaps If Luck was oN our sidE we might be AbLe to get some joy sooner.

[vikpaw]

Here's a question.

I've used the all new fancy upgraded DBAttach to backup my db and the logins are transferred to the db.

I restore to brand new disaster recovery server A.

The logins wont work at this point, until i run the install procedure, right?

Need to have that as an optional command on the DBAttach too.

 

What would happen if i just detach and then reattach the db in it's new location. I think that it would work, as that often fixes things for people, however if i had to guess i would expect that from a blank master the table in the mdf would be cleared / overwritten, as though it was being updated... views anyone..

[vikpaw]

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!).

 

matt can you please expand more on this ordering business. i'm backing up both as part of the same maintenance plan, and fms being alphabetically first is running first by about 2 secs. The backup takes around 90 secs. What flavor of impending doom should i expect?

 

Also, who only weakly voted for the change request? Somebody doesn't really want the logins to work that badly ...?