Restore Test for FMS

quaium · November 27, 2018

Yup! Schema is just like a folder. If you don't set the database, it defaults to the current database and if you don't a schema it defaults to dbo. dbo is the fallback, for me its my Desktop

Great, makes no sense to me but thanks. One final question... the SIMS and FMS database is also backed up using Backup Exec, running this routine before hand shouldn't have any effect on that should it?

TwistedHelixis · November 27, 2018

I have a few questions based on the above script but will wait until you have everything working before asking.

matt40k · November 28, 2018

Great, makes no sense to me but thanks. One final question... the SIMS and FMS database is also backed up using Backup Exec, running this routine before hand shouldn't have any effect on that should it?

Nope. It just creates a (full) copy. Might want to check you aren't backing it up however, otherwise you could fill your backups with backups of backups of backups

quaium · November 28, 2018

Tried the script last night and it worked fine. Managed to get the backups on to the test laptop and the FMS database restored with logins intact.

Also an important note to anyone trying this... get the latest installation files from Capita! I spent days using an old DBAttach which was throwing out errors because I had copied over files from our live SIMS server.

Thank you for the help and advice everyone.

TwistedHelixis · November 28, 2018

Hope its OK to ask a few questions now you seem to have it all working.

Sorry these might seem very basic but I have never got my head round backing up Sims or FMS and have just relied on the LA scripts, which do not even have db_p_transfer_login. and the fact I do a VM backup of the entire server.

Q1. I was told DBattach does move the master users database over, is that just the GUI version?

Q2. Does the script db_p_transfer_login need to be run with every backup or can I run it once manually as a separate script, and from then on the sims & fms databases will always have the master database users?

Q3. The script to transfer to sims is 'sims.sims.db_p_transfer_login' so should the fms one be fms.fms.db_p_transfer_login?

Q4. From the backup script do I need the line? - Import-Module SQLPS -DisableNameChecking - What does it do?

MikeW · November 28, 2018

Here's mine if its any use - archives previous backups and compresses them

# Set Variables

$server = 'SERVER\INSTANCE'
$user = 'user'
$password = 'password'
$date = (Get-Date).ToString("d-M-yyyy").Replace(",","-")

# Clear Archive Folders Of Files Older Than 7 Days
Get-ChildItem –Path  “Z:\Archive\Databases\SIMS” –Recurse | Where-Object{$_.CreationTime –lt (Get-Date).AddDays(-7)} | Remove-Item
Get-ChildItem –Path  “Z:\Archive\Databases\FMS” –Recurse | Where-Object{$_.CreationTime –lt (Get-Date).AddDays(-7)} | Remove-Item
Get-ChildItem –Path  “Z:\Archive\Databases\Discover” –Recurse | Where-Object{$_.CreationTime –lt (Get-Date).AddDays(-7)} | Remove-Item
Get-ChildItem –Path  “Z:\Archive\Databases\SOLUS” –Recurse | Where-Object{$_.CreationTime –lt (Get-Date).AddDays(-7)} | Remove-Item
Get-ChildItem –Path  “Z:\Archive\Folders” –Recurse | Where-Object{$_.CreationTime –lt (Get-Date).AddDays(-7)} | Remove-Item

# Move Last Backup To The Relevant Archive folder
Move-Item "Z:\Current\Databases\SIMS*" "Z:\Archive\Databases\SIMS\"
Move-Item "Z:\Current\Databases\FMS*" "Z:\Archive\Databases\FMS\"
Move-Item "Z:\Current\Databases\Discover*" "Z:\Archive\Databases\Discover\"
Move-Item "Z:\Current\Databases\SOLUS*" "Z:\Archive\Databases\SOLUS\"
Move-Item "Z:\Current\Folders\*.zip" "Z:\Archive\Folders\"

# Shrink Databases To Keep The File Size Low
Set-Location -Path 'F:\Apps\SIMS\Setups\ShrinkDBLog'
.\ShrinkDBLog.bat $server SIMS
.\ShrinkDBLog.bat $server FMS
.\ShrinkDBLog.bat $server Discover
.\ShrinkDBLog.bat $server solus3_deployment_server

# Transfer Users From Master Database
OSQL -S"$server" -U $user -P $password -Q"exec sims.sims.db_p_transfer_login"
OSQL -S"$server" -U $user -P $password -Q"exec fms.sims.db_p_transfer_login"

# Backup The Sims Database Into The 'Current' Folder
Set-Location -Path 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\Binn'
.\DbAttach.exe /server=$server /database=SIMS /user="$user" /password="$password" /backup /auto /path="Z:\Current\Databases\SIMS-$date.bak"

