Scripted backup for SIMS and FMS

[ctbjs]

Hi

 

I'm looking for a vbs or a PowerShell script that will allow me to backup both SIMS and FMS databases into a bak file.

 

Does anyone have anything like this available. I don't really want to use a batch file for this.

 

Many thanks in advance

 

Chris

[Boredguy]

you mean something like

# Common SIMS Backup Variables.
$BackupPath = "D:\Backups"
$SAPassword = "?blah?"
$DBServer = "localhost\sims2014"
$DBAttach = "d:\program files\microsoft sql server\mssql12.sims2014\mssql\binn\DbAttach.exe"

# Backup SQL databases and copy to backup share.
Write-Host "Perform Backup of SQL databases and copy to backup..."
Import-Module SQLPS -DisableNameChecking
Invoke-Sqlcmd -ServerInstance $DBServer -Username "Sa" -Password $SAPassword -Query "exec sims.sims.db_p_transfer_login"
Start-Sleep -s 30
Invoke-Sqlcmd -ServerInstance $DBServer -Username "Sa" -Password $SAPassword -Query "exec ccsfms.sims.db_p_transfer_login"
Start-Sleep -s 30

&$DBAttach /BACKUP /AUTO /SERVER=$DBServer /DATABASE=SIMS /USER=sa /PASSWORD=$SAPassword /PATH=$BackupPath\SIMS.BAK | Out-Host
&$DBAttach /BACKUP /AUTO /SERVER=$DBServer /DATABASE=CCSFMS /USER=sa /PASSWORD=$SAPassword /PATH=$BackupPath\FMS.BAK | Out-Host
&$DBAttach /BACKUP /AUTO /SERVER=$DBServer /DATABASE=Solus3_deployment_server /USER=sa /PASSWORD=$SAPassword /PATH=$BackupPath\SOLUS.BAK | Out-Host
&$DBAttach /BACKUP /AUTO /SERVER=$DBServer /DATABASE=Discover /USER=sa /PASSWORD=$SAPassword /PATH=$BackupPath\Discover.BAK | Out-Host

[Esteban_Child_of_the_Sun]

If you want Discover to be more easily restored on anything other than an exact duplicate of the system it came from you may find this a bit more useful. (Plus it lets you correct some errors that can occur in Discover by editing the backup file and restoring back to your system)

 

$discoBackupExe = $SQLInstPath+'\Binn\Discover.Backup.Console.exe'

# Backup Discover, writes to a config file

$discoFile = "{0}\Discover\{1}-{2}-Disco.dbk" -f $backupDir, $site, $DoTW

&$discoBackupExe /operation=backup /server=$instance /database=Discover /backupfilename=$discoFile /overwrite=true | Out-Null

[ctbjs]

Thank you both for your input, very much appreciated.

[ctbjs]

@Boredguy

 

When you run the script for your database files does that overwrite any previous .bak files within the d:\backups folder or do you manually remove the old copy first?

[Boredguy]

We have part of the script further on that copies the created files to a remote directory so we basically just over write the local copies.

[ctbjs]

you mean something like

# Common SIMS Backup Variables.
$BackupPath = "D:\Backups"
$SAPassword = "?blah?"
$DBServer = "localhost\sims2014"
$DBAttach = "d:\program files\microsoft sql server\mssql12.sims2014\mssql\binn\DbAttach.exe"

# Backup SQL databases and copy to backup share.
Write-Host "Perform Backup of SQL databases and copy to backup..."
Import-Module SQLPS -DisableNameChecking
Invoke-Sqlcmd -ServerInstance $DBServer -Username "Sa" -Password $SAPassword -Query "exec sims.sims.db_p_transfer_login"
Start-Sleep -s 30
Invoke-Sqlcmd -ServerInstance $DBServer -Username "Sa" -Password $SAPassword -Query "exec ccsfms.sims.db_p_transfer_login"
Start-Sleep -s 30

&$DBAttach /BACKUP /AUTO /SERVER=$DBServer /DATABASE=SIMS /USER=sa /PASSWORD=$SAPassword /PATH=$BackupPath\SIMS.BAK | Out-Host
&$DBAttach /BACKUP /AUTO /SERVER=$DBServer /DATABASE=CCSFMS /USER=sa /PASSWORD=$SAPassword /PATH=$BackupPath\FMS.BAK | Out-Host
&$DBAttach /BACKUP /AUTO /SERVER=$DBServer /DATABASE=Solus3_deployment_server /USER=sa /PASSWORD=$SAPassword /PATH=$BackupPath\SOLUS.BAK | Out-Host
&$DBAttach /BACKUP /AUTO /SERVER=$DBServer /DATABASE=Discover /USER=sa /PASSWORD=$SAPassword /PATH=$BackupPath\Discover.BAK | Out-Host

 

Can you confirm that you run this as a vbs script. I've just run it and it faults with error 1 line 1 (invalid character) 80DA0408

[Arthur]

Can you confirm that you run this as a vbs script.

It's a PowerShell script (.ps1).

[ctbjs]

We have part of the script further on that copies the created files to a remote directory so we basically just over write the local copies.

 

Hi, the script works great in terms of running and processing the bak files to the specified location and have it working as a scheduled task. The script itself doesn't overwrite the files if they already exist in the backups folder. What exactly do you do in this case to ensure that you have the most current set in the backups folder.

 

Essentially what I'm looking to do is to create a backup set in a folder called backups that updates at a set time daily. At a later time on that day we will then upload the "backups" folder to the cloud containing the latest backup copy.

 

I suppose I could also add a line to the script that deletes the files prior to the backup starting, any ideas on how you would do this?

 

Chris

[ctbjs]

I got around the issue of overwriting by adding the following at the start of the script

 

get-childitem e:\backups -include *.BAK -recurse | foreach ($_) {remove-item $_.fullname}

 

It removes all *.BAK files allowing them to be re-written. Works a treat.

[Arthur]

get-childitem e:\backups -include *.BAK -recurse | foreach ($_) {remove-item $_.fullname}

You could simplify that to the following...

 

Get-ChildItem e:\backups -Filter *.BAK -File -Recurse | Remove-Item

 

There isn't any need to use ForEach in this instance because the Remove-Item cmdlet gets all of the info it needs from Get-ChildItem (inc. the full path to each .bak file).

[Boredguy]

It appears that earlier in the code we have the following commands

Write-Host "Cleaning up previous database backups."
If (Test-Path "$BackupPath\SIMS.BAK")
{
   Remove-Item ("$BackupPath\*.BAK")
}

 

Our script has lots of extra bits and pieces as it checks RoboCopy logs as well and e-mails us the backup success/failure each night, as well as connecting to our remote backup server to copy the files.