Copying table data from a different database

I am attempting to copy table data from our live production database to our development database. To do this, I need to specify the name of the database in addition to the table name. I'm running into some problems that I'm hoping are just syntax related. Here is a portion of the code that runs fine when I don't specify a database...

>> SELECT name,description FROM cec_cecil_permissions

And here is a portion of the code I'm trying to run that results in an error...

>> SELECT name,description FROM devsql01.cec_cecil_permissions

When I run this, I get the following error...

>> Invalid object name 'devsql01.cec_cecil_permissions'.

My first thought was that I need to specify the complete path to the table within the database, so I ran the following code...

>> SELECT name,description FROM devsql01.Databases.Intranet.Tables.cec_cecil_permi ssions

This resulted in the following error...

>> The object name 'devsql01.Databases.Intranet.Tables.cec_cecil_perm issions' contains more than the maximum number of prefixes. The maximum is 3.

I can't reduce the number of prefixes because I'd be leaving out part of the path to the table. I tried changing every "." to a "\" but that resulted in a syntax error...

>> Incorrect syntax near '\'.

What do I need to do to make this work? Thanks in advance for your help!Never mind. I realized I was putting the server name in instead of the database name.

copying databases from live to backup SQL Server

Hello All,
I have two installations of SQL Server, one is the live SQL Server with
all live databases. What I want to do is create and update all databases on
this second database server which is to be used as a backup, is there a way
to copy over these 30 odd databases to this backup server, and then also set
it up to do a daily update of these databases with the live data ? or is
that to be done manually taking each database at a time. I know this can be
done automatically in SQL Server 2005, but we have got SQL Server 2000 for
now.
Imran.
Take a look at replication or log shipping
http://sqlservercode.blogspot.com/
|||thanks a lot for the response, looking into replication now.
Imran.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1135172121.968297.208350@.g44g2000cwa.googlegr oups.com...
> Take a look at replication or log shipping
>
> http://sqlservercode.blogspot.com/
>

copying databases from live to backup SQL Server

Hello All,
I have two installations of SQL Server, one is the live SQL Server with
all live databases. What I want to do is create and update all databases on
this second database server which is to be used as a backup, is there a way
to copy over these 30 odd databases to this backup server, and then also set
it up to do a daily update of these databases with the live data ? or is
that to be done manually taking each database at a time. I know this can be
done automatically in SQL Server 2005, but we have got SQL Server 2000 for
now.
Imran.Take a look at replication or log shipping
http://sqlservercode.blogspot.com/|||thanks a lot for the response, looking into replication now.
Imran.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1135172121.968297.208350@.g44g2000cwa.googlegroups.com...
> Take a look at replication or log shipping
>
> http://sqlservercode.blogspot.com/
>

copying databases from live to backup SQL Server

Hello All,
I have two installations of SQL Server, one is the live SQL Server with
all live databases. What I want to do is create and update all databases on
this second database server which is to be used as a backup, is there a way
to copy over these 30 odd databases to this backup server, and then also set
it up to do a daily update of these databases with the live data ? or is
that to be done manually taking each database at a time. I know this can be
done automatically in SQL Server 2005, but we have got SQL Server 2000 for
now.
Imran.Take a look at replication or log shipping
http://sqlservercode.blogspot.com/|||thanks a lot for the response, looking into replication now.
Imran.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1135172121.968297.208350@.g44g2000cwa.googlegroups.com...
> Take a look at replication or log shipping
>
> http://sqlservercode.blogspot.com/
>

Copying a SQL Server database

Hi

I am new to SQL Server. I am required to make a copy of a live database, or bring the database down and then make a copy of it.

I need to create a webpage to query the database, but another scheduling program also connects to the database, so that shceduling can be performed

Any helpBy all means refer to this http://vyaskn.tripod.com/moving_sql_server.htm link about copying to another server though it relates to moving to another server.|||Originally posted by Satya
By all means refer to this http://vyaskn.tripod.com/moving_sql_server.htm link about copying to another server though it relates to moving to another server.

Thank you so much fro the quick reply, I will go and have a look at the site right now...thanks

Copy tables with autoincrement values

I'm trying to establish the quickest and easiest solution to a problem
involving copying data from our live server to our test one.
The database contains approximately 30 tables with about 500mb of data
expanding at about 10% a month. The issue is that in order to refresh the
database on our test systems using the live data, the test database needs to
be completely deleted and then created again using the live db. However each
time this is done, the users on the database need to be re-added and
permissions assigned. Also any changes done to the table schema, SP's etc is
lost in the process.
The reason the live data cannot be simply cannot be copied from one database
to the other is that as test data is added to the test system, the keys with
the data on the live system conflict and thus copying cannot continue. Also
if the tables are attempted to be truncated before data copying - foreign
keys stop certain ones from completing.
The databases are both running Server 2000 std edition on Windows 2003 std.
Any help or guidance would be much appreciated.
TIA,
Matt Brooke
=============
VB .NET Developer
http://www.rocketscience.uk.com
Matt,
This might be helpful to
you:http://www.simple-talk.com/2005/07/05/replication/
|||Matt,
This might be helpful to
you:http://www.simple-talk.com/2005/07/05/replication/
~Rohit

Copy tables with autoincrement values

I'm trying to establish the quickest and easiest solution to a problem
involving copying data from our live server to our test one.
The database contains approximately 30 tables with about 500mb of data
expanding at about 10% a month. The issue is that in order to refresh the
database on our test systems using the live data, the test database needs to
be completely deleted and then created again using the live db. However each
time this is done, the users on the database need to be re-added and
permissions assigned. Also any changes done to the table schema, SP's etc is
lost in the process.
The reason the live data cannot be simply cannot be copied from one database
to the other is that as test data is added to the test system, the keys with
the data on the live system conflict and thus copying cannot continue. Also
if the tables are attempted to be truncated before data copying - foreign
keys stop certain ones from completing.
The databases are both running Server 2000 std edition on Windows 2003 std.
Any help or guidance would be much appreciated.
TIA,
Matt Brooke
--
=============
VB .NET Developer
http://www.rocketscience.uk.comMatt,
This might be helpful to
you:http://www.simple-talk.com/2005/07/05/replication/|||Matt,
This might be helpful to
you:http://www.simple-talk.com/2005/07/05/replication/
~Rohit

Copy sql server 2005 database on the same machine

How can I create a copy of an existing sql server 2005 (live or offline) database and put it on the same server in a test database ?

First, make a Backup.

Then, look in Books Online about using RESTORE ... WITH MOVE.

Example:

Code Snippet

BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'

RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'

|||Thanks for the reply. Your response was very helpful.