# Backup The Discover Database Into The Current Folder
.\Discover.Backup.Console.exe /operation=backup /database=Discover /username="$user" /password="$password" /server=$server /backupfilename=Z:\Current\Databases\Discover-$date.dbk

# Backup The FMS Database Into the 'Current' Folder
Set-Location -Path 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\Binn\FMS'.\DbAttach.exe /server=$server /database=FMS /user="$user" /password="$password" /backup /auto /path="Z:\Current\Databases\FMS-$date.bak"

# Backup The SOLUS Database Into the 'Current' Folder
Set-Location -Path 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\Binn'.\DbAttach.exe /server=$server /database=solus3_deployment_server /user="$user" /password="$password" /backup /auto /path="Z:\Current\Databases\SOLUS-$date.bak"

#Wait For 15 Minutes Whilst Database Backups Completes
Start-Sleep -s 900

# Zip Files To Save Space And Then Delete Originals
Set-Location -Path C:\Windows\System32
$files = Get-Item "Z:\Current\Databases\*.bak" | Foreach-Object {$_ -replace ".bak", ""}
foreach ($file in $files)    
{ .\7za a -tzip $file'.zip' $file'.bak'
}
Remove-Item "Z:\Current\Databases\*.bak"

# Zip SIMS And DOCSTORAGE Folders
.\7za.exe a -tzip Z:\Current\Folders\DOCSTORAGE-$date.zip F:\DOCSTORAGE
.\7za.exe a -tzip Z:\Current\Folders\SIMSFILES-$date.zip F:\Apps\SIMS -mx0 -xr!Solus3 -xr!Setups

Edited November 28, 2018 by MikeW

quaium · November 28, 2018

Hope its OK to ask a few questions now you seem to have it all working.

Sorry these might seem very basic but I have never got my head round backing up Sims or FMS and have just relied on the LA scripts, which do not even have db_p_transfer_login. and the fact I do a VM backup of the entire server.

Q1. I was told DBattach does move the master users database over, is that just the GUI version?

Q2. Does the script db_p_transfer_login need to be run with every backup or can I run it once manually as a separate script, and from then on the sims & fms databases will always have the master database users?

Q3. The script to transfer to sims is 'sims.sims.db_p_transfer_login' so should the fms one be fms.fms.db_p_transfer_login?

Q4. From the backup script do I need the line? - Import-Module SQLPS -DisableNameChecking - What does it do?

Q1. DBAttach can backup the master but you're right only via the GUI, or if I am correct if no destination is provided in the script. For example, if "$SIMSDBAttach /BACKUP /AUTO /SERVER=$SIMSDBServer /DATABASE=SIMS /USER=sa /PASSWORD=$SAPassword /PATH=$BackupPath\SIMS.BAK" is used you'd get a SIMS.bak file in the destination provided. However, if you used "$SIMSDBAttach /BACKUP /AUTO /SERVER=$SIMSDBServer /DATABASE=SIMS /USER=sa /PASSWORD=$SAPassword" you would get a backup of the master, msdb and the sims/fms database in the backup folder of your SQL installation.

Q2. I'm not sure about this but would be interesting to find out.

Q3. The .sims.db_p_transfer_login part stays the same for both, you just need to add the database name to the start of it, for example, sims.sims.db_p_transfer_login or fms.sims.db_p_transfer_login

Q4. I'm not sure about this too, I just used the script provided on here and modified it a little.

TwistedHelixis · November 28, 2018

Sorry, a couple more questions

Q5 - My server is vm-data-01 the sql database is sql2012 and then I have SIMS / FMS databases

Invoke-Sqlcmd -ServerInstance $SIMSDBServer -Username "sa" -Password $SAPassword -Query "exec sims.sims.db_p_transfer_login"

Invoke-Sqlcmd -ServerInstance $FMSDBServer -Username "sa" -Password $SAPassword -Query "exec fmsacad.sims.db_p_transfer_login"

In the above script it has the following lines $SIMSDBServer or $FMSDBServer

So would my script be

vm-data-01\sql2012 for both

or

vm-data-01\sims

vm-data-01\fms

or

vm-data-01\sql2012\Sims

vm-data-01\sql2012\Fms

Q6 - I notice it does say -serverInstance but cant workout how it would know the instance name, as it has not been defined in the script as a $

quaium · November 28, 2018

Sorry, a couple more questions

Q5 - My server is vm-data-01 the sql database is sql2012 and then I have SIMS / FMS databases

In the above script it has the following lines $SIMSDBServer or $FMSDBServer

So would my script be
vm-data-01\sql2012 for both

or

vm-data-01\sims
vm-data-01\fms

or

vm-data-01\sql2012\Sims
vm-data-01\sql2012\Fms

