f



OT: How Can I Copy Database Content to Another SQL Database?

Hey guys,

I am looking for a simple method that would allow me to complete the
complete contents of one SQL database into another SQL database.  I
have a test data with the exact same table structure as my Live
database.  I want the test to be a mirror copy of the Live.

I thought I might just delete my test database, and then restore the
Live backup as my test database.  Unfortunately I hit a briak wall
because it tells me that users are logged into the test database.  How
can I find out who is logged into the database?


Would it be easier to write a SQL script?  Thanks!




































0
3/3/2008 6:30:47 PM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

1 Replies
658 Views

Similar Articles

[PageSpeed] 53

alvinstraight38@hotmail.com (alvinstraight38@hotmail.com) writes:
> I am looking for a simple method that would allow me to complete the
> complete contents of one SQL database into another SQL database.  I
> have a test data with the exact same table structure as my Live
> database.  I want the test to be a mirror copy of the Live.
> 
> I thought I might just delete my test database, and then restore the
> Live backup as my test database.  Unfortunately I hit a briak wall
> because it tells me that users are logged into the test database.  How
> can I find out who is logged into the database?
 
You can run sp_who or sp_who2 and look in the database column. Mostly,
you are the one yourself who is active in the database. You cannot
run restore command from a query window which has that database as the
current database.

If you don't care what happens to the user who are logged in, you can
issue this command:

   ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE

this will kick out all users.

Using BACKUP/RESTORE is by far the easiest and safest method to copy a 
database.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
esquel (7068)
3/3/2008 10:45:53 PM
Reply:

Similar Artilces:

Database Database Database Database Software Cheap
Database Database Database Database Great Datase Software See Website Below. Ultra Easy to Learn (Typically 30 Seconds) Professional Programmable Database Ver. 2.3 2.1 Million Record Capacity, (New cond). Search Rate: 2000 / Records / Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Has Six Seperate Field Sets All Programmable. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Email). http://www.vehiclerepair.org/dbPro/dbpro.html ...

Database Database Database Database Software Cheap
Database Database Database Database Software Cheap Great Datase Software See Website Below. Ultra Easy to Learn (Typically 30 Seconds) Professional Programmable Database Ver. 2.3 2.1 Million Record Capacity, (New cond). Search Rate: 2000 / Records / Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Has Six Seperate Field Sets All Programmable. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Email). http://www.vehiclerepair.org/dbPro/dbpro.html ...

How to copy a sun-database in one informix database to another informix database?
Hi, Sorry I am not an informix dba, and I was faced to copy a sub-database(sorry I don't know the clear name of such concept, as informix is like sql-server, which used multiple database) from one informix database system to another informix database. I know in oracle, we can use transportable tablespace, copy the datafile and transport the metadata, and do something else. How can I do it in informix then? Is there document which talk about the detail step? Thanks It would really help if you knew the versions of the Informix databases. Assuming Informix IDS 7.x or IDS 9.x If the two systems are binary compatible the fastest method is to use onunload/onload. If the systems are not binary compatible the easiest way is to use dbexport/dbimport. Everything is well documented in the manual "IBM Informix Migration Guide". This manual covers all Informix databases from Standard Engine and Online to IDS and XPS. See http://www.ibm.com/informix/pubs zhu chao wrote: > Hi, > Sorry I am not an informix dba, and I was faced to copy a > sub-database(sorry I don't know the clear name of such concept, as > informix is like sql-server, which used multiple database) from one > informix database system to another informix database. > I know in oracle, we can use transportable tablespace, copy the > datafile and transport the metadata, and do something else. How can I > do it in informix then? Is there...

a database, is a database, is a err database
How many times can we see the same request from someone who wants to access data from a 'pick' database through what has come to be 'standard' practices (odbc, oledb) and still get the same old sloppy ' buy this proprietary utility (and above all, my services)' answer. I think most of these pick flavors should have some sort of layer (by now!) to handle this; If someone needs to do this, the service is really 'education' i.e to show them how. Lets cut the shit now and stop with this tired and silly BS and sad marketing schlock. Regards, -Jim Jim wrote: > How many times can we see the same request from someone who wants to > access data from a 'pick' database through what has come to be > 'standard' practices (odbc, oledb) > and still get the same old sloppy ' buy this proprietary utility (and > above all, my services)' answer. I think most of these pick flavors > should have some sort of layer (by now!) to handle this; If someone > needs to do this, the service is really 'education' i.e to show them > how. Lets cut the shit now and stop with this tired and silly BS and > sad marketing schlock. > > Regards, > > -Jim Jim who? I wonder? What is this? An attack on capitalism? Providing services for those who perhaps lack the time, skill, or knowledge to perform such tasks is hardly a crime. Maybe "standard" odbc and...

Database Database Database Software Cheap
Database Database Database Database Software Cheap Great Datase Software See Website Below. Ultra Easy to Learn (Typically 30 Seconds) Professional Programmable Database Ver. 2.3 2.1 Million Record Capacity, (New cond). Search Rate: 2000 / Records / Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Has Six Seperate Field Sets All Programmable. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Email). http://www.vehiclerepair.org/dbPro/dbpro.html ...

How to copy table from oracle database to sqlserver database ?
Hello, I need to copy a table from an 8i oracle database to a sqlserver 2000 database. Is it possible to use the command "COPY FROM ... TO ..." ? So, what is the correct syntax ? Thanks for your help Cyril On 10 Aug 2004, jewelk@free.fr wrote: > Hello, > > I need to copy a table from an 8i oracle database to a > sqlserver 2000 database. A few options exist. If this is one-off, just use sqlldr to drop the data to a file and then bcp to get it into SQLServer. > Is it possible to use the command "COPY FROM ... TO ..." ? > So, what is the correct syntax ? Well, I'm sure SQLServer has connectivity to Oracle? If you want to go this route, use that and do this from SQLServer. -- Galen Boyer On 10 Aug 2004 07:15:16 -0700, jewelk@free.fr (Cyril) wrote: >Hello, > >I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > >Is it possible to use the command "COPY FROM ... TO ..." ? >So, what is the correct syntax ? > > >Thanks for your help > >Cyril Read up on using the Heterogeneous Gateway to Sqlserver. -- Sybrand Bakker, Senior Oracle DBA "Cyril" <jewelk@free.fr> wrote in message news:cd38c3d6.0408100615.6371b40e@posting.google.com... > Hello, > > I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > > Is it possible to use the command "...

How to copy table from oracle database to sqlserver database ?
Hello, I need to copy a table from an 8i oracle database to a sqlserver 2000 database. Is it possible to use the command "COPY FROM ... TO ..." ? So, what is the correct syntax ? Thanks for your help Cyril "Cyril" <jewelk@free.fr> wrote in message news:cd38c3d6.0408100617.6f7b9f3e@posting.google.com... > Hello, > > I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > > Is it possible to use the command "COPY FROM ... TO ..." ? > So, what is the correct syntax ? > > > Thanks for your help > &...

copying data from one database to another database
I want to copy data from our production database to the development database. I want to move only the data. I do not need the table structure, constrains or anything else. I think I can use the db2move command to export out all the data like the following: db2move dbname export -tc schema1 When I load the data into our development database, will it recreate the tables? My problem is that I do not want to recreate the structure. I want to make sure that I am copying over only the data. Is db2move the best utility to move the data between two databases without making any stru...

