Restore Test for FMS

[quaium]

I carried out a backup of FMS via Tools | FMS Transactions Backup. I have restored this backup to a standalone laptop but it's not allowing me to log in to FMS. Can anyone tell me why this is happening? I've read that it could be because logins are stored in the Master database.

 

I've also read that you need to use the FMS version of dbattach, but for some reason this isn't available on our SIMS server or the laptop I have set up. The path I am looking at is C:\Program Files\Microsoft SQL Server\MSSQL14.FMSDB\MSSQL\Binn and there's no sign of DBAttach.

 

Final question, if I manually run DBAttach, whichever version, and do the Backup a Database thing from there, is this a valid backup for both SIMS and FMS and would restoring FMS mean restoring both the FMS database and the Master database for the user logins?

[quaium]

Anyone got any ideas regarding this issue?

[theeggmaster]

I've got one DbAttach.exe under Binn, but then I've also got a folder in Binn called FMSSQL and in that folder there is a different DbAttach.exe for FMS.

[quaium]

Ahhh... OK I found it in C:\Program Files\Microsoft SQL Server\MSSQL11.FMSDB\MSSQL\Binn\FMSSQL. Thanks.

 

So to my other point, if I manually run DBAttach and do the Backup a Database thing from there, is this a valid backup for FMS and would restoring FMS mean restoring both the FMS database and the Master database for the user logins to stay intact?

[steveg]

I'm pretty sure, but may be wrong, but if you do the backup and restore from DBAttach then this sorts out the user issues - you don't need to restore the Master Database.

 

Steve

[quaium]

I'm pretty sure, but may be wrong, but if you do the backup and restore from DBAttach then this sorts out the user issues - you don't need to restore the Master Database.

 

Steve

So I'm guessing that will means doing the backup via DBAttach? Not through FMS? The backup I have was done though FMS.

[TwistedHelixis]

We use a script that uses DBAttach to backup the databases and never had any issues when restoring.

[quaium]

We use a script that uses DBAttach to backup the databases and never had any issues when restoring.

Does that script use the backup database option on DBAttach?

 

I've not performed the backup I'm having issues with via DBAttach, it's the backup performed with FMS that causing the issues with user logins.

[mrwoberts]

I think you're referring to this command

 

SQLCMD -SLocalhost\SIMS2016 -E -Q"exec sims.sims.db_p_transfer_login"

[quaium]

I think you're referring to this command

 

SQLCMD -SLocalhost\SIMS2016 -E -Q"exec sims.sims.db_p_transfer_login"

What does this command do?

[mrwoberts]

sims.sims.db_p_transfer_login

 

Runs an SQL procedure to transfer the logins from the master database to the individual database. The one above was for SIMS, you'd just change it to reflect the FMS database. (need someone to confirm that)

I think the DBattach gui may actually run this when it does the backup, not entirely sure.

Also, please note I haven't used this for the FMS database, so I'd be interested if you reported back whether it was needed, or whether it worked...

Edited November 23, 2018 by mrwoberts

[quaium]

sims.sims.db_p_transfer_login

 

Runs an SQL procedure to transfer the logins from the master database to the individual database. The one above was for SIMS, you'd just change it to reflect the FMS database. (need someone to confirm that)

I think the DBattach gui may actually run this when it does the backup, not entirely sure.

Also, please note I haven't used this for the FMS database, so I'd be interested if you reported back whether it was needed, or whether it worked...

 

Sois this SQL procedure run before the backup is taken or after?

 

The standalone laptop I have where I restored the backup taken from within FMS (Tools | FMS Transactions Backup) has users present in the FMS database from what I can see in SQL Management Studio but they can't log in,

[steveg]

Have a look at Capita My account knowledgebase article KB400958 - it gives the recommended backup procedures for SIMS, which will also apply to FMS.

 

Since virtualising our SIMS server years ago I haven't ever done a database restore, its much quicker and easier to just restore the whole server. Still do the backups just in case! - Obviously doesn't work if you share the sql server with other databases.

 

Steve

Edited November 23, 2018 by steveg

[quaium]

This is driving me crazy... I've created a backup using knowledgebase article KB400958 and I am still inable to log in to FMS on the laptop. Keeps getting the error about invalid login!

 

What am I doing wrong here?

[bobsmith]

ok, if you've got access to the original SQL server try just doing a sql backup of the master db and then restore that to your test laptop?

[bobsmith]

http://www.edugeek.net/forums/mis-systems/200954-sims-backup.html

[quaium]

ok, if you've got access to the original SQL server try just doing a sql backup of the master db and then restore that to your test laptop?

 