Q6 - I notice it does say -serverInstance but cant workout how it would know the instance name, as it has not been defined in the script as a $

All the definations are defined at the start of the script...

# Common SIMS/FMS Backup Variables.

$BackupPath = "D:\Backups"

$SAPassword = "PASSWORD"

$SIMSDBServer = "SERVERSIMS\SIMS"

$SIMSDBAttach = "D:\Program Files\Microsoft SQL Server\MSSQL11.SIMS2012\MSSQL\Binn\DbAttach.exe"

$FMSDBServer = "SERVERSIMS\FMS"

$FMSDBAttach = "D:\Program Files\Microsoft SQL Server\MSSQL11.FMS2012\MSSQL\Binn\FMSSQL\DbAttach.exe"

So anywhere $SIMSDBServer is used it will equal what you have defined.

You're $SIMSDBServer or $FMSDBServer would vm-data-01\sql2012, the sql2012 is the instance name and you would need to find that out.

TwistedHelixis · November 28, 2018

sql2012 is the instance name

So are you saying $SIMSDBServer = "vm-data-01\sql2012"

quaium · November 28, 2018

Yes, that's correct.

TwistedHelixis · November 28, 2018

Would the script work as a powershell script or just bat?

quaium · November 28, 2018

Its a Powershell script (.ps1) it won't work as a batch file.

PurpleTentacle · November 28, 2018

If the FMS database works the same way as the SIMS database, when the database is moved to a new server all of the passwords get reset.

matt40k · November 28, 2018

If the FMS database works the same way as the SIMS database, when the database is moved to a new server all of the passwords get reset.

That's the idea of using the transfer login stored procedure before backing up... so it doesn't reset the passwords

TwistedHelixis · November 28, 2018

Think I am all done, thanks.

As my LA do not want me adding to their Sims / FMS backup script, I have created a standalone script that just transfers the logins, I will run this every now and then, or add to a scheduled task but assume it does not need to run every night.

quaium · November 28, 2018

If the FMS database works the same way as the SIMS database, when the database is moved to a new server all of the passwords get reset.

I haven't found that to be the case with the SIMS database, the SIMS database logins transfered over fine, it was the FMS logins which caused the issues. Strange, as I've read elsewhere that for some people it's affected both databases.

TwistedHelixis · November 28, 2018

I haven't found that to be the case with the SIMS database, the SIMS database logins transfered over fine, it was the FMS logins which caused the issues. Strange, as I've read elsewhere that for some people it's affected both databases.

I wonder if any other routines, like updating sims with a single fix patch from Capita might move the accounts over during the database backup process or similar.

Do you think it's OK to run db_p_transfer_login script while users are logged in to Sims or FMS? is it moving or just copying?

matt40k · November 28, 2018

Fine to run with users on the system. Just copying. No moving.

bobsmith · November 29, 2018

Think I am all done, thanks.

As my LA do not want me adding to their Sims / FMS backup script, I have created a standalone script that just transfers the logins, I will run this every now and then, or add to a scheduled task but assume it does not need to run every night.

Could you not set your script up to run your login transfer THEN call the LA's backup script to run?

Automates the whole caboodle nicely?

quaium · December 4, 2018

I've been doing some testing and can confirm that sims.db_p_transfer_login doesn't have to be run every day. Did a backup running sims.db_p_transfer_login and then the next day did it without running sims.db_p_transfer_login. Restored both backups to the test laptop and both had logins intact.

I'm guessing it's only when new users are added that you would need to run sims.db_p_transfer_login.

matt40k · December 4, 2018

I've been doing some testing and can confirm that sims.db_p_transfer_login doesn't have to be run every day. Did a backup running sims.db_p_transfer_login and then the next day did it without running sims.db_p_transfer_login. Restored both backups to the test laptop and both had logins intact.

I'm guessing it's only when new users are added that you would need to run sims.db_p_transfer_login.

... or when users change their passwords.

quaium · December 4, 2018

... or when users change their passwords.

Ahh... yes, that too. Thanks.

Restore Test for FMS

Recommended Posts

quaium

TwistedHelixis

matt40k

quaium

TwistedHelixis

MikeW

quaium

TwistedHelixis

quaium

TwistedHelixis

quaium

TwistedHelixis

quaium

PurpleTentacle

matt40k

TwistedHelixis

quaium

TwistedHelixis

matt40k

bobsmith

quaium

matt40k

quaium

Create an account or sign in to comment

Create an account

Sign in

Recent Threads

Confirm acknowledgement of parental consent requirement for the Additional Google services

Lenovo Tablet Firmware

Outloook 365 - Classic

Home

Forums

What's New