Making multiple copies using a Schema

Question

Making multiple copies using a Schema

toparsi

SSCrazy

Points: 2292
More actions
June 7, 2011 at 9:15 am

#238729

Hello Guys,
I have an application which has one dbo schema and 2 other schemas. Client wants me to create copies of certain schema for his testing with different name. He wants me to create one for each person who wants to do testing and this is more than 5 within the same database. I was wondering what could be the best way of doing this and also was looking for automating this process. Any help would be appreciated.
Thanks,
Nick

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

toparsi (6/7/2011)
Hello Guys,
I have an application which has one dbo schema and 2 other schemas. Client wants me to create copies of certain schema for his testing with different name. He wants me to create one for each person who wants to do testing and this is more than 5 within the same database. I was wondering what could be the best way of doing this and also was looking for automating this process. Any help would be appreciated.
Thanks,
Nick

I guess i would script the tables, and then edit them with a quick find and replace to change [dbo]to something like [Bob],

to make everything [Bob].EachObjectName; you have the option to script the data with it as well in SQL 2008, so if it's not an insane amount of rows, that's an option as well, otherwise, after creating the data, you need to script the INSERT commands...you know..

SET IDENTITY_INSERT BOB.TABLENAME ON;

INSERT INTO BOB.TABLENAME(COLUMNLIST)

SELECT COLUMNLIST FROM dbo.TABLENAME

SET IDENTITY_INSERT BOB.TABLENAME OFF;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

toparsi SSCrazy Points: 2292 More actions · Answer 2

Thanks Lowel,

But I have lot of tables and lot of data in the tables with lot of dependencies. The process you suggested was used once and it took a lot of time for me.So, if anyone else has any other ideas.

Thanks,

Nick

Lowell SSC Guru Points: 323518 More actions · Answer 3

i assume that working within the new/specific schema is critical, and so copying the database and restoring under a new database name is not really an option?

is using the ideas in this article an option?

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 4

ok how about this...untested but i'm brainstorming.

backup and restore the database as a new database.

go to the "new" database, and rename a specific schema to a new name.

use the "Transfer SQL Server Objects Task" to copy the objects and data from NewDatabase To the Testing database.

change the schema name in the new database and repeat for each of the fiver (or more) schemas you plan to create.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

toparsi SSCrazy Points: 2292 More actions · Answer 5

Thanks Lowel,

I will probably have to do the generate scripts for creating DDL Scripts. But I need to have all the schemas in the same Database, so the second option would not work. Also, generating DML scripts is bigger challenge for me due the dependencies. If I can figure out the dependency order somehow then I can do insert into select * from. Can we run some command figure out the parent child relationships or order of inserts for tables in a database.

Thanks,

nick

Lowell SSC Guru Points: 323518 More actions · Answer 6

toparsi (6/7/2011)
Thanks Lowel,
I will probably have to do the generate scripts for creating DDL Scripts. But I need to have all the schemas in the same Database, so the second option would not work. Also, generating DML scripts is bigger challenge for me due the dependencies. If I can figure out the dependency order somehow then I can do insert into select * from. Can we run some command figure out the parent child relationships or order of inserts for tables in a database.
Thanks,
nick

dependancies in order suitable for scripting for all objects can be gotten with this command:

EXEC sp_msdependencies @intrans = 1

I'm testing my thoery now, i created a schema called "Orange" in a database with 1500 + objects, and then did ALTER SCHEMA Orange TRANSFER dbo.[EachObjectName] so i have a sample...

no in my original database, which only has a dbo schema, i hope to import all the Orange schema objects....

still testing

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 7

my idea worked fine for me; copied 1637 tables, and thier data with no problem.

I had to do recompile all the views, and script htem and procs/functions separately.

in my case, all my procs and views were inferring [dbo] as the owner of objects they referenced, so after i transferred them to the 'Orange' schema, they all failed, since there was no dbo.[TableTheyWereLookingfor]

when i scripted them to have orange[TableTheyWereLookingfor] for all the referenced objects, then ran the script, they all worked...i did mis a couple on the first pass find and replace for dbo., but not [dbo]., pasting missing schema references of Orange.)

this was surprisingly easy to simply use the Import Data wizard to get the tables.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

toparsi SSCrazy Points: 2292 More actions · Answer 8

toparsi

SSCrazy

Points: 2292

June 7, 2011 at 11:24 am

#1335192

thanks Lowel for all the help.

Lowell SSC Guru Points: 323518 More actions · Answer 9

please post back if it works for you; this same issue comes up on otehr posts, this is the first time i really jumped in at it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 10

