Restoring SIMS Data

[ITGURU]

Hi

I'm waiting to restore an older version of the SIMS database, but as a separate database and not overwrite the existing one to login to a backup from some time ago.

 

I have used SQL management studio to restore the backup to the server as a new DB name and can see all the tables and users in the DB.

 

However, when I change the connect .ini to the second database name, all users come up as invalid username/password.

 

Please could anyone advise what step I have missed so i can login to it?

 

Thanks.

[Cazale]

Tried the SA u/p? If it works, permissions is your issue.

[SpaceInvader83]

Did you restore the DB using dbattach or just using management studio? I'm fairly confident to keep logins intact you must use dbattach.

[Willott]

I seemed to hit an issues when trying to use dbattach to restore to a new DB name - I used the following procedure (which uses dbattach later):

 

Restored BAK to HDD

Ran “RESTORE FILELISTONLY FROM DISK='[filelocation]'

 

Noted logical names of DBs and then did:

 

RESTORE DATABASE [sims_test] FROM DISK='[filelocation]'

WITH

MOVE 'LogicalNameForTheMDF' TO ‘[databasefilelocation]\sims_test.mdf,

MOVE 'LogicalNameForTheLDF' TO '[databasefilelocation]\sims_test.ldf'

 

Then unattached from in SQL management studio and used dbattach to attach in (without doing this step the logins didn’t seem to work)

 

So as you've already restored to a new DB name, you may be able to use dbattach to detach and reattach the old database on its new DB name and restore the logins

[jinnantonnixx]

When you restored your database, your database users were 'orphaned'. This happens when you move a database to another machine, or restore a database with a different name: The SQL logins for the new database do not exist.

This is because SIMS logins use a database prefix in the logins and database user names.

e.g. Fred and John want to use SIMS database simsdb1. They will log in to the SIMS application with the SIMS users 'fred' and 'john', but behind the scenes, the SIMS application is logging them in to the SQL database with the logins 'simsdb1_fred' and 'simsdb1_john'.

If you restore simsdb1 as simsdb2, the SIMS application will attempt to login with the login names 'simsdb2_fred' and 'simsdb2_john' - these logins don't yet exist.

It's easily fixed, though.

 

There are a some SIMS routines which creates these newly-needed logins and map the database users to the SQL logins, along with setting other security measures.

Running this SQL script against your newly-restored database will normally do the trick.

 

exec sims.db_p_install
go
exec sims.db_p_grant
go

Edited December 9, 2016 by jinnantonnixx

[Willott]

@jinnantonnixx - how did you find that little gem?

[jinnantonnixx]

@jinnantonnixx - how did you find that little gem?

 

It's not a secret, but it's baked into SIMS tools like DBATTACH.

Edited December 9, 2016 by jinnantonnixx

[ITGURU]

Did you restore the DB using dbattach or just using management studio? I'm fairly confident to keep logins intact you must use dbattach.

if I use dbattach to restore a database as a new name, will it have any effect on the current live working database? I.e you can only use it to attach/detach 1 database? Just want to be sure before I do anything that might mess up the current database.

[SpaceInvader83]

if I use dbattach to restore a database as a new name, will it have any effect on the current live working database? I.e you can only use it to attach/detach 1 database? Just want to be sure before I do anything that might mess up the current database.

 

No need if you run the SQL Script Jinnantonnixx posted, this will fix up your logins without having to reattach.

[ITGURU]

No need if you run the SQL Script Jinnantonnixx posted, this will fix up your logins without having to reattach.

 

I assume I just replace the sims. part with the restored database name to run it against the other database?

exec sims.db_p_install

go

exec sims.db_p_grant

go

[SpaceInvader83]

I assume I just replace the sims. part with the restored database name to run it against the other database?

exec sims.db_p_install

go

exec sims.db_p_grant

go

 

As I understand it no you wouldn't need to alter the commands, there stored procedures you are calling. If you are running the command in management studio just make sure you right click the db you want to run it against and choose new query. Perhaps someone else could clarify my thinking is correct?

[jinnantonnixx]

The install and grant statements are SIMS stored procedures, present in every SIMS database, so they'll be in the database you've restored.

 

Right-click the database from SQL Server Management Studio then click New Query.

 

To make doubly sure, use the 'use' statement in your query. This makes sure that the query has switched context to your particular database.

 

use [the_name_of_sims_database_you've_restored]
go
exec sims.db_p_install
go
exec sims.db_p_grant
go

Edited December 9, 2016 by jinnantonnixx

[ITGURU]

The install and grant statements are SIMS stored procedures, present in every SIMS database, so they'll be in the database you've restored.

 

Right-click the database from SQL Server Management Studio then click New Query.

 

To make doubly sure, use the 'use' statement in your query. This makes sure that the query has switched context to your particular database.

 

use [the_name_of_sims_database_you've_restored]
go
exec sims.db_p_install
go
exec sims.db_p_grant
go

That worked running the commands thanks. However, recently added new users don't appear to work on the login box. Is this something to do with how the database is backed up?

also, can the user password be changed from the SLQ management studio, or does it have to be within system manager?

[ITGURU]

my next questions is, if I then want to remove the database through SQL management studio will it automatically delete all the usernames under the Server Security as well as the database security?

 

or how is best to remove it?

[jinnantonnixx]

That worked running the commands thanks. However, recently added new users don't appear to work on the login box. Is this something to do with how the database is backed up?

also, can the user password be changed from the SLQ management studio, or does it have to be within system manager?

 

The SIMS users and passwords are encrypted and stored within the SIMS database. From memory, the stored procedure that does this is called sims.db_p_transfer_logins, but I'll have to check when I'm back in work. DBAttach runs this procedure before it backs up the database so that the latest users and their passwords are stored.

 

A simple way round your problem is to run the transfer logins routine on your main database, back it up, then restore and proceed as outlined in the previous posts.

Edited December 9, 2016 by jinnantonnixx

[jinnantonnixx]

my next questions is, if I then want to remove the database through SQL management studio will it automatically delete all the usernames under the Server Security as well as the database security?

 

or how is best to remove it?

 

The usernames will be deleted as they belong in the database and will disappear with the database. However, the logins, which give you access to the server will not be deleted. As they're organised with the database prefix, they're easy to select and delete as a group.

Edited December 9, 2016 by jinnantonnixx