Can I use an O8 database as a transit database between a O9 and an O7 database?
Hi all, We will gradually migrate our application data from a O7 database to a new O9 database. The application data is also written to an O7 datawarehouse via a database link. In the intermediate period data has to be written to the O7 datawarehouse db from the old O7 application db and also from the new O9 database. I did a test to see if could do an insert from the O9 database over a database link into the O7 datawarehouse database, but this is not supported. Then I did the following: - create a dblink in the O9 database to a O8 database - create a dblink in the O8 database to the O7 database - create a synonym in the O8 database to the desired table in the O7 database - create a synonym in the O9 database to the synonym in the O8 database Now in the O9 database I inserted a record into the synonym, which indeed resulted in a record in the O7-table. Would it be a sensible solution to use an O8 database as a transit database in this way? Jaap. yes "Jaap W. van Dijk" <j.w.vandijk.removethis@hetnet.nl> wrote in message news:7fm4b05a4mbbb4jub1urin1j163jjsc7vg@4ax.com... > Hi all, > > We will gradually migrate our application data from a O7 database to a > new O9 database. The application data is also written to an O7 > datawarehouse via a database link. In the intermediate period data has > to be written to the O7 datawarehouse db from the old O7 application > db and also from the new O9 database. I did a test to see if could do >...

open database from another database
I'm struggling with this, and I'm sure there's a way to do it. I would like to automate the back-up of an Access application to a writeable CD drive. I've got the process of creating a folder on the CD with the back-up date done, and I can copy the database objects (there are four MDB files in total) to the CD. All of that works. Here's my problem: because Access databases cannot be copied when they are open, I need to have my CD writing code in a new MDB, and will need to launch the new CD writing MDB from within one of the existing databases. I've done this kind of thing before, but I don't have the slightest recollection on how I did it (used to be you had to do that kind of thing in order to compact/repair through automation). So, I give up. How do you launch Access from within Access??? Well this will do it: Dim i As Variant i = Shell("msaccess d:\persprop\CSCPP.mdb") Just put in your own db path. But this does come up with the warning that requires you to click to get into to the db (in 2003). If you know how to get by that I'd like to know. You could put the function in a macro and then close the db stat started the new one. Good luck Ira Solomon On Tue, 25 Apr 2006 20:17:10 -0500, Mike Easter <username@domain.com> wrote: >I'm struggling with this, and I'm sure there's a way to do it. I would like >to automate the back-up of an Access application to a wri...

How can I backup database from database itself
Is there a way to backup a database by issuing an SQL statement from the database? I would like user to be able to create a backup from database itself, and not by telnetting to server and typing 'pg_dump' or 'pg_dumpall'. Tnx in advance Dragan ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org On Thu, Jun 24, 2004 at 13:56:58 +0200, Dragan Matic <mlists@panforma.co.yu> wrote: > Is there a way to backup a database by issuing an SQL statement from the > database...

How can I backup database from database itself
Is there a way to backup a database by issuing an SQL statement from the database? I would like user to be able to create a backup from database itself, and not by telnetting to server and typing 'pg_dump' or 'pg_dumpall'. Tnx in advance Dragan ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org On Thu, Jun 24, 2004 at 13:56:58 +0200, Dragan Matic <mlists@panforma.co.yu> wrote: > Is there a way to backup a database by issuing an SQL statement from the > database...

How do I reference a table in one database from a SQL script running in another database?
I am construvcting a number of databases, some of which contain sensitive data and most of which do not. I am attempting to handle the security issues involved in protecting sensitive data in part by putting it in its own database. If the sensitive data is in a database called d_SensitiveData, and in that database there is a table called 't_A' (I know, not very informative, but this is only a trivially simple example :-), and I have a script running in a database 'NotSensitiveData' (i.e. there is a statement at the beginning of the script "USE NotSensitiveData"), ho...