I tried that and it threw up an error when trying to restore the master database. I am rebuilding the laptop to see if using SQL Server 2012 makes any difference. I was using SQL Server 2017.

[quaium]

http://www.edugeek.net/forums/mis-systems/200954-sims-backup.html

 

Now that's interesting, thank you. Does anyone have a script that they use for SIMS and FMS backups that incorporates that script?

 

My script has the following...

 

"D:\Program Files\Microsoft SQL Server\MSSQL10.\MSSQL\Binn\DbAttach.exe" /BACKUP /SERVER="\" /USER="sa" /PASSWORD="password" /DATABASE="sims" /AUTO /PATH="d:\SIMSBackup\sims.bak"

"D:\Program Files\Microsoft SQL Server\MSSQL10.\MSSQL\Binn\DbAttach.exe" /BACKUP /SERVER="\" /USER="sa" /PASSWORD="password" /DATABASE="fms" /AUTO /PATH="d:\SIMSBackup\fms.bak"

[mrwoberts]

Couple of things.

Try to keep the environments as identical as you can when testing something - I wondered if you were suggesting that your testing laptop was using SQL Server 2017, whereas your production server wasn't? You'd also be better off keeping within the Capita supported product range (not sure SQL 2017 is on that list yet??)

Keep the SQL instance name and database names the same, which you're possibly already doing.

Call that stored procedure, which I previously pointed you to, and see whether that fixes the login issue.

[quaium]

Couple of things.

Try to keep the environments as identical as you can when testing something - I wondered if you were suggesting that your testing laptop was using SQL Server 2017, whereas your production server wasn't? You'd also be better off keeping within the Capita supported product range (not sure SQL 2017 is on that list yet??)

Keep the SQL instance name and database names the same, which you're possibly already doing.

Call that stored procedure, which I previously pointed you to, and see whether that fixes the login issue.

Yes, I've started again so will try and keep the environments as equal as possible.

 

How do I use that stored procedure? Do I use it on the test laptop after restoring the database or do I use it during the backup procedure on the live server? If that's the case, how wound I run the backup of SIMS and FMS? Step by step instructions would be great.

[mrwoberts]

I backup the SIMS database every night using a script (FMS has gone 'hosted').

This is pretty much the script I use here http://www.edugeek.net/forums/mis-systems/171205-scripted-backup-sims-fms.html#post1465637

 

Within this script you'll see the expected DBAttach calls, but crucially, before that, you'll see the Invoke-Sqlcmd, which calls the procedure to transfer the logins.

Then, when you restore, via the GUI or script, you'll should have the user logins within each database. If not, there are other problems.

[quaium]

So I've managed to get together a script from the links provided. Could someone please check it over to see if everything is OK with it?

 

# 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"

# 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 $SIMSDBServer -Username "sa" -Password $SAPassword -Query "exec sims.sims.db_p_transfer_login"
Start-Sleep -s 30
Invoke-Sqlcmd -ServerInstance $FMSDBServer -Username "sa" -Password $SAPassword -Query "exec fmsacad.sims.db_p_transfer_login"
Start-Sleep -s 30

$SIMSDBAttach /BACKUP /AUTO /SERVER=$SIMSDBServer /DATABASE=SIMS /USER=sa /PASSWORD=$SAPassword /PATH=$BackupPath\SIMS.BAK | Out-Host
$FMSDBAttach /BACKUP /AUTO /SERVER=$FMSDBServer /DATABASE=FMSAcad /USER=sa /PASSWORD=$SAPassword /PATH=$BackupPath\FMS.BAK | Out-Host

 

Two things, for the FMS sims.db_p_transfer_login is it correct that I have put in the database name at the beginning fmsacad.sims.db_p_transfer_login?

 

And lastly, is it OK and safe to run the command sims.db_p_transfer_login, I'm not going to arrive tomorrow morning to find that users can't log in to SIMS or FMS or anything like that am I?

[matt40k]

Looks good.

 

"exec fmsacad.sims.db_p_transfer_login"

 

fmsacad is the database

sims is the schema

db_p_transfer_login is the object - this case the stored procedure.

 

Yes its safe to run. It will copy the users from Master to SIMS\FMS db. Users still login to Master, not the SIMS\FMS db. Only way it could break would be if the backup filled the hard drive

[quaium]

Only way it could break would be if the backup filled the hard drive

 

Oh please don't mate! New to all this and it's like walking on egg shells!

 

"sims is the schema" - is that by default and would it be the same for both databases even though they are on different SQL instances?

[matt40k]

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