Script needed to backup SQL databases (Master etc)

[TwistedHelixis]

I was just reading another thread about the Sims & FMS databases not containing the user login accounts and so basically rendering the backup of Sims and FMS almost useless, and have just realized the script our LA supplied for backing Sims and FMS also does not backup any of the other needed databases, which I think are master, model and modellog.

 

I don't want to include all the backup script the LA supplied as its not my script, but based on these lines what would I need to add to the script to go about getting everything correctly backup up. If it helps I can PM the rest of the script.

 

& $sims_path /server=$computer\$instance /user=?? /password='??' /database=sims /backup /path=$sims_filename /auto& $ccsfms_path /server=$computer\$instance /user=?? /password='??' /database=fms /backup /path=$fms_filename /auto

 

Scripts are not my thing, so thanks in advance for you help.

 

BTW we have SQL express 2012, not full SQL.

[mrwoberts]

I found this script in an EduGeek thread

 

The bit that transfers the logins from the Master Database is..

OSQL -S"$server" -U $user -P $password -Q"exec sims.sims.db_p_transfer_login"
OSQL -S"$server" -U $user -P $password -Q"exec fmsstm.sims.db_p_transfer_login"

 

The script formatting of that thread looks jumbled on my PC, if you find it jumbled, I've reformatted it...

 

sims-backup-script1.ps1

Edited May 25, 2017 by mrwoberts

[TwistedHelixis]

Thanks for that.

 

A few questions?

If I was hard coding the details would I do the following and remove the $

so

OSQL -S"$server" -U $user -P $password

would become

OSQL -S"MyServer\instance" -U USER -P Password

 

Also do you know why it say

sims.sims.db_p_transfer

but for fms it says

fmsstm.sims.db_p_transfer

Why the sims bit?

[Banjo]

I was just reading another thread about the Sims & FMS databases not containing the user login accounts and so basically rendering the backup of Sims and FMS almost useless, and have just realized the script our LA supplied for backing Sims and FMS also does not backup any of the other needed databases, which I think are master, model and modellog.

 

I don't want to include all the backup script the LA supplied as its not my script, but based on these lines what would I need to add to the script to go about getting everything correctly backup up. If it helps I can PM the rest of the script.

 

 

 

Scripts are not my thing, so thanks in advance for you help.

 

BTW we have SQL express 2012, not full SQL.

 

I'm not sure you are correct with this. The instance holds the SA account (master db) but the SIMS/FMS databases have the DBO accounts in them and the SA account by default is mapped to the DBO role. Anyone that logs into the instance using the SA account is by default the DBO of SIMS/FMS.

If your SQL instance breaks - create a new one, I’d recommend adding local administrators to the login role of the instance as this gives you sysadmin on the instance using window authentication. Attach the SIMS/FMS backups to a new instance of SQL using the Capita dbattach tool(s) and away you go.

If your master is corrupt – then it has to be new SQL instance as a minimum and I’d be looking to flatten the box as I wouldn’t trust it to not happen again.

We have disaster recovered quite a few times and never bothered with the master.

[mrwoberts]

If I was hard coding the details would I do the following and remove the $

 

The $ precedes a variable, so if you wanted to make the text literal do not use the quotes or the $

So it would be

 

OSQL -S MyServer\instance -U USER -P Password

 

Also do you know why it say

sims.sims.db_p_transfer

These OSQL exec statements were relevant to the original poster, but you'd need to change them for your DB.

The sims.sims.db_p_transfer_login string is made of up

 

sims = the instance

sims = the db inside the instance

 

It looks like your FMS db would instead be...

 

fms.sims.db_p_transfer_login

[TwistedHelixis]

Thanks for the info so far, I do appreciate it.

@Banjo

A few years ago I installed a new server and needed to move the sims database. I built the new server, installed the same version of SQL and restored the Sims database from my backups, but was not able to log in to Sims using my sims username or password.

 

I called my LA and they said, we have had that on occasion and normally send the database to Capita to look at, but its something to do with the fact the users not stored in the Sims database.

 

As the original server was a vm and I had no time to wait for Capita to look at the database, I simply restored the VM to the new server and moved on.

 

What should I have done in the above scenario? Would logging in to the instance using the SA account of helped, and if so how?

Edited May 25, 2017 by TwistedHelixis

[Banjo]

All the SIMS users are in the SIMS database - Just opened up Wateredge in MSSQLStudio and Kevin Myles is in the SIMS db as EngWE2_MYLESK

 

What I expect happened above is you didn't use the DBattach tool to attach the databases to the new instance. Just using MSSQLStudio it wont like one bit and you wont be able to login.

 

You ideally need to use DBattach, either the .mdf file or restore the .bak file using these Capita tool(s).

[matt40k]

@TwistedHelixis

 

sims.sims.db_p_transfer

 

first SIMS is the database name, second is the schema, last bit is the object \ stored procedure name. You can also prefix it with a server name (assuming you have linked servers setup) - so SQL format is:

Server . Database . Schema . Object

 

Server, database and schema are all option. Schema is required, but it will default to dbo if not present

 

The SIMS database will contain the user names, that stored procedure (db_p_transfer), will transfer the passwords from the MASTER database into the SIMS \ FMS database. It's utterly pointless backing up the systems databases on a SIMS SQL server, if your system databases get corrupt, you have an underlying problem which will most likely result in a reinstall. In terms of the passwords, you really need to use Windows Authentication, SIMS SQL password is only really obscured

[TwistedHelixis]

The script transfers the passwords not the users = Thanks, I have a better understating.

@matt40k

you really need to use Windows Authentication

Where do I do this? Can I use Windows authentication to open and run Sims? or are you just talking about SQL?

 

Are you also saying that if I use Windows Authentication then I do not need to run the script that copies over the passwords?

[matt40k]

Where do I do this? Can I use Windows authentication to open and run Sims? or are you just talking about SQL?

Are you also saying that if I use Windows Authentication then I do not need to run the script that copies over the passwords?

 

Both. Within SIMS .net, system manager, you can link the user to a Windows User. It then automatically passes through the User token to SQL rather then the password.

[Banjo]

You might be able to login to SIMS with the sysman account - run the patch to reset the password if necessary. Once you’re in, all the staff accounts will be there, but you will need to regenerate their passwords and send them out.

[matt40k]

You might be able to login to SIMS with the sysman account - run the patch to reset the password if necessary. Once you’re in, all the staff accounts will be there, but you will need to regenerate their passwords and send them out.

Unless your using Windows Auth...

 

... or you run that stored proc

 

... and never a bad thing to change your password

[TwistedHelixis]

@matt40k

So does it make sense to use my domain admin / local admin account as my connected Sims auth account?

 

Is it possible to do the same with FMS and connect a Windows account?

@Banjo

Just using MSSQLStudio it wont like one bit and you wont be able to login.

You jogged my memory and you are correct. Our LA always backed up SIMS and FMS using SQL Studio, hence my issues back then and no need for me to worry know.

[matt40k]

There is a pretty good guide within SIMS that details it all better then I can. Should be within SIMS .net > Documentation

[Banjo]

We use Attix5 (RBUSS) from Redstor and approved by Capita. Its a cloud backup with a plugin for SIMS/FMS/Discover live databases and also your Docstore, backup folder, CTF, Star folders etc. Any backup from Studio will have issues with logging in. It can be fixed with scripting but is a PITA.

 

We have to DR several times a year and never had an issue.

Edited May 31, 2017 by Banjo