Restore to my development box does not work

Question

I asked my client to send me the *.bak files, so that I could look at his data on my machine, to test out with my code, in VisualStudio, in Debug mode. Initially, when I tried to restore, I ran into "Files could not be found" message? I could see, in the error message, that the *.bak files referred to the original "production machine" location, on my client's machine? Something I was not expecting. A quick search online revealed a fix, use the OVERWRITE EXISTING DATA (WITH REPLACE) option. I also checked the 'Close existing connections to destination database' option. And that worked. BUT! My login became 'messed up', permissions were gone? So I tried to delete the user but SSMS refused to obey. I had to 'transfer ownership' of the table to 'sa' and only then, SSMS allowed to drop the user. But as I tried to recreate the user, SSMS kept telling me it already existed?

I confess I don't understand all the ramifications that comes with granting users 'ownership' access, I'm discovering it the painful way, but, at the time, when I created the user, it was the only way my VisualBasic code was able to log in the database and have access to the tables. 

Any suggestion on how I could restore the data from my client without touching the user?

Answer 1

Please post the exact error message.

The error "files could not be found" sounds more like a problem with finding the .bak file, not the database.

Also post the results of SELECT @@VERSION.  What version of SQL Server is the .bak file from?

Answer 2

The production server is running SQL Server 2016.
My development box is running SQL Server 2019.
I created the tables as 2014-compatible.

I got a fresh series of *.bak files this morning and the restore was successful. Here's my configuration :

Here is my Security config :

And here is the User mapping for user = 2720qc, which should be my main user :

As you can see, 2720qc does not have access databases? 
When I login to the server, under 2720qc, in SSMS, I can't browse any databases.
If I login with Windows authentication, everything works, but, since I'm not on my client's domain, I use SQL Server Authentication. 

Now, the problem starts when I try to give permissions to 2720qc. I log back under Windows Authentication and try to change the user Properties as follows :

If I cannot change the Mapping, the user 2720qc will never have access to the databases.

At this point, I hit OK and SSMS responds with this error message :

The only 'fix' I found was to create a new user, like '2720qc2', and then I was able to regain access to the databases through my VB applications by specifying this user in the ConnectionString.

My question is why does SSMS complains that the user already exists? I'm not trying to 'create' the user, as far as I can tell. I merely right-click the user and select Properties.

Answer 3

Cause that user has a different SID in the backup (on the other server) than on yours. SQL Server does not automatically "join" these.

After restoring such a backup, run

USE master;
GO

EXECUTE sys.sp_change_users_login 'report';
GO

to list user mismatches. And run

USE master;
GO

EXECUTE sys.sp_change_users_login 'update_one', 'username_from_previouslist';
GO

to match the SID of that user.

Answer 4

Running the 'report' parts works. I can see the user SID number.

But the second part gives :

Msg 15600, Level 15, State 1, Procedure sys.sp_change_users_login, Line 98 [Batch Start Line 8]
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.

I ran exactly :

USE master;
GO

EXECUTE sys.sp_change_users_login 'update_one', 'username_from_previouslist';
GO

I tried this as well :

    EXECUTE sys.sp_change_users_login 'update_one', '2720qc';

Same result :(

Answer 5

Did you try to look up this procedure first for the parameters it needs

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-change-users-login-transact-sql?view=sql-server-ver15

---

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Answer 6

Look up the correct syntax and I wrote the from memory. So it maybe you need to be in the user database instead of master.

Answer 7

The concept is VERY SLOWLY sinking in... A 'User' is different from a 'Login'?

So I use this instruction :

   EXECUTE sys.sp_change_users_login 'update_one', '2720qc', '2720qc';

And I did not get any error. Now, if I execute :

  EXECUTE sys.sp_change_users_login 'report';

All I get is this :

The user '2720qc' is no longer showing in the list, which means that 2720qc is no longer an ORPHAN user?

Not sure what the next step is? Will I be able to update the mappings at this point? Currently, I have this mapping :

If I try to change any mappings, like this :

I get the friendly message :

Answer 8

Did you try running the scripts in all DBs you need (and restored)? BTW, while searching an answer to your question I came up to stackoverflow thread which lead me to this answer 

https://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/fixing-orphaned-database-users/

---

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Answer 9

Perhaps the 'master' mapping above resulted from the 'Use Master' statement in the query?

So I experimented with separate statements for each databases, like this :

  USE CIM;
  GO

  EXECUTE sys.sp_change_users_login 'update_one', '2720qc', '2720qc';
  GO

  USE CIM_History;
  GO

  EXECUTE sys.sp_change_users_login 'update_one', '2720qc', '2720qc';
  GO

  USE CIM_Log;
  GO

  EXECUTE sys.sp_change_users_login 'update_one', '2720qc', '2720qc';

  GO

  USE ISO3664;
  GO

  EXECUTE sys.sp_change_users_login 'update_one', '2720qc', '2720qc';
  GO

And did not get any errors!?

So a visit to 2720qc Properties > Mappings show this :

This looks good to me! At this point, in SSMS, I 'disconnected' and reconnect using the SQL Server Authentication '2720qc' with password, and I can now browse all databases!

The best part is now, I changed my ConnectionString to use the '2720qc' user login and password, and my application has regained access to the databases! Meaning, I can now WORK!!!

Thank you everyone for your kind and patient help.

Answer 10

Keep in mind, the "compatibility version" has nothing to do with the format of the backup file, or MDF files.  There is no way to restore SQL 2019 backups to SQL 2016, regardless of the compatibility level.  The files are always in the format of the database engine.

Answer 11

I kept reading that 'lower versions' were always compatible with 'higher versions'?

When I created this database, I had no idea what server it would run under. So I chose what I deemed to be a 'safe' version, 2014. I hesitated because I'm using 2019 Express and my client didn't even know, at the time I started, where it was going to be hosted. It ended up on a 2016 Enterprise box. 

So you're saying, since I created the database in a 2019 version, even if I chose 2014 compatibility, it is still a 2019 database under the hood?

Answer 12

Yes, that is exactly what I am saying.

Compatibility Level setting only controls how some things are "treated", NOT anything about the engine using.

Please see:

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15#backwardCompat

Answer 13

BTW, do you know anyone who's really good with c++?

Answer 14

I think you may try asking C++ question in C# forum here if there is no C++ forum unless you want to find some people. In that case I can recommend few of my former colleagues who are very proficient in C++.

---

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Answer 15

I'd be interested in getting some help from your former colleagues, Naomi.

Answer 16

Can you email me, please (first letter of my first name + full last name at gmail dot com) and I'll try to connect with one of them through Facebook in the meantime.

---

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles