I'm looking to create a new version of a database but with completely reloaded data - so I want to retain the database structure but none of the data.
Is there an easy way (read: without a DBA on staff) to copy an existing database structure - preferably with views and stored procedures - and not take the data along for the ride?
Thanks in advance for suggestions...!
I have been working on my windows app, using SQL Server 2005 (& C#) but I now have loads of junk data, so I want to copy the structure without any data to a new set of files that can become the production version.
I assume I can use backup and restore in future when I want a copy of my production data to use for future testing.
How would I take just one table from my test database and add it to my production database without retyping the design?
Apart from the size limitation is there any advantage in buying a version of SQL Server over using the Express edition?
Is there a way to copy the structure from one database to anotherwithout affecting the actual data?For example, I added new fields and stored procedures to a db in mydevelopment environment. There are a lot of them.I now want to make the same changes to my production environment db butdon't want to affect the data. Only the fields, stored procs &constraints and stuff pertaining to the structure of the db should bechanged.Does anyone know of a program that can do this or can this be done inMS SQL Enterprise Manager.Any help would be appreciated.7078895
View 2 Replies View RelatedCan anyone tell me;
Is there anyway to copy an entire database structure (layout of column definitions and character formatting)?
Or do I have to delete the relationship, then the child first?
For testing purposes, if I screw one up, I would like to be able to start with another copy.
Thanks
Hi all,
I want to copy a production database schema to an archiving database, only
the structure : tables, views, defaults, rules,UDTs. For achieving that
I am trying to use SQL/DMO functions, "CopyAllTables" for example. My script works with no error but unfortunately the destination database
contains no objects. Why, have I missed something ?
Does anyone know how to do that?
Many thanks for your help
Regards,
Herve
hmeftah@aol.com
We have SQL Enterprise Manager (8.0). Is there any way to Copy a Table from one database to another with only the Structure (design) - not all the Data?
I can't find any option in the Import Data Wizard that only copies the Table structure.
Any help is appreciated. Thanks
Hi,
I have a complicated sql server mobile database (.sdf) and need to create a SQL SERVER database with the same tables. How can I do it without scripting the whole thing? I thought of using the views.information_schema databases, but it is still a lot of coding.
thanks
I have written 5 content-management systems and am getting a little bored writing a lot of the same functionality and tweaking it for different datamodels. Is there a way within .NET to have visibility of a database's structure (ie, data type, column names, foreign keys etc)? I'd like to write up a dynamically generated form for any given table, displaying appropriate form controls based on data type and foreign keys.
View 7 Replies View RelatedHi all!I have an application that needs to copy the database structure fromone database to another without using the "Generate SQL Script"function in Enterprise Manager. I'd like to do this from within astored procedure. Can someone recommend the best approach for this?I've seen references to using SQL-DMO from a stored procedure using thesp_OA* procs in other postings to this group but was wondering if therewas an easier way? Can I use bcp and then use xp_cmdshell from withinmy stored procedure? It's not clear to me from the documentationwhether bcp copies both structure and data or just data? Is there abetter way?Thanks in advance for any help!Karen
View 1 Replies View RelatedI just installed SQLEXPRESS on XP Prof. It is working fine. I want to know if there is a way to copy a table structure so I could create clones. I need about 300 tables with identical structure but different name for each one.
Also I am wondering if there is a way to ADD a table to SQL database. It is kind of a long shot but I will try to explain. I have about 200 tables in old Visual dBase format (v7.1). I would like to convert them to SQL Server tables. I found a company that offers an app that converts them to MySQL. I tried and it kind of works. I could open the output file in SQL Server but it is not a table but a kind of csv file with INSERT commands. I had to run it. So far it did not compile but in the future if those bugs (in their app) could be corrected I may end up with a single table which I may want to include in one of my databases. Is it possible?
Thanks.
hi
how can i copy a table structure from a database to another(without bakup)?
Hi,
In SQL Server, Is there any DDL available to copy a table structure alone and not the data,
I believe 'SELECT * into new_table from table1' will copy both structure and data as well.
Please advice,
Thanks,
MiraJ
Is it possible to purge all records in the database while retaining the the table structures. Even better yet, could I do it on a table by table basis? If I simply delete all the records the identities for the tables do not revert back to 1.
View 2 Replies View RelatedI need to make something that copies all tabels (structures + data) from a progress database into another mssql2000 database.
THANKS!
How does one generate Transact-SQL for table structure in SQL server express 2005?
I have very limited access to an SQL server database (using rudimentary web based interface) for my hosted website. I am ready to deploy an application that was developed with Visual Studio 2005 using SQL server express as the database. I have four tables that I want to copy (structure only) from "express" to the hosted SQL server. I think I need to generate Transact-SQL statements to "create" the table structure, then run these statements as a stored procedure on the web based SQL server. I'm having trouble trying to figure out how to generate the code from SQL express 2005. Anyone had any experience with this?
thanks.
Hello Room,
M stuck with an issue with SQL Query. My requirement is to create a temporary table with the out put of "SELECT" Clause..........
Like.......
SELECT NC_ID, AUD_PLAN_ID, AUD_CLAUSE_ID FROM AUD_NC
This is my "Select" Query and all I want is to store the out put in a #table. Now one way is to craete #tmp_table with the fields same as the out put column of the "SELECT" Clause......... But I want it in direct fashion.... dat means without Creating the #tmp_table directly...........
I tried with "CREATE TBALE #tmp_table AS SELECT NC_ID, AUD_PLAN_ID, AUD_CLAUSE_ID FROM AUD_NC".......... But it dini work
Please help me out in this regard......
I represent a software development house and we have developed a client server system based on SQL Server. Most of our customers have already purchased Enterprise License of SQL Server, therefore they own the SA Login and Password. We are bound to attach our Database with their Server on their machine.
My question is how can we stop a System Administrator of SQL Server to view our Database Structure, Queries, Data installed on their SQL Server on their machine.
Our database structure is a trade secret and we cant reveal the structure to the client.
please answer this question by email to me at farhandotcom@gmail.com
Thanks & Regards
Farhan
If I want to copy the data from Table1 in Database A to Table2 in
Database B but Table1 column name is code , Table 2 column name is
vesselcode.
(Code = vesselcode)
How to copy all data from Table1 in Database A to Table2 in Database B ?
Do I need to write the SQL statment ? and Can I use Server Enterprise
Manager Tool?Thx a lot.
Hi Team,
We need to get the data from our third party CRM vendor. CRM vendor will provide us with .txt file on their folder each night and we need to get those data into our database. I know that SSIS should be able to do this for us. Is there any tutorial of how to do this job in SQL Server 2005? Thanks in advance.
Hi,
This requirement is for our development and test environment. We have a database server which we use for development hence many changes are done on the database objects on daily basis like tables def changes, view def changes, sp changes, new sp etc.
On the other hand our testing team connects to another database server where they perform testing hence any internal test release would need the changes like tables def changes, view def changes, sp changes, new sp etc.
Since testing team already have their test data set in the test environment hence they want to keep their data but new test release should only provide with datbase object changes. The difficult part is, we have over 500 tables and about 1000 views and somewhere about 400 stored procs, so its not possible to manually perform the above task.
I dont do database admin job hence clueless as what would be the best way to accomplish the above. Pl advice.
we are using sql server 2005 on both test and dev environemts.
cheers!
d2
Hi all,
We have a website with a database connected to a SQL server. I need to copy that database without the data onto a CD so I can place the program on another server?
Any help would be greatly appreciated.
Hello all,
I'v got a database full with an administration. This database needs to be protected and can not be accessed from the internet. Now we are making an internetpage for the employees and i want to read from the database who's working here.
For the security i want to copy some database data from one database to another... Is that possible? With a script ore something?
Thanks i advance!
Willio
How do i make a new database file and then copy all the tables and data out of an old SQL file into it? I just want to see if rebuilding the file helps with a corruption issue i have been having with the log file.
brandon jelinek
www.ccgspokane.com
I am working on a migration project.
Doing the standard processing, taking source data in a staging database, where I then create the new target tables, transform the source data into the new target table structure, and load the data.
However, having created the new target tables in my staging database, I cannot accurately migrate these tables into the new database.
An example table.
The following is the script created by SQL Management Studio if I right click and script the table as CREATE.
CREATE TABLE [dbo].[tbPRO_Package]( [PRO_PackageID] [int] IDENTITY(1,1) NOT NULL, [CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedDate] DEFAULT (getdate()), [CreatedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_CreatedBy] DEFAULT (suser_sname()), [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedDate] DEFAULT (getdate()), [ModifiedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tbPRO_Package_ModifiedBy] DEFAULT (suser_sname()), [GUID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tbPRO_Package_GUID] DEFAULT (newid()), CONSTRAINT [PK_tbPackage_ID] PRIMARY KEY CLUSTERED ( [PRO_PackageID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
Note that the field PRO_PackageID has an identity, and is a primary key.
Also note the constraints on several fields and the default values.
If I try to move this table and data using the SQL Server Import and Export Wizard, telling it to drop and create the table, and to keep identity, it does not correctly generate the table defintion - it generates the following script
CREATE TABLE [Migration Staging].[dbo].[tbPRO_Package] ([PRO_PackageID] int NOT NULL,[PackageCode] varchar(8) NOT NULL,[LKU_VatRateCode] nchar(10),[CancellationCharge] decimal(18,2),[CancellationMultiplier] decimal(18,2),[CreatedDate] datetime NOT NULL,[CreatedBy] varchar(50) NOT NULL,[ModifiedDate] datetime NOT NULL,[ModifiedBy] varchar(50) NOT NULL,[GUID] uniqueidentifier NOT NULL)
We've lost the identity, the PK,the constraints and the defaults.
If I use the SSIS Transfer SQL Server Objects Task, then the primary key is kept, but the identity is not, nor are the constraints or defaults.
Am I missing something? Is it actually POSSIBLE to accurately move tables between databases?
I have database1 and database2. Most table names are the same between both dbs.
I would like to copy a column from db1 into db2 but since the data requirement on db2 is different i would like to change it before inserting.
Example:
Data from db1: <SomePage id="1" <template>This is a test </template>
When it saves to db2 it needs to be
<Page ident="1" <content>This is a test </content>
Is this possible at all?
Hello everyone. i am using vwd 2005 express edition along with sqlexpress 2005. if i need to use database in my project. i click on database explorer and create a new table from the existing database. it is working very fine. But my problem is when ever i copy the project in my pen driveand try to use in another computer, the database doesnot gets copied when i copy my project folder. well i also tried to copy the tables from the database explorer but it does not happen.i tried to locate where does the database get stored in my computer.but i cant find where it resides..and even if i find which file to copy?i think there is log file and database file in some cases. can some body clarify me this scenario here.just tell me where can i find my database i m using in my project,so that i can copy it . and also whats the use of creating .mdf file in the project.?is this the better way of using database rather than creating using database explorer?confused?? thanks.jack..
View 10 Replies View RelatedHi there.
Here is my problem: I'm trying to copy a few tables from an ODBC database located on a PC on my LAN to a SQL Server on a remote server.
To do this I have created a DTS package on the SQL Server which runs smoothly, copying the data as it should, but only as long as it copies the data from the ODBC database on my own computer where I have Enterprise Manager installed. The moment I try to copy data from another PC in the network where Enterprise Manager is not used to execute the DTS package the task fails to run.
I'm quite at a loss here and would very much appreciate a notch in the right direction. Perhabs some source or book which I may read to solve the problem.
/Bakkegaard
1) I can't get the 'copy database' function to work from SQL Server 2008 to SQL Server 2005. I connect ok. Everything goes to the last step and then it fails.2) I cant get a SQL server 2008 backup to restore on SQL SEerver 2005 either.
The only way I know that works is to script the creation of all tables then export and import. This does work.
How can I get the 'entire' database, structure and data, from 2008 to 2005?
ThanksSQL newbie.
i want to copy some tables from 1 database to another with its data & procedures etc to another database in Microsoft SQL 2005.
can any one help
plz reply
tnx alot
I want to be able to reproduce my production execution plans on development with copying data.
Hi,
I'm new to SQL Server 2005 SSIS. I'm trying to do something very simple, but I cannot figure it out, PLEASE HELP!
I have a flat file, which I read and then insert the data in a database table, that works fine. The problem is that I don't want to insert duplicate records. For example; if I run the package again, it will appent to the table. What I need to do is that if the package runs again, check if the record already exist, based one two columns, date and hour, and do not insert the record.
Thank you,
Aldo
I work on a copy of SQL Server Express on my desktop. After modifying and creating views and user defined functions, I would like to copy and paste them into the working database. Is there a method programmatically of doing this or must I copy and paste the t-sql language from the existing view to the new database--then save the new view on the working database?
View 6 Replies View RelatedHi,
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