January 26, 2012 at 11:08 am
Does anyone know why a database backup, and restored (to the same instance on the same server) is not carrying all the users that had access to this database? Some are there (about 1/3rd) and the rest you have to add back.. by hand.
dwcp
January 26, 2012 at 12:04 pm
That is quite odd. Considering that the users are stored at the database level and not the server. If it is indeed the same server it was restored to with the same master database that it had when the backup occurred I can't see how this would happen.
Do the users exist in the table in the database?
What other commands have been run on the database after the restore?
CEWII
January 26, 2012 at 12:41 pm
a full backup is always an exact copy at a certain point of time...so it's just not possible that a backup restores some, but not all users. a backup is all or nothing...if any portion failed, you'd get an error.
i think the issue is your database backup was taken BEFORE those users were added...that's why you need to keep adding them back.
the easiest fix:
restore it right now, add the users right now, then back it up right away, overwriting that last backup you were using.
That way when you restore again, you have your users.
Lowell
January 26, 2012 at 1:59 pm
I second Lowell's view, the database backup might have been taken before those users existed in the database..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
January 26, 2012 at 2:15 pm
When you check for existing users then I am assuming that you are looking at the list of users under SECURITY for that database.
In case you are concluding that based on the users response that they are not able to connect then check the orphaned users report.
EXEC sp_change_users_login 'Report'
Blog
http://saveadba.blogspot.com/
January 27, 2012 at 6:40 am
Yep it is very strange. I have not had it happen to me, but my new boss has. The current case in point.. they took the .Net database (that runs our reporting, note it is called .Net becouse they are using .NET to do the presentation in IE) and made a copy.. then installed that copy into the same instance, but with a new name. (for new reporting) He used the standard SQL Management Studio to restore the DB and the users are there in the old version, but only 1/3rd in the new. They do exist in the instance level, and when you add them back into the database level it gives you an error about the user being there already.. and you refresh and the one you just tried to add is there! Dam strange.. I have never seen something like this before, but my boss stated that this was normal for him. (granted I have not restored many databases, in my time, but this was never an issue for me. In fact I did create a copy of one of our databases to test some new index's and all the users were there...) Just wanted to see if anyone knew what might be going on for him.
Side note, yes we are checking this in the Security section for the database, and the instance. We make sure they are there before releasing the App back into production.
dwp
February 2, 2012 at 5:42 am
One phrase sticks out "Made a copy"
Does that mean you ran "BACKUP DATABASE..." or that you copied files?
Assuming it is a backup, check the header. You might find that there are multiple copies of the backup in a single location. If you don't initialize the file when you run backup, it will add the new backup to the existing backup. When you run restore, it'll use the oldest one, which might explain why things look wrong.
But I'm with everyone else. A backup, using BACKUP DATABASE, is an EXACT copy of the database. Nothing comes up missing, rearranged, changed, etc.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 2, 2012 at 6:46 am
They are using the Backup command.. but backing up to file, instead of tape. I have restored two of their backup's and they are fine... so I do not know what he is doing that strips out users.
February 2, 2012 at 7:39 am
If you're not seeing it, I'd put money down that he's accessing an old backup or has a second set of commands that's stripping out the users. Nothing else makes sense.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 2, 2012 at 9:32 am
I have to agree with you Grant, no other scenarios fit..
CEWII
February 6, 2012 at 1:08 pm
dwilliscp (1/27/2012)
Yep it is very strange. I have not had it happen to me, but my new boss has. The current case in point.. they took the .Net database (that runs our reporting, note it is called .Net becouse they are using .NET to do the presentation in IE) and made a copy.. then installed that copy into the same instance, but with a new name. (for new reporting) He used the standard SQL Management Studio to restore the DB and the users are there in the old version, but only 1/3rd in the new. They do exist in the instance level, and when you add them back into the database level it gives you an error about the user being there already.. and you refresh and the one you just tried to add is there! Dam strange.. I have never seen something like this before, but my boss stated that this was normal for him. (granted I have not restored many databases, in my time, but this was never an issue for me. In fact I did create a copy of one of our databases to test some new index's and all the users were there...) Just wanted to see if anyone knew what might be going on for him.Side note, yes we are checking this in the Security section for the database, and the instance. We make sure they are there before releasing the App back into production.
dwp
What do you mean by: "made a copy.. then installed that copy into the same instance" ?
Pierre
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply