Solved

Help on Setting up an identical test database

Posted on 2016-10-31
17
51 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 17

Expert Comment

by:Pawan Kumar Khowal
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 45

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 17

Expert Comment

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

Thanks!
0
 
LVL 45

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 17

Expert Comment

by:Pawan Kumar Khowal
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 45

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 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41871696
Hi purplesoup,
Any feedback on this?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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 45

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 17

Expert Comment

by:Pawan Kumar Khowal
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 45

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 45

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 17

Accepted Solution

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 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

19 Experts available now in Live!

Get 1:1 Help Now