[RESOLVED] Copying Data

I have a database in SQL Server Express 2012 with data in it that I care very little about. I decided to move to whatever the newest version of SQL Server Express is (2017? 2019? I forget which). I have two options, one is to abandon the data in the existing database because I care very little about it. However, there is some slight advantage to moving the data over to the new DB. Oddly, upgrading SQL Server directly didn't seem to work. I got a failure at some point along the process, and ended up just installing the new one beside the old one. I didn't spend much time exploring this, because my real goal was to migrate the whole DB onto a new computer, so on the new computer I simply installed the newer version of SQL Server, and not the old one. Whether I migrate on the old computer really doesn't matter.

So, the situation is that I have two computers. One has SQL Server Express 2012 along with the latest version. The other computer just has the latest version. I'm looking for the easiest way to move the data from a database on the 2012 to a database on the newer version. It seems like there should be an easy way to do this, but from searching around online, every solution seems so convoluted that I'd be better off just abandoning the old data and starting over. As I said, I don't really care about it all that much. In fact, the database will be replicated into the new version of SQL Server, because that is an automated process that I want to test, and now have two good test cases to work with. So, I'm really looking to migrate data, or a whole database (I have a test case for that, too).

What's the easiest way to migrate data from one version to the other, given the same DB structure on both? Second, what's the easiest way to migrate an entire DB from one version to another?

Does the database version have to match the instance version? You could just backup the database on the old instance and then restore it on the new instance.

That mostly worked, with a few interesting issues along the way. The most notable issue is that restoring on a Surface 2 Pro is essentially impossible. The restore form has either a tab or panel that takes up the bulk of the form, with some OK/Cancel buttons below the tab. The way this works on a Surface Pro is that the tab takes up the screen and the buttons are hidden behind it. You can't scroll it, either, so it looks like the tab is the WHOLE form. The fact that there is something else that should be below what you see is not obvious. I was kind of stumped by that, until I moved to doing the work on a larger screen, at which point those hidden buttons appeared, and all was well. It's a minor annoyance, especially since the screen format found on the Surface Pro 2 is probably never going to show up again.

I then had to make the new database visible over my home network, which isn't difficult.

But now I'm stumped by a problem that arose once all that was done:

I normally access this database from a different computer. The SQL Server is found on the aforementioned Surface Pro, but I'm none too keen to do any development work on that. I like larger screens and more of them. Anyways, that's a hardware issue and only tangentially related. The point is the the DB is located on the Surface Pro, but I access it via SQL Server Management Studio (SSMS) on a different computer. This has worked fine for years with SQL Server 2012. I have now used the backup and restore approach to move the DB over to SQL Server 2017. In fact, I used the desktop to do the restore to put the databases (two of them) into the 2017 version.

The issue is that from the desktop, I can't see anything about the databases aside from the fact that they are there. From the Surface Pro, all is well (using a Windows login). From the desktop, using the same SQL Server login (not windows authentication) that I used to restore the databases from backup, I get a message saying "The Database is Not Accessible." A look online finds numerous cases of that error message indicating that there are several potential causes, most of which don't appear to apply to my case.

I then tried to look at the properties and got what I believe to be a more specific error message: "The server principal 'my login name here' is not able to access the database 'the database here' under the current security context."

I have been looking through the security settings for the login, and have not been able to find anything that looks relevant. The login is found in the db_owner role, which seems pretty broad.

When I look at the properties for the login, I can look at the User Mappings, at which point I get a message saying "One or more databases are inaccessible and will not be displayed in list."

I haven't tried to track down that later message, yet, but if anybody has any suggestions, I'd be interested to read them.

I kind of solved that latest issue, but I'm not quite sure what I did to get it into the bad state, nor am I quite clear how I fixed it.

Under the logins for the database, I had X. Under the logins for the server I also had X. It appears that DB-X was not the same as Server-X. The solution was to delete the login for the database, then go to user mapping for the server login and map to the role I wanted (db_owner, in this case) for the server login. I wasn't able to do that before deleting the DB login because I'd get an error saying that there already was such a login in that role.

So, when I restored from backup in the new version, it looks like it brought in the series of logins, but created them as different logins with the same name, than the ones that were found in the server already. I should note that I wasn't paying great attention to the sequence in which I did things, so I may be wrong on a few points:

1) I'm not sure whether I created the server logins before or after I restored the DB.

2) I'm not totally sure that the logins really were found in the DB logins after the restore. I think they were, I'm just not totally sure.

Quote:

---

Originally Posted by Shaggy Hiker

I kind of solved that latest issue, but I'm not quite sure what I did to get it into the bad state, nor am I quite clear how I fixed it.

Under the logins for the database, I had X. Under the logins for the server I also had X. It appears that DB-X was not the same as Server-X. The solution was to delete the login for the database, then go to user mapping for the server login and map to the role I wanted (db_owner, in this case) for the server login. I wasn't able to do that before deleting the DB login because I'd get an error saying that there already was such a login in that role.

So, when I restored from backup in the new version, it looks like it brought in the series of logins, but created them as different logins with the same name, than the ones that were found in the server already. I should note that I wasn't paying great attention to the sequence in which I did things, so I may be wrong on a few points:

1) I'm not sure whether I created the server logins before or after I restored the DB.

2) I'm not totally sure that the logins really were found in the DB logins after the restore. I think they were, I'm just not totally sure.

---

This is basically what I was going to suggest after reading your previous post. The user in the database couldn't possibly be mapped to the login in the instance because the instance was new so the login had to also be new. The user was still mapped to the login in the old instance, which no longer existed. That they have the same name is irrelevant. I have to do this login/user dance often as I get numerous client database backups that I have to restore locally for testing. I often have to delete users from the databases and then map my local logins.

Note that it is best to use the correct terminology to avoid confusion. It is a login at the instance level and a user at the database level.

Unfortunate, but makes sense. This isn't something I'll be doing often, though. My migration path isn't a frequent one.

I know you've resolved this but you still might find this link useful in the future. Check the first answer. Basically, instead of deleting and re-adding the users (which can be easy to muck up) you can simply use ALTER USER.

Yeah, I knew I had to look more into users. It will become quite important soon enough. I have a second computer to install this on, and dealing with the users more efficiently is what I wanted to look at on that one....but first I had to decide what it was I wanted to do with that one...I keep changing my mind.

I actually have a script that I use to look at all the users (based on name) in a DB and match that to the Server login. If they are different I update the SID in the DB user list with the SID in the server login list. That would normally clear my issues when I move databases between servers.