Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help on Setting up an identical test database

Posted on 2016-10-31
17
Medium Priority
?
87 Views
Last Modified: 2016-11-11
I am trying to create a copy of a database for testing, I have moved some code out of a desktop application and onto the web and want to run the same process on each database, so I backed up the database and restored it to a new database, but it doesn't seem to restore the users of the first database with the same permissions in the second.

Basically I want two identical databases and run the same process on each - one from the web, the other from the desktop, and check the database results are the same.

I did do some searches on copying users over from one database to another and it just looked like it wasn't possible to automate it - you just have to do it all by hand, is that the case or is there some way to get it working.

The two databases are on the same server.

This should be MS SQL Server 2008 and later
0
Comment
Question by:purplesoup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 5
17 Comments
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41866799
Use Generate Scripts--

Right click on the DB -> Generate Scripts  -> Advanced Scripting options -> Schema only.. --> OK.

and you are done. You get a script , run the server you want to create the new DB. Before this just create the blank DB.

Hope it helps !
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41866822
Your issue is that you didn't migrate the logins. Logins aren't part of a database but the SQL Server instance itself. Since you already replicated the database you'll need to replicate the logins as well.
The process isn't hard and Microsoft already has a script built that will do everything for you. Just follow this MSDN article and you'll be just fine.
0
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41866992
ohh  yes ! After using the Generate Script , use the above script to move the logins if required. !!

Thanks!
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41867006
After using the Generate Script ,
What for the OP will need that? OP already stated that has the backup and even restored it. Issue is with permissions.
0
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41867030
Ohh ! My bad.
0
 

Author Comment

by:purplesoup
ID: 41867104
ok I'm not quite seeing the same things described.

I'm in generate scripts, but the first screen I see is script entire database or select specific database objects, I don't see any advanced scripting options. I can choose specific objects and then select schema. That generates just a series of CREATE SCHEMA statements - is that all I need?

The MSDN article had various options, but seemed to cover setting up a database on another server, not just having a copy of the database on the same server, hence it had a lot of things about setting up new logins etc which didn't really seem relevant and I couldn't see anything about setting up a user on the new database.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41867120
You don't need to generate nothing new. You already restored a copy of the database, right?

The MSDN article had various options, but seemed to cover setting up a database on another server, not just having a copy of the database on the same server, hence it had a lot of things about setting up new logins etc which didn't really seem relevant and I couldn't see anything about setting up a user on the new database.
That article is only for moving logins from one server to another. Forget about the copy database option since you already have a backup and even restored it.
Just try to follow the article step-by-step. It can't be more clear.
0
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41871696
Hi purplesoup,
Any feedback on this?
0
 

Author Comment

by:purplesoup
ID: 41872573
So I was a bit confused as to why I needed to set up logins as I am on the same server and it was getting an existing login to access the restored database with the same settings and the original database, however I have followed the steps in the MSDN article and I got the following messages:


Msg 15433, Level 16, State 1, Line 6
Supplied parameter sid is in use.
Msg 15025, Level 16, State 2, Line 9
The server principal 'NT AUTHORITY\SYSTEM' already exists.
Msg 15025, Level 16, State 2, Line 12
The server principal 'NT SERVICE\MSSQLSERVER' already exists.
Msg 15025, Level 16, State 2, Line 15
The server principal 'CIINSTALL\Administrator' already exists.
Msg 15025, Level 16, State 2, Line 18
The server principal 'NT SERVICE\SQLSERVERAGENT' already exists.
Msg 15433, Level 16, State 1, Line 21
Supplied parameter sid is in use.
Msg 15025, Level 16, State 2, Line 24
The server principal 'BUILTIN\Administrators' already exists.
Msg 15433, Level 16, State 1, Line 27
Supplied parameter sid is in use.
Msg 15433, Level 16, State 1, Line 30
Supplied parameter sid is in use.

I mean the script just generated a series of logins, and the article itself says "This article describes how to transfer the logins and the passwords between instances of Microsoft SQL Server 2005, of Microsoft SQL Server 2008, and of Microsoft SQL Server 2012 on different servers".
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41877013
"So I was a bit confused as to why I needed to set up logins as I am on the same server"

Well, that's new for me. You copied the database into the same SQL Server instance?
0
 

Author Comment

by:purplesoup
ID: 41878532
Sorry, when I said "The two databases are on the same server" I did in fact mean on the same instance on the same server.

Just to repeat, the purpose of doing this is I have a web application and a desktop application where I want the web application to perform the same calculations on the data as the desktop application. I therefore want two identical databases side by side so I can run data comparisons to check the data on one matches the data on the other after processing one by the desktop application and the other from the web application.
0
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41878541
If you need only logins then use below-
https://msdn.microsoft.com/en-in/library/ee406927.aspx

<<Right-click on the login --> select Script Login as. --> Select one of New Query Editor Window and run this script against the new DB. >>
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41878563
I did in fact mean on the same instance on the same server.
This changes everything because then you don't need to copy logins because they are already there. The restore you performed at the 1st time should work.
Just for review, what's the error you're getting?
0
 

Author Comment

by:purplesoup
ID: 41878908
I just didn't have the same access to the new database with the same user - it just said the user couldn't access the database - I had to go through and re-set the permissions manually, so I wanted to know if there was an easier way to do it.
0
 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 41878928
You'll need to run the following command for each user in the new database:
EXEC sp_change_users_login 'Update_One', 'DatabaseUserName', 'SQLServerLoginName';  

Open in new window

NOTE: Replace the User and Login name with the correct ones.
0
 
LVL 30

Accepted Solution

by:
Pawan Kumar earned 1000 total points
ID: 41879924
I think you need this.. Below query will give you the output, just copy the output on the new SSMS query window and execute. I am using db_owner role, you can define your own. Try..

--Below query will give you the output, just copy the output on the new SSMS query window and execute.


DECLARE @NewDbName AS VARCHAR(100) = 'NEWDatabaseUserName'

SELECT 'USE [' + @NewDbName + '];   CREATE USER [' +  name + '] FOR LOGIN [' + name + ']; '
+ '  ALTER ROLE [db_owner] ADD MEMBER ' + '[' +  name + '] ;' 
FROM sys.server_principals 
WHERE ( TYPE = 'S' OR TYPE = 'U' )
and ( name not like '%##%' AND name not like 'NT %')
and ( name <> 'sa' )

--

Open in new window


Hope this helps!!
0
 

Author Closing Comment

by:purplesoup
ID: 41884317
Thanks for your help. Sorry I wasn't clearer in the original question.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question