dbo is for default,revoke the permission from the dbo schema on the other users,whenever users create any objects then they will restrict to create their own objects in their schema only.

Yes this is good and best practice users create their objects in own schema not in default dbo but i will not recommended the same tables in other schemas as well

My Blog
www.aureus-salah.com

toparsi SSCrazy Points: 2292 More actions · Answer 11

Thanks Lowell,

It works great and I think this is the option which will create multiple copies with less work unless someone else has better ideas than this one. I did the following to create multiple copies of schemas within the same database.

1) Created a source and target database with the same backup which had one dbo and SchemaA

2) On the target DB, Created a second SchemaB and moved all the objects from SchemaA to SchemaB

3) Imported all the objects from SchemaA from Source DB to target DB

4) Had to do this 10 times as I needed 10 copies.

Lowell SSC Guru Points: 323518 More actions · Answer 12

awesome!

like i said, it worked perfect for me with tables, but most of my views and procs failed; they all pointed to dbo.Tables....

i cleaned them all up for a single schema, so they all referenced Orangle.Objectname isntead of dbo.Objectname, but i think i still ended up haivng to script them out for each new schema , and do a find and replace. so that they would compile correctly in the new schema.

glad this is working for you!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

toparsi SSCrazy Points: 2292 More actions · Answer 13

Hey Lowell,

The copy worked but I am facing new problem with the object that is being copied. I have user defined data types created and I use them in the tables. So, when I use import and export wizard, it is just copying the underlying datatype and not copying the user defined data type name. I have 200 tables in each schema and about 8 user defined data types that are used in all the tables. So, if this is not copied the whole concept of me changing the type in one place is lost. I am stuck with this now and dont know how to fix this. Any ideas,

Nick

Lowell SSC Guru Points: 323518 More actions · Answer 14

no way to do types, views , procs or functions automatically...you have to edit them and make their definitions find-and-replace ready for editing.

the originals typically may reference items in the original dbo schema;

I've only got one UserDefinedDataType that I use...it's basically a char that allows preceeding zeros, but only numeric values;

it's bound to a rule as well.

i think you'll have to script them out manually, and edit them manually, and isnert them before you add procs or columns bound to the data type; i cannot think of any way to do it otherwise.

To get just this one simple thing to work in a schema, i had to change the table definition to explcitly have the Orange.numchar datatype, and edit a lot of other stuff .

for example, here's my simple data type.

CREATE RULE [dbo].[NumericCharsOnly]

AS

--@value NOT LIKE '%[0-9,-+]%' ESCAPE '!' --bad way...minus and spec chars need to be first!

@value NOT LIKE '%[^-+,0-9]%' ESCAPE '!'

GO

--create a "type" , and bind the rule to teh type

CREATE TYPE [dbo].[numchar] FROM [varchar](20) NULL

GO

EXEC sys.sp_bindrule @rulename=N'[dbo].[NumericCharsOnly]', @objname=N'[dbo].[numchar]' , @futureonly='futureonly'

GO

--a simple test table.drop table example

create table example(exampleid int identity,test numchar)

--insert some test data.

insert into example(test) values ('0000')

insert into example(test) values ('00a00') --fails! all is good

insert into example(test) values ('0000&444') --fails as expected

insert into example(test) values ('-0000') --works!

insert into example(test) values ('+0000') --works like t want it too

drop table example

drop type [numchar]

drop rule [NumericCharsOnly]

and here's what i'd have to script to get it into my "Orange" schema:

CREATE SCHEMA [Orange]

CREATE RULE [Orange].[NumericCharsOnly]

AS

--@value NOT LIKE '%[0-9,-+]%' ESCAPE '!' --bad way...minus and spec chars need to be first!

@value NOT LIKE '%[^-+,0-9]%' ESCAPE '!'

GO

--create a "type" , and bind the rule to teh type

CREATE TYPE [Orange].[numchar] FROM [varchar](20) NULL

GO

EXEC sys.sp_bindrule @rulename=N'[Orange].[NumericCharsOnly]', @objname=N'[Orange].[numchar]' , @futureonly='futureonly'

GO

--a simple test table.drop table example

create table [Orange].example(exampleid int identity,test [Orange].numchar)

--insert some test data.

insert into [Orange].example(test) values ('0000')

insert into [Orange].example(test) values ('00a00') --fails! all is good

insert into [Orange].example(test) values ('0000&444') --fails as expected

insert into [Orange].example(test) values ('-0000') --works!

insert into [Orange].example(test) values ('+0000') --works like t want it too

drop table [Orange].example

drop type [Orange].[numchar]

drop rule [Orange].[NumericCharsOnly]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!