Question:
How do you copy one database to another database on a separate server?
geo
2007-01-05 05:42:17 UTC
How do you copy one database to another database on a separate server?
Seven answers:
2007-01-05 05:46:00 UTC
I would recommend that you create a DTS Package to transfer the object. There was a 'Copy SQL Server Object Task' that was made exactly for this purpose. In this task you specify the source and destination servers, the source and destination databases along with the object(s) you wish to transfer. You will have to specify the exact database object(s) you wish to transfer in the copy tab of this task.



Create the DTS Package and verify that you can execute it in DTS Design Mode. Verify the view was indeed copied to the remote server. You may have to drop the destination object each time the package is executed. Save the package and then you can have the SQL Server Job scheduler automatically kick-start the package by right-clicking on the newly created package and choosing Schedule Package option. From here, you can provide the job scheduling details.



One additional point I'd like to make. I'm confused by your stating that the size of the view can be quite large over time. Views, unlike tables, do not hold data. They are simply named SQL statements we create to perhaps simplify the end-users perception of data in our database. I've also used views for security purposes to hide or exclude sensitive columns. Views, therefore, are not large because they are simply database objects that hold SQL Statements. Granted, the table(s) that view 'hits' can certainly be large.







Here are a couple options you may want look into:



Option 1: Use a linked server



First, you can create a linked server on your source server that points to the target server. Look under the security tab in Enterprise Manager so do this. Once this is done, you can access the target servers' database objects using the fully qualified path name as follows:



TARGETSERVERNAME.databasename.dbo.targettablename



If you choose to go this route, you can create and schedule a SQL Server Job on the source server that has the following Transact-SQL Steps:



Step 1: Drop table TARGETSERVER.databasename.dbo.targettable



Step 2: Select * into TARGETSERVER.databasename.dbo.targettable



From database.dbo.v_vewname



Here, the select into will automatically create the new table every time the job is executed. The schema of this newly created table will match the result set produced by selecting from the view. You can place these Transact-SQL Steps in a stored procedure that resides on the source server if you'd like and have a job automatically execute the procedure on a scheduled basis.



Here's a similar approach that also uses linked servers. The difference is that the second link-server approach assumes that you already have created the target table. This second approach uses a insert / select which is a logged statement while the first uses a 'select into,' which is non-logged. The nonlogged statement should run real fast while the insert /select approach is logged and will be slower.



Step 1: truncate table TARGETSERVER.databasename.dbo.targettable



Step 2: insert TARGETSERVER.databasename.dbo.targettable (column listing)



Select column listing ... from database.dbo.v_viewname



Option 2: Use a DTS package with a data pump



If you don't want to create a linked server definition, you can create a DTS Package that accomplishes the same thing. The DTS Package should have two connections that I will call SOURCESERVER and TARGETSERVER. It is assumed that you will manually create the target table on the target server. The DTS Package then must perform the following steps:

Truncate the targettable on the TARGETSERVER. This step must be done BEFORE the data pump step.

Create a datapump step that specifies the SOURCESSERVER as the source and the TARGETSERVER as the destination.

Configure the Data Pump as follows

The actual source specified in the data pump definition will be the source servers's view

The actual destination specified in the data pump definition should be the target server's target table.

Click on the transformations tab to verify that the source view columns and the destination target table columns map correctly.
aiguyaiguy
2007-01-05 05:57:04 UTC
It depends on which database system you are using.



PC databases Access, FileMaker, etc... usually just require a backup to CD and restore to the same directory location on the new system or a copy over the network of that filesystem.



Typically the database software must be installed there also to make sure all the proper DLLs and registry entries are there for runtime execution.



On larger UNIX databases like Oracle and DB2 a lot more configuration is usually required to recreate their environment on the target box. Also licensing issues may require repurchasing the database software in the CPUs on the new machine exceeds the number on the original machine.
Lear R
2007-01-05 06:04:59 UTC
Well it depends on what type of database you have, databases are extremely specific. If each piece of data has several files associated with it, and if the files are missing, you can corrupt your data. The best suggestion is to go to the website that concerns your database, do you have NT Server, NT Server 2000, Foxpro, Access? without knowing what type of database you have its difficult to give you a clear answer.
freemind
2007-01-05 05:55:49 UTC
well it depends on the database you are using.

At first i would generate SQL statements using export database from server 1 and i would copy that to some file.



At server 2 I would run SQL command and paste the same copied SQL statements.



It's job done.
Diane A
2007-01-05 05:47:39 UTC
It would depend on the database that you are using. If you are using something like access:

Create a new, empty database. Use File... Get External Data... Import

to import the tables; there's an option to import only the structure.

You can import all the Queries and other objects as they are.
Moein
2007-01-06 01:45:36 UTC
you should export DB from one database sever and attach it on the other database server.

open sql server,then right click on your db,then choose Export data.

on the other server go ahead just choose Import data.
2016-11-27 02:00:24 UTC
no longer common to respond to with out greater suggestions, as there is loads of subjects to evaluate. some that during the present day spring to ideas: what proportion clientele are probably to be getting access to on a similar time? How frequently is a client updating fairly than basically viewing? while a client is updating, how long is a checklist lock probably to be in place in the previous the exchange is dedicated? If through 'chop up' you advise replication, how lots device source is had to propagate the exchange? If through chop up you advise segmenting, how small are you making plans to make segments?


This content was originally posted on Y! Answers, a Q&A website that shut down in 2021.
Loading...