[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

Duplicate SQL DB on same server 2005

How do i make a duplicate DB that i can alter with out affecting existing system

eg

application connects to DB1

I duplicate DB1 and call it DB2 (back up restore)


then connecte application to DB2 for testing.

my main issue so far is i nee dto duplicate schema too, when i do this my tables in DB" are called DB1.Auth etc

i need them to say DB2.Auth   other wise the update script sdont work (third party)

so i after a complete copy of DB including Schema but wth a diffrent name so i can updat eit safley with out affecting the original DB and schema.
0
mhamer
Asked:
mhamer
  • 3
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
Changing the schema will cause other headaches, such as (possibly?) losing explicit permissions.

Instead, I suggest creating synonyms in the db so that db2.<whatever> points to db1.<whatever>.  That way you can reference db1.name or db2.name and they will both always point to the same underlying object.
0
 
sameer2010Commented:
Hi,

Copying the DB should be it. Are you using 4 part convetion for your queries? i.e. DB.Owner.Table.Column? If so, and facing problem with DB, then there is no solution other than alias setup. If it is with owner, you can change dbOwner.

Thanks,
Sam
0
 
mhamerAuthor Commented:
Hi, thank you.

I ineed the original to remain unchanged.

we are up grading an application,  the upgrade has been tested on test systems.
but the live one is especialy critical. and any failier results in a rebuild from scratch of teh upgrade.

the idea in live is to upgrade a copy DB if it works stay with it, if it fails then revert back to old.
the upgrade alters the db and schema by the way.

So I dont think alias will work (assuming the schema is shared in that case? (im not a sql guy)
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
mhamerAuthor Commented:
tables look like this

DB1.AUTHLIST
DB1.SERVER
DB1.Config

but need

DB2.AUTHLIST
DB2.SERVER
DB2.Config


(I say "need" doing this i think will get me past current issues as there all t odo with schema and logs show app i slooking for DB2.Authlist.

app is bespoke in house
0
 
Scott PletcherSenior DBACommented:
The original db can remain the same, although adding the aliases to it won't hurt, and then you wouldn't need to constantly add them back in after you restore it to the new name.


In the restored db, issue these commands:

CREATE SYNONYM DB2.AUTHLIST FOR DB1.AUTHLIST
CREATE SYNONYM DB2.SERVER FOR DB1.SERVER
CREATE SYNONYM DB2.Config FOR DB1.Config

After that, code referencing DB2.AUTHLIST will still work, but that code will actually reference DB1.AUTHLIST instead.
0
 
mhamerAuthor Commented:
Thanks got me past that partcular obstacle :-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now