Solved

Make copy SQL database on same server

Posted on 2014-12-16
5
326 Views
Last Modified: 2014-12-16
I have a SQL 2014 instance containing a database with the following properties:

SQL Host:  sqlprod
DB:  ecm
DB User:  ecmuser

I have a production web app that connects to the db with the properties above.

I'm trying to stand up a test instance of the web app.  Can I copy (backup/restore) the ecm db on the same SQL host?  ....Essentially creating a new db with the name ecmtest, and restoring a backup of ecm to ecmtest.

Would this work?  Or do I need a new SQL instance?

Thanks in advance.
0
Comment
Question by:bmsande
5 Comments
 
LVL 32

Expert Comment

by:Stefan Hoffmann
ID: 40502831
You can do this. But keep in mind, that this requires resources (CPU, RAM, DISK) from the production system..

Thus its better to :
1. Install it on a separate test server.
2. Install it in a test instance.

You need also to consider interactions: Do you use xp_cmdshell or work with jobs? These may affect production resources like files or web services.
0
 

Author Comment

by:bmsande
ID: 40502888
Thanks for your response.  System resources are not a concern, but good point.  I'm pretty certain xp_cmdshell is not used.  Could you be more specific on 'jobs'?   This is mostly tables and views, and a couple stored procedures.

When looking at my db in SQL, I expand tables and see the following:

ecmuser.table1
ecmuser.table2
etc, etc

How would I handle the user during this process, on the same SQL instance?  The same user can be mapped to both db's, right?  I would prefer to use a different user, for isolation purposes.  

I'm in a situation where a test environment is needed, but the customer only has one SQL 2014 instance.  They have SQL 2012 and 2008 R2 on the network, but I was afraid I can't take a SQL 2014 db backup and restore (downgrade) on a different SQL version.  If this is possible, then I would prefer this process for test-prod isolation.
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
ID: 40502908
Well, consider a stored procedure interacting with SQL Server Agent.

You need to remove them manually in your restored database and create the appropriate user/logins. When you already use roles assign permissions to users then this is easy. Otherwise you need to assign the permissions also.

When there exists a test server, then install an express instance on it.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 40502928
Yes, you can do it pretty easy. You just need a backup from emc database and then run the following restore command:
RESTORE DATABASE emctest
   FROM DISK='K:\Backups\EMC.bak'
   MOVE 'EMC_Data' TO 'N:\Data\EMCtest_Data.mdf', 
   MOVE 'EMC_Log' TO 'N:\Log\EMCtest_Log.ldf'

Open in new window

K;\Backups is only an example. You should replace it with the current path to the backup file.
N:\Data and N:\Log are also an example. You should replace both with the new location for the files of the new database (emctes).

NOTE: Since you are copying to the same instance there's no need to recreate the users since they will automatically matched with the respective logins.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 40502943
You can create the test db using a standard SQL RESTORE including the necessary WITH clauses to assign new physical file names:
RESTORE DATABASE ecmtest
FROM DISK = 'x:\full\path\to\backup\file\ecm.bak'
WITH MOVE 'logical_file_name_1' TO 'd:\full\path\to\new\data\file\ecmtest.mdf',
    MOVE 'logical_file_name_2' TO 'l:\full\path\to\new\log\file\ecmtest_log.ldf'

Then change the existing user name to your test user name:
USE ecmtest;
ALTER USER ecmuser WITH NAME = ecmtestuser;
ALTER USER ecmtestuser WITH DEFAULT_SCHEMA = ecmuser;

IMO, the test user name should never appear in the production db, and the prod user name should never appear in the test db, to help prevent accidentally being in the wrong environment.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Optimize/Performance Tune Production Databases 11 40
SQl query 19 12
Usage Scenarios for Extended Events? 1 9
Sql query 34 17
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

708 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

14 Experts available now in Live!

Get 1:1 Help Now