Open a database from a database
I want to be able to open and run an Access database from within an already running one. The OpenDatabase command simply opens it but does not run the startup routine. Interestingly opening from Windows Explorer has a different meaning in that it opens and runs! What command enables me to open another database and get it to run its AutoExec macro, from code? Jim I don't know the answer, but I do know that when you use OpenDatabase the code in a startup form of that database does work. Martin "Jim Devenish" schreef in bericht news:0b4510ac-d415-4c2c-b200-7546aae178a4@gu8g2000vbb.googlegroups.com... I want to be able to open and run an Access database from within an already running one. The OpenDatabase command simply opens it but does not run the startup routine. Interestingly opening from Windows Explorer has a different meaning in that it opens and runs! What command enables me to open another database and get it to run its AutoExec macro, from code? Jim On Tue, 12 Apr 2011 13:37:41 -0700 (PDT), Jim Devenish <internet.shopping@foobox.com> wrote: Have you tried the Shell command? If you weren't using the AutoExec macro but one with another name, you could use the /X command line switch for msaccess.exe -Tom. Microsoft Access MVP >I want to be able to open and run an Access database from within an >already running one. The OpenDatabase command simply opens it but >does not run the startup routine. Interestingly opening ...

Opening Another Database from WITHIN a Database
I have a database (2000.mdb) which links to my data tables (Data2000.mdb). The database has a Main Menu screen (not a switchboard - a custom one). I need to import records from 5 text files into the 5 different Tables in Data2000.mdb. Here was my plan: Create various Import specs in Data2000.mdb Create a Macro (in Data2000.mdb) which calls each of the specs sequentially. Place a button on the Main Menu (2000.mdb), which closes the current database (optional), opens Data2000.mdb, runs the Macro (Import Reg), then returns the user to the original Menu in the original database (2000.mdb) I can't seem to get past running the Macro. See my code below: Private Sub cmdOpenDataTables_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single) Dim wrkJet as Workspace Dim dbsAnother as Database Set wrkJet = CreateWorkspace("DataWorkspace", "admin", _ "", dbUseJet) Workspaces.Append wrkJet Set dbsAnother = wrkJet.OpenDatabase("Data2000.mdb") With dbsAnother DoCmd.RunMacro "Import Reg" End With End Sub I keep getting a "Can't find Macro Import Reg" error. Any suggestions? Is there an easier way to automate this? Is part of the problem is having 2000.mdb open (and linked) to Data2000.mdb when I try to execute this? Thanks in advance for any assistance. -- Andi Plotsky IRIS, LLC 2859 Galahad Drive Atlanta, GA 30345 404-321-9459 (office) 404-636...

Database copy -- recover database question
I am coping SAP/Oracle database R3Q to Q02, For R3Q: SQL> connect / as sysdba Connected. SQL> alter system switch logfile; (4 times, we have 2 groups , 2 memebers per group) System altered. SQL> alter database backup controlfile to trace; SQL> shutdown Database closed. Database dismounted. ORACLE instance shut down. For Q02: Copy all database & online redo logs to correcponding destination directory. CREATE CONTROLFILE SET DATABASE "Q02" RESETLOGS ARCHIVELOG -- SET STANDBY TO MAXIMIZE PERFORMANCE MAXLOGFILES 255 MAXLOGMEMBERS 3 MAXDATAFILES 254 MAXINSTANCES 50 MAXLOGHISTORY 3176 LOGFILE GROUP 1 ( '/oracle/Q02/origlogA/log_g11m1.dbf', '/oracle/Q02/mirrlogA/log_g11m2.dbf' ) SIZE 50M, GROUP 2 ( '/oracle/Q02/origlogB/log_g12m1.dbf', '/oracle/Q02/mirrlogB/log_g12m2.dbf' ) SIZE 50M, GROUP 3 ( '/oracle/Q02/origlogA/log_g13m1.dbf', '/oracle/Q02/mirrlogA/log_g13m2.dbf' ) SIZE 50M, GROUP 4 ( '/oracle/Q02/origlogB/log_g14m1.dbf', '/oracle/Q02/mirrlogB/log_g14m2.dbf' ) SIZE 50M -- STANDBY LOGFILE DATAFILE '/oracle/Q02/sapdata1/system_1/system.data1', '/oracle/Q02/sapdata3/r3q_1/r3q.data1', '/oracle/Q02/sapdata3/r3q_2/r3q.data2', '/oracle/Q02/sapdata3/r3q_3/r3q.data3', '/oracle/Q02/sapdata3/r3q_4/r3q.data4', '/oracle/Q02/sapdata4/r3q_5/r3q.data5', '/oracle/Q02/...

