Copy Schema
Apr 18, 2006
Hi,
What do I need to do in order to make a copy of a schema. For example lets say my database has 3 schemas - Test, Production and Staging. I want to make an exact copy of Production. ie. complete copy - all objects - tables, views, stored procs and also all the data and probably call it Production-New. Now we have 4 schemas in the database.
This needs to be initiated from the app that the user is currently using. Is there any built in support (TSQL functions/commands/Sps ?) for such an activity ? and if not what may be the direction that I need to look at to take this further ?
Overall I'm looking at being able to take a quick snapshot of a schema and start using that. I must mention that the volume of data will be substancial and the solution devised should be able to do it rather quickly - The user clicks a button in order to take a snap shot. So the wait time has to be reasonable.
Any suggestions/ideas will be great.
Thanks -
Avinash
View 3 Replies
ADVERTISEMENT
Nov 21, 2011
I am using sql server 2008 R2.I want to copy all the objects of one schema and put it in another schema. I want to do that from command prompt.
In oracle we can export the objects of one user and import to another user using exp and imp. I want similar type.
View 5 Replies
View Related
Feb 4, 2002
Hi,
Can anyone help me to figure out how to generate the SQL Script that can produce the same database schema with the existing database?
I have tried the "Generate SQL Script" tool. However, it only creates the object such as tables, stored procedures, triggers and etc. I still have to create the database first in order to run that script. I have an existing database that has a 2.7 GB size (let's called this ABC Database). In a different server, I would like to create the same database with the ABC DB without having to restore the ABC DB. Bottom line, I would like to see the script creates the database along with all stored procedures, triggers and the size of the database will be 2.7 GB. Is it possible?
Thank you very much!
Jane
View 2 Replies
View Related
Aug 9, 2004
I need to create some sort of method to copy the schema and data from an existing database and recreate that database schema and data at another location i.e. customer site. I would like to be able to start the rendered script from a command line. I will be very appreciative of anyone who can get me started in the right direction.
Thank you,
iRead
View 1 Replies
View Related
May 20, 2008
Hi All,
I want to migrate data from old DB to New DB. New db is having schema changes. For e.g Table A in Old DB is split into
two tables A1 and A2 in new DB. I want to copy some columns from Table A to A1 and some into A2. A2 is having relation with A1(A1's ID is FK in A2). I want to insert records into A1 and using the identity column of newly inserted record, i want to insert the record into A2. Please guide me how can i do this with SSIS.
Thanks in Advance
Devika
View 3 Replies
View Related
Feb 12, 2015
I have a database DB1 and DBTest
DBTest Database is empty. I want to copy all the objects in DB1 - schema1 how can I do that?
View 2 Replies
View Related
Jan 24, 2008
I created my database on a remote server. It now has lots of tables and stored procedures
When I created it, the server created a schema named for me, the user, so all my tables and stored procedures are named like johndoe.tablename.
I would like to rename the schema to something less personal more professional.
Can it be as simple as "ALTER SCHEMA johndoe RENAME professional"?
Also, I would like to create a local copy of the databse, so I can develop offline, without the 2 second delay.
SQL Server Management Studio Express lets me generate scripts, which I have been using to make backups.
Can I use the script file to recreate the database on my local machine?
Thanks
View 1 Replies
View Related
Sep 27, 2007
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
View 4 Replies
View Related
May 27, 2008
I have 35+ tables and 15+ stored procedures with SchemaA, now I want to transfer them to SchemaB.
I know how to do one by one...!
alter schema SchemaB transfer
SchemaA.TableA
but it will take long time...!
Thanks,
View 3 Replies
View Related
Apr 12, 2008
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
View 2 Replies
View Related
May 8, 2007
Hello,
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
Please give me some advices here.
Thanks.
View 3 Replies
View Related
Apr 19, 2006
I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin
View 3 Replies
View Related
May 11, 2006
Hi,
I set up DB mirror between a primary (SQL1) and a mirror (SQL2); no witness. I have a problem when I issue command:
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
The error message is:
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.
I have the steps below prior to the command. (Note that both servers' service accounts use the same domain account. The domain account I login to do db mirror setup is a member of the local admin group.)
1. backup database DBmirrorTest on SQL1
2. backup database log
3. copy db and log backup files to SQL2
4. restore db with norecovery
5. restore log with norecovery
6. create endpoints on both SQL1 and SQL2
CREATE ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER)
7. enable mirror on mirror server SQL2
:connect SQL2
alter database DBmirrorTest
Set Partner = N'TCP://SQL1.mycom.com:5022';
go
8. Enable mirror on primary server SQL1
:connect SQL1
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
This is where I got the error.
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy
Thanks for any help,
KT
View 8 Replies
View Related
Feb 28, 2008
Hi!
I did:
alter database mydb set single_user with rollback immediate;
exec sp_detach_db @dbname='mydb', @keepfulltextindexfile='true';
then I tried to copy files to new location on other drives, same server but got
>>Cannot copy <myfile>: Access is denied
Make sure the disk is not full or write-protected and that the file is not currently in use<<
I also tried rename of file without success.
I also tried with db service stoppet (not preferred) without success.
How to find out, which process locks the files?
Best regards
View 7 Replies
View Related
Aug 30, 2005
How do I transfer/copy the stored procedures in my Test DB to my LIVE DB? IT won't allow me to export keeps giving me an error.
View 4 Replies
View Related
Nov 4, 2006
Hello,
if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.
Or is the datebase name part of the .mdf file?
Regards
Markus
View 6 Replies
View Related
Jan 23, 2012
I am attempting to use the copy wizard to copy databases from SQL Server 2005 to SQL Server 2008 R2 w/ FP1.
The copy fails with a login failure to SQL Server 2005. I have a user id & password under Windows for both servers. I have a user id and password under SQL security with the called for admin security rights.
The 2005 server has two instances, 20 databases, two dozen maintenance plans, and over a hundred users. I really would like to use the utility so I don't have to recreate everything manually.
View 1 Replies
View Related
Feb 1, 2007
Hi~,
Before implementing memory based bulk copy insert with IRowsetFastLoad interface of SQL Server 2005 OLE DB provider, I want to know some considerations.
- performance : compared with T-SQL's "BULK INSERT ..." and bcp utility
- SQL Server's resource usage : when running memory based bulk copy, server resource's influence
- server side action(behavior) : when server is busy, delayed-update means IRowsetFastLoad::Commit(true) method can insert right after?
- row-count : The rowcount limitation can be inserted by IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit
- any other guide lines
View 1 Replies
View Related
Feb 15, 2007
Hi~, I have 3 questions about memory based bulk copy.
1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)?
For example, how much insert row at below sample?(the max value of nCount)
for(i=0 ; i<nCount ; i++)
{
pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData));
}
2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)
3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ?
BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);
-------------------------------------------------------
My solution is like this. Is it correct?
// CoCreateInstance(...);
// Data source
// Create session
m_TableID.uName.pwszName = m_wszTableName;
m_TableID.eKind = DBKIND_NAME;
DBPROP rgProps[1];
DBPROPSET PropSet[1];
rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].colid = DB_NULLID;
rgProps[0].vValue.vt = VT_BSTR;
rgProps[0].dwPropertyID = SSPROP_FASTLOADOPTIONS;
rgProps[0].vValue.bstrVal = L"ROWS_PER_BATCH = 10000,TABLOCK";
PropSet[0].rgProperties = rgProps;
PropSet[0].cProperties = 1;
PropSet[0].guidPropertySet = DBPROPSET_SQLSERVERROWSET;
if(m_pIOpenRowset)
{
if(FAILED(m_pIOpenRowset->OpenRowset(NULL,&m_TableID,NULL,IID_IRowsetFastLoad,1,PropSet,(LPUNKNOWN*)&m_pIRowsetFastLoad)))
{
return FALSE;
}
}
else
{
return FALSE;
}
View 6 Replies
View Related
Jun 25, 2001
SQL 7.0 SP3 replicating a database from a publisher to a SQL 7.0 SP 3 subscriber.
Is there a way to change publisher schema without stopping the replication? Example Add a 'bit' column to a table with default of '0'.
View 1 Replies
View Related
Jun 3, 2004
Hi all,
I want to set up a db for an e-commerce site. I need to know how to set up the db correctly with out getting anything mixed up ... 'cos I never done an e-commerce db b4:
I need to know where and how to store the passwords, the product pictures and the customers delivery address which is different from the billing address.
The tables are as follows...
--Customer--
CustID
Email (email & pwd is for login)
password (will this be secure here)
Name
Address (should it all be in one column or firstline,secondline,zipcode columns)
DeliveryAddress
--Product--
ProductID
ItemName
Catogary
Price
SellingPrice
Quantity
ItemsPicture...(not sure where to link the pics to)
DistributorsID (the warehouse who dispatches the item)
--Distributors--
DistributorsID
Name
Company
Address
It would be much appreciated if you could share some info and tips for how to set it up all correctly.
Tools I'll be using are, ASP.NET(C#), ADO.NET, MSSQL (Stored procedures)
Thanks In Advance
View 4 Replies
View Related
May 28, 2007
HI All,
What Is Schema in sql Server 2005 ?
View 4 Replies
View Related
Nov 23, 2007
Good afternoon
could someone tell what is a schema is when using within SQL SERVER 2005
Many thanks
Rob
View 3 Replies
View Related
Aug 26, 2005
I know in SQL Server the terms Database and Catalog are usedinterchangably. But a table is also assigned a schema. As seen in theINFORMATION_SCHEMA.Tables View. I don't get what this schema qualifieris all about. Like if a table has a schema of dbo.Can someone explain the relationship the schema has and what it is?Thanks.
View 10 Replies
View Related
Nov 8, 2006
I have setup merge replication between two machines and the data seems to be updating when changed on either side ok. However I am now looking at how I can handle schema changes. I have found on msdn (although not tried) stored procedures that will add and remove columns, but what happens if I want to delete a table for example.
At the moment I can deselect the table being deleted from being published and then delete it or modify it in Management Studio, which then means I need to create a new snapshot which can take sometime before the change is updated on the subscriber.
Is there a better way to tackle schema updates that I have not found yet? Also, how will the subscriber cope if someone starts modifying data while synchronisation to the new snapshot is going on.
I am using SQL 2005 by the way.
Thanks
John
View 1 Replies
View Related
Oct 17, 2006
Hi,
I am new to SQL Server and started it with SQL Server Express.
I tried unfruitful to know about 'Schema' in SQL Server.
I have to mention it as prefix for table name in SQL query. But if it is in "dbo" schema, it is not required.
Please tell me about it..
Thanks
View 1 Replies
View Related
Oct 28, 2006
Hello I am a software developer with minimal SQL server administration skills. Currently I am using SQL Server 2000.I need to know if there is a way to copy a particular table from a database, and to copy the table into a different database.Basically on a project I am working on we are using a table named "Customers" from a database named QTR. We need to copy this database table into a different database named "Research". How can this be done? Is if very complicated?
View 1 Replies
View Related
Nov 19, 2006
I wrote a website that hits an SQL Server DB using queries that directly acces the table name with no Schema references. (Ex: SELECT * FROM tblHelpNotes;) I have also backed the file and and somehow (I have forgotten how I managed to import it.) imported it into SQL Server Express to run at home. The problem I am having is that in order to query at home I HAVE to use the Schema or it won't work. (Ex: SELECT * FROM dbo123456.tblHelpNotes;) How can I change this so I can just hit the table directly? I have tried miserable to set up a user that uses the default schema of db123456 but I can't seem to get it to work. Any idea's to point me in the right direction?
View 2 Replies
View Related
Jun 19, 2000
Hi,
i need to move the data and schema from 6.5 to 7.0. could you please tell me the most simple way.
thank you
raju
View 1 Replies
View Related
Dec 27, 2004
Hi,
I have tried the following out and would appreciate feedback from experienced users regarding if the following is a good/bad approach:
After bring all the data in my Data Mart, I have created a view which has all the data in a big flat table (totally unnormalized). Then based on this BIG FLAT UNNORMALIZED VIEW :) I have created my various dimensions using the 1st option i.e. Star Schema.
Based on the little testing that I have done, I seem to be getting the correct results across various dimensions... However, can someone kindly comment on this approach and the pros/cons.
Thanks
View 4 Replies
View Related
Apr 28, 2005
What would be the best way to get the Schema from a complete database.
Thanks.
View 1 Replies
View Related
Sep 9, 2005
hello,
here is my problem.
I have to rebuild database after crash. there is no backup. So
I did a bcp to get data from. But I do not have the original database so my question is how to get the full schema off the data base, tables,colomns,stored procedures etc...
thanks lot for any help.
View 1 Replies
View Related
Jan 15, 2007
Hi
I come from a MySQL background and have been having some trouble finding the MSSQL command that corresponds to MySQL's "describe." I Googled for around 1/2 hour but can't seem to find it.
Any help appreciated
View 3 Replies
View Related
