Solved

Help on Setting up an identical test database

Posted on 2016-10-31
17
58 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
  • 6
  • 6
  • 5
17 Comments
 
LVL 24

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 46

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 24

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
 
LVL 46

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 24

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 46

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 24

Expert Comment

by:Pawan Kumar
ID: 41871696
Hi purplesoup,
Any feedback on this?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 46

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 24

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 46

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 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 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 24

Accepted Solution

by:
Pawan Kumar earned 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now