Check if a database is available from another database
Hello, i'm new, so i want to ask you if you know how can i check if a database is available before i connect to it. I am using Oracle 7.3.4 on a UNIX machine, and i am using Oracle Forms 4.5. The situation goes as follows: I'm using Oracle Forms and i want at some point to chande my database connection to do some job. What i want is to check if the database is available before i connect to it. If it is available i continue, else a message will tell me that the database is not available. Can this be done using PL/SQL? If you have anything please tell me. Thanks. Marios Varna...

How can i find words from database?if the database is so big?it can reduce the speed?
Please say me if the size of database become big what happen itcan reduce the speed - Compact the Database - Create proper Indexes - Make sure the database is Normalized Again, You need to post what Database you are using... -- Chris Hanscom MVP (Visual Basic) http://www.veign.com -- "mit" <mbzala@hotmail.com> wrote in message news:4b7fd448.0406132308.681b6861@posting.google.com... > Please say me if the size of database become big what happen itcan reduce the speed ...

Appending data to a master database from another database
How can I, without using Replication technology, append a table in my master database from data in another database? I have several field offices with sales data that I want, on demand, to append up to my home office Master database... any ideas? Thanks!!!! Dav On 17 Nov 2006 08:02:28 -0800, "Parasyke" <kress1963nov22@yahoo.com> wrote: Attach tables from the Child database to the Master, and run Append queries. Or forego the table attachments and use the IN clause to specify the Child database. Off the top of my head: insert into MyTable select * from MyTable IN 'c:\test\child.mdb' -Tom. >How can I, without using Replication technology, append a table in my >master database from data in another database? I have several field >offices with sales data that I want, on demand, to append up to my home >office Master database... any ideas? Thanks!!!! Dav ...

Database Toolkit: Reading data from SQL database
Hello, &nbsp; I have been working on a automated test system storing data into a SQL server database.&nbsp; Once you get your head wrapped around the toolkit and walk through some of the examples it is not too bad.&nbsp; Writing to the database was very straight forward.&nbsp; I simply put all of my data including the primary keys into a cluster and inserted the cluster into the specified table.&nbsp; As I learned the hard way,&nbsp; it is very important for all of your types to match up.&nbsp; I saved double type&nbsp;from labview as decimal type&nbsp;in th...

Close another database from within a separate database.
Have to call up a 2nd database from within the first. Can open the 2nd database no problem, but when the user is done with the 2nd database and closes the form, or exits the 2nd database, how can I move the focus back to the 1st database, close the 2nd database, and have the form in the 1st database restored to it's original position? Take a look at the TSOON utility (Trigeminal Shut One, Open Next) at: http://www.trigeminal.com/lang/1033/utility.asp?ItemID=8#8 -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps.org. "J. Shrimp, Jr." <jumbo_shrimps@hotmail.com> wrote in message news:10tmerd5ikbqa01@corp.supernews.com... > Have to call up a 2nd database from within the first. > Can open the 2nd database no problem, but when > the user is done with the 2nd database and closes the form, > or exits the 2nd database, > how can I move the focus back to the 1st database, close > the 2nd database, and have the form in the 1st > database restored to it's original position? ...

