Newbie: Copy Database But Not Tables
Oct 1, 2007
Hi,
I am using SQL 2000. I have two DB's one is live and the other is developement.
I need to copy the live DB over the developement DB but I can't overide certain tables.
How do I go about? Do I need to this using transact?
View 3 Replies
ADVERTISEMENT
May 24, 2007
Hi,
I am very new to SQL Server and am just starting to look into replication as a possible solution to a problem I have. I have a local database which will be running on either MSDE or SQL Express 2005. I have a database on a webserver running SQL Server 2005 Standard which I wish to keep as an exact copy of the MSDE/Express one. If data is changed in the local db then I want the webserver db to be updated with this data, preferably as the change occurs, but it could be on an hourly schedule if required.
If data in the webserver db is altered then I do not wish for it to update the local db, but I would like it to be assumed that the change is erroneous and reset the item to whatever exists in the local db. In other words, should a table be erroneously dropped or something from the webserver db then the table would be recreated when it syncs with the local db (rather than propogating the change back to the local db and thus losing local data).
Basically I want the web db to be an exact mirror of the local db as often as possible. Given that the webserver is running SQL Server 2005 Standard and the local machine will be running either MSDE or SQL EXPRESS, is this achievable and what would be the best way of achieving it? Should I use merge replication or database mirroring? I have succeeded in getting 1-directional merge replication working by setting the webserver up as a publisher and distributor and setting up a push merge subscription on the local machine with an ExchangeType of 1. However, this won't correct the data in the web db if it is compromised until it is altered locally.
It crossed my mind that I could write a small application to utilise tablediff to compare each table in the DBs and get it to correct the web db if required on a regular basis. Would this be a silly approach?
I would appreciate expert advice!
Max
View 1 Replies
View Related
Nov 1, 2006
I'm using SQL Server Management Studio Express and I'm trying to figure out how to copy a table(s) from my local database to my web hosting database. I know how to do it in 2000, but it's completely different now. Is this feature not allowed on SSMSE? If so, then how do I deploy database tables to a web host?Also, how do you add local database(s) to SSMSE? I tried to use 'attach database' in SSMSE and it wouldn't allow me to navigate to My Documents folder where the database resides. Thanks...
View 8 Replies
View Related
Apr 22, 2004
Hi,
I have 2 databases and each of them has different tables. I want to copy all of tables of database A to database B while keeping the existing tables of database B.
What is the best solution?
Thanks
View 3 Replies
View Related
May 17, 2015
copy the tables of a database that will keep the indexes,constraints etc.? I'll be using SQL 2008 or Visual Studio 2008. The basic SSIS package will not work since our databases are being updated from time to time. (e.g. new columns/tables meaning we need to redo the SSIS package everytime there are changes).
View 4 Replies
View Related
Apr 18, 2006
Hi!
Can someone tell me how I can copy my database which is made with SQLExpress or the tables to another SQL server. The servers could be SQL server 2000 and SQLExpress.
Thanks!
View 5 Replies
View Related
Jul 4, 2007
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
View 10 Replies
View Related
Jul 28, 2007
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
View 10 Replies
View Related
Apr 3, 2008
1) Scenario
Source Oracle database with 400 tables, each table has a field 2 common fields
Change_date and Company_ID
2) Required
To copy 50 tables to at least 2 different SQL Server databases based on the
Change_date and Company_ID
for Compnay_ID = €˜A1€™ and Change_Date = Last_Working_Date , copy 50 tables to Database1
for Company ID = €˜A2€™ and Change_Date = Last_Working_Date , copy 50 tables to Database2
Also I am not copying all the fields from each table, just a subset of fields
3)What I have done so far to create Database1
a) Created a SSIS project
b) Create 5 individual packages for the project.
c) Each package has 10 pairs of OLE DB source to OLE DB source representing the transfer of data for 10 individual tables.
d) For each data source, I use SQL command as the data access mode
E.g
Select field1, field2, fiedl10 from Table1 where Compnay_ID = €˜A1€™ and Change_Date =
Last_Working Date
4) What I want to do
However, since the where condition is the same for all tables (Compnay_ID = €˜A1€™ and Change_Date = Last_Working Date) , I want to find a way to set this as variable, so if the where condition changes, I only have to change it once.
Any assistance with this would be greatly appreciated.
<!--[endif]-->
View 3 Replies
View Related
May 4, 2007
Hi,
I want to know how to copy tables and data from one database to antoher database including table permissions. Presently i am using Integrity security services. Is it having any option in Integration services or sqlserver 2005.
Thanks
View 7 Replies
View Related
Jun 4, 2015
how to copy tables from old database to new database in sql server.
View 6 Replies
View Related
May 29, 2006
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
View 5 Replies
View Related
Oct 6, 2015
I would like to know if is possible copy data from a database located in my hosting to my database located in my pc,but using a store procedure,how can i do that ?.What tool i have to use?
Sql server 2008
View 2 Replies
View Related
Feb 24, 2007
Hi there
I sorry if I have placed this query in the wrong place.
I'm getting to grips with ASP.net 2, slowly but surely!
When i try to access my site which uses a Sql Server 2005 express DB i am receiving the following error:
Server Error in '/jarebu/site1' Application.
Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.
Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735075
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
This is the connection string that I am using:
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;Initial Catalog=ASPNETDB;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
The database is definitly in the folder that the error message relates to.
What I'm finding confusing is that the connection string seems to be finding "aranga"s database.
Is it something daft?
Many thanks.
James
View 1 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
Jan 6, 2007
Hi all,
I have read/studied (i) Working with Databases in Visual Web Developer 2005 Express in http://quickstarts.asp.net/QuickStartv20/aspnet/doc/data/vwd.aspx, (ii) Xcopy Deployment (SQL Server Express) in http://msdn2.microsoft.com/en-us/library/ms165716.aspx, (iii) User Instances for Non-Administrators in http://msdn2.microsoft.com/en-us/library/ms143684.aspx, and (iv) Embedding SQL Server Server Express in Applications in http://msdn2.microsoft.com/en-us/library/ms165660.aspx. I do not understand the concepts and procedures to do Xcopy and User Instances for non-administrators completely-I do not know how to connect to databases and create database diagrams or schemas using the Database Explorer. I have a stand-alone Windows XP Pro PC. I have created a ChemDatabase with 3 dbo tables in the SQL Server Management Studio of my SQL Server Express and a website of my VWD Express application with an App_Data folder. I am not able to proceed to use Xcopy and user instance to bring the 3 dbo tables of ChemDatabase to my App_Data folder. Please help and give me some detailed procedures/instructions to bring the 3 dbo tables of ChemDatabase (or ChemDatabase itself) from the SQL Server Management Studio Express to the App_Data folder of the website of my VWD Express project?
Thanks in advance,
Scott Chang
View 3 Replies
View Related
Oct 13, 2007
Hi, I am creating a table called products, which sellers will post their items to. How do I create a column which auto fills according to which user posts the product? Thanks, Jon
View 4 Replies
View Related
Sep 3, 2002
Hi all...
I noticed that when transferring tables from the local server to the remote server that some colums lost its properties ... For example, If I am sepcifying a column to have identity seed and identity increment, it is transferred to the server without these properties...
How make a carbon copy of tables from my local to the remote server ?
Thanks in advance
View 1 Replies
View Related
Feb 1, 2005
Hi. I just set up my first sql server database and I've managed to connect to it via ASP as a test.
I'm not sure how to add data to my tables. In MS Access, you can edit the table and add records. How do I do that in SQL Server?
I'm using the Enterprise manager tool to create tables.... does it have something i can use?
Thanks
View 6 Replies
View Related
Jan 16, 2004
Hi,
I am new to SQL and these forums, and have a quick simple question.
I am trying to view the records that do not match in 2 tables. I have tried a few different ways but keep getting results in the hundreds of thousands and the table only has 36 thousand reocrds.
Here is an example of what the two tables contain.
Table 1
customer_no eaddress_no name address (36000 records)
Table2
customer_no eaddress_no email (17000 records)
I need to find out which customers are not in table 2 by linking the eaddress_no numbers.
This is one of the scripts i ran that gives me the large results with a ton of dups:
SELECT *
FROM T_EADDRESS inner JOIN
T_CUST_LOGIN ON T_EADDRESS.eaddress_no
!= T_CUST_LOGIN.eaddress_no
Thanks in advance for any help with this.
View 13 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
May 26, 2007
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
View 4 Replies
View Related
Sep 12, 2007
Hi All,
We are post-deployment with a serious reporting issue that's causing us to rethink our reporting solution. We're considering moving from what we have to SQL Server Reporting Services (client side).
I have spent the past couple of days getting up to speed on this feature and seeing if we can easily migrate it into our existing application. However, I've hit a couple of stumbling blocks and was hoping perhaps someone here could either tell me the solution or point me in the right direction.
I'm not having any problems creating basic reports (e.g. flat data).
However, I am having problems creating reports where there are related tables in a dataset.
The way it works with our existing solution is that I get a dataset (which contains several data tables) and point it to the report's datasource. That report expects those tables and I have defined table-relationships in the report which process and display the information correctly.
I'm not having as much luck with RDLC.
I can go into futher detail about how I'm creating the report, but let me just ask these general questions first:
1. Can I set a dataset containing multiple datatables equal to a property on an RDLC report and that RDLC report know how to treat and display the data?
2. Is there a better/smarter than this to get a field selection from my datasource (remember, this information is coming from a stored procedure so connecting directly to the database is not an option):
a. In code, populate dataset
b. In code, write dataset schema to xml (e.g. an xsd file)
c. In Visual Studio, add the XSD file to project
d. Use fields from XSD file to drag and drop fields on report
A fancy example would be nice too. I've googled like crazy the past couple of days and downloaded as many samples as I can find (including the ones on ftponline.com, gotreportviewer.com, "Tudor's WebLog", and several others). However, I have yet to find one that uses grouping and related datatables.
Thank you so much (if, for nothing else, reading this post )
View 4 Replies
View Related
Aug 14, 2015
I need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?
View 3 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
Feb 10, 2007
Is there anyway I can copy tables and stored procedures from one MS SQL server database to another? I presume there is, so how is it done.Thanks
View 6 Replies
View Related
Oct 3, 2007
I have two databases and want to copy tables (with table data) and stored procedures from one database to another.both databases have the same name (so management studio is not letting me have both up)....what should I do?thanks.
View 5 Replies
View Related
Apr 1, 2002
What is the easiest way for me to copy my database tables so that I could use the copy as a template to work on for setting primary keys, and normalizing the data?
Thank you in advance for your help.
Shari
View 1 Replies
View Related
Aug 6, 2002
A question from a beginner. Is there a simple command to create a copy of at table with its original attributes. I would like to take a table for a year and copy it to an archive file with the year appended to the name, i.e. myfile copied to myfile02
View 1 Replies
View Related
Dec 10, 2000
I need a job to copy data between 2 databases on the same server. (2nd one will be used for reporting purposes, and I'll run my job nightly)
Can anyone at least point me in the right direction for Transact-SQL statements or which process to start with.
I tried a DTS job but it fails on the schedule. Only runs immediatly but then fails.
Thanks..
View 1 Replies
View Related
Apr 15, 2005
Hello all
I have a table named Configuration and I want to make a copy of this table.
I already have the table with the same structure named Configuration1. All I want to do is copy data from Configuration to Configuration1
How do I do it? I tried various queries:
SELECT *
INTO configuration1
FROM configuration
GO
Results:
(395 row(s) affected) but configuration1 is empty.
----------------------
Create configuration1 like configuration
Results:
Error near like
------------------------
So how do i achieve this?
Thanks much guys/girls....
View 2 Replies
View Related
Sep 12, 2006
Hi
I have a DB2 database server.It has 2000+ tables...And I have a MS SQL database server.It has got the same tables.With same schema...
I want to take all of that tables datas to MS Sql database's tables....
I try to use DTS Import Data Wİzard but it gace an erro which is Unknown Error!!!
Now;
I try to make a DTS which is capable with doing this mission...
I want to create one DTC package and the source table and destination table names will be changed dynamically...Probably i should use ActiveX Task or Dynamic PropertTask...
Despite i have searched from net very much i couldnt find any helpfull article or sample for 3 days...I have also checked this one;
http://www.sqldts.com/default.aspx?246
But it hasnt helped me too..
I really wish thatsomebody can help me...
Thank u all
Osman AYHAN
View 5 Replies
View Related
Dec 7, 2007
Hi everyone!
I want to copy from DB certain tables to an other DB. How can I do that with SQL Server 2005. I would prefer an SQL string solution instead of buttonclicking solution.
Thanks a lot and greetings from Vienna, Austria
landau
View 6 Replies
View Related