Using Objects from one database in another database
I've looked around here to see if this can be done, but haven't found the answer. Here is what I want to do... I have a two databases -- One for Sales and one for Operations. These are used by two groups of people with some obvious overlap (like the boss, for example). Some of the data is the same, so I'm using only one backend database file to hold the data. However, I want to have one piece (projects) from the Operations database to also be used in the sales database. The projects piece of it consists of several forms, subforms, reports, queries, code, etc. To cut down on maintenance (so without actually importing) I want to be able to use those forms, subforms, etc. in the Sales database, exactly as they are in the Operations database. Is there a way to do this so that any changes made in the Operations database are also reflected in the Sales database? I've never created Add-ins before, but before I go trying to make this happen, is this the way I need to go? Would this even work? I guess what I'm saying is I want to link code, forms, reports, etc. just like I can link tables. Any ideas? ...

OT: Copying a SQL Server database to MSDE
A potential client has asked us to look at modifying his current system that uses SQL Server for its database. Ideally I'd like to take a copy of his production database and load it into MSDE on one of our machines so we can have a look at it. I have downloaded a copy of Geoff SQLMaster that looks as though it will do everything and more that I need with MSDE. Unfortunately I am forbidden to load it onto the production server at this stage. What do I need to do with a live database to take a copy that can be loaded into MSDE? Sorry to be so clueless but there is a first ti...

how can I copy a huge table to another database?
suppose I have 100M records and we don't like INSERT. one option is to export to external file and bulk load the file. is there any better idea? linked server configured here. Thanks. Hardy wrote: > suppose I have 100M records and we don't like INSERT. > > one option is to export to external file and bulk load the file. > > is there any better idea? linked server configured here. > > Thanks. What version of sql server? There's always bcp, dts, ssis. Depending on the version, a dts or ssis package can be generated using the Import/Export wizard. On Jul 27, 11:57=A0am, Hardy <wyh...@gmail.com> wrote: > suppose I have 100M records and we don't like INSERT. > > one option is to export to external file and bulk load the file. > > is there any better idea? linked server configured here. > > Thanks. We have a weekly transfer of about 5M fairly wide records (about 4GB total with indexes) . We just use an SSIS transfer accepting whatever the undelying method is. We found that a big saving in dropping indexes in the target table and rebuilding them aftert the transfer. In our system this seems to take around 11 minutes over a 100MHZ network including the index rebuild. Although the only transfer on your scale I've done was multiple complete databases via backup, file copy and restore, I found that setting up a dedicated GHZ link between the servers was well worth the set-up time. I was able to on the order...

Web resources about - OT: How Can I Copy Database Content to Another SQL Database? - comp.databases.ms-sqlserver

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Fearing no punishment, Denver cops abuse crime databases for personal gain
(credit: Noel Hidalgo ) Denver police officers performed searches on state and federal criminal justice databases that were not work-related ...

Open source database improves protection and performance
Most enterprises rely on databases in some form or another, but they can be vulnerable to attack from people looking to steal information. They ...

Larry Ellison shrugs off Amazon, Microsoft play for Oracle database business
... companies – Amazon (Nasdaq: AMZN) and Microsoft (Nasdaq: MSFT) – both made it easier for Oracle's (NYSE: ORCL) customers to move their databases ...

NTTC's Liquid Products Database making steady progress
NTTC's Liquid Products Database making steady progress Modern Bulk Transporter NTTC is asking carriers to suggest “proprietary blends” for ...

Amazon Web Services Announces that over 1,000 Databases Have Migrated to AWS since January 1, 2016
Amazon Web Services, Inc. (AWS), today announced that the AWS Database Migration Service is now generally available.

Taiwan launches database on areas vulnerable to quake damage
BEIJING (AP) — Earthquake-prone Taiwan is launching a database to inform residents which areas might be susceptible to creating potentially catastrophic ...

Interior Dept Spent $15 Million On A Crime Database That Doesn’t Work
Interior Dept Spent $15 Million On A Crime Database That Doesn’t Work

Microsoft's SQL database software now runs on Linux
Remember when Steve Ballmer likened Linux to cancer, and the notion of Microsoft courting the open source crowd was virtually unimaginable? The ...

Resources last updated: 3/22/2016 1:35:31 AM