copy files

Question

copy files

J Scott-419766

Old Hand

Points: 390
More actions
March 27, 2007 at 10:59 am

#116267

I created a new database using backup and restore from SQL 2000 to SQL 2005. It's working fine but users are stating they're missing data from yesterday. I run a script on the old database and the data is there. What is the best way to capture that days data. The data I need is in one table. I don't want to override todays data by doing another backup and restore. I'll be in the same boat if I do that and wondering how to get back today's data.
Thanks

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Carlos Pereyra Ten Centuries Points: 1069 More actions · Answer 1

I normally use either linked servers and execute a copy from the remote server to the local for any rows missing (using the primary key) or, I extract the rows from the server that has the data I want by doing a select into a table in tempdb. Then, I BCP the data out and BCP it into the server that's missing the data.

Carlos.

Regards,

Carlos

J Scott-419766 Old Hand Points: 390 More actions · Answer 2

Ok. I am not familiar with any of those features. Can you explain more? Thanks

mngong SSCertifiable Points: 6167 More actions · Answer 3

Are you talking a single table ,multiple tables or database objects ?

If the problem is tables which is most likely drop ,export import

You can drop all the tables in the new database and replace them if you have the stored procs and other objects in the new DB

Mike

Carlos Pereyra Ten Centuries Points: 1069 More actions · Answer 4

Assuming that the data is in different servers...

As I understand, you have data in one table in one database that is not present in the table that the users are now accessing. To get that data into the current table, you could create a linked server. That is accomplished by using the SQL Management Studio and creating a connection to the old server under Server Objects/Linked Servers/Providers. For example, let's say that your old server name is oldserver. Once you have created the link to oldserver, in the new server you would execute an insert statement:

insert into table select * from oldserver.databasename.dbo.table where pkid not in (select pkid from table)

Another way to do it, is to extract the missing data into a table in tempdb. For example:

Select * into tempdb.dbo.table from table where datecreated >= .....

Then, from the command line, you would execute a BCP command (the particulars of the command vary depending on the data format), something like:

bcp tempdb.dbo.table out table.txt -Uusername -Sservername -Ppassword -c -t,

This command (or something like it), will create a file called table.txt in this case containing the rows you extracted from the old table.

Then, you would execute the BCP command to insert the data into the new table. The format of the command is almost identical with the exception of using "in" instead of "out" and getting the database and table names correct.

If your data is in two databases in the same server, then it's much simpler. Let us know if that's the case.

Carlos.

Regards,

Carlos

J Scott-419766 Old Hand Points: 390 More actions · Answer 5

I'm talking about exporting out of one table on the old server and appending the data for this particular day in the table on the new server.

Carlos Pereyra Ten Centuries Points: 1069 More actions · Answer 6

Yes, either of the solutions I propose will accomplish what you want.

Carlos.

Regards,

Carlos

J Scott-419766 Old Hand Points: 390 More actions · Answer 7

You've given me some great suggestions and try to link the servers and go from there. I'll let you know how it goes. Thanks so much.

Murali-420858 Ten Centuries Points: 1076 More actions · Answer 8

J Scott,

You can also use an Export/Import for appending the data on the destination table. If not able

to do with Linked server. Linkedserver too is another way in transfering data.

Murali.a

J Scott-419766 Old Hand Points: 390 More actions · Answer 9

insert

into dbo.table_signon select * from dbo.query

I was able to get the missing data from the old server onto the new server. When I try to append the data from the query table to the signon table I'm getting this error An explicit value for the identity column in table 'dbo.table_signon' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I also tried:

SET

IDENTITY_INSERT dbo.table_signup ON

insert

into dbo.table_signup select * from dbo.query

Carlos Pereyra Ten Centuries Points: 1069 More actions · Answer 10

Try

set identity_insert <yourtable> on

insert into yourtable (columns) select columns from othertable

set identity_insert <yourtable> off

The columns have to be named.

Carlos.

Regards,

Carlos

Carlos Pereyra Ten Centuries Points: 1069 More actions · Answer 11

I just realized that my post was somewhat confusing so I'm including an example:

set identity_insert customers on

insert into customers (customerid, name, address) select customerid, name, address from customers_old

set identity_insert customers off

Regards,

Carlos

J Scott-419766 Old Hand Points: 390 More actions · Answer 12

I have 14 columns in the table. You're saying that I have to name all the column names in the query?

Thanks

Carlos Pereyra Ten Centuries Points: 1069 More actions · Answer 13

Yes, you have to name all the columns or it won't work. I usually generate the column name and the commas from querying the syscolumns table such as:

select name + ',' from syscolumns where object_name(id) = 'tablename' order by colorder

Regards,

Carlos

J Scott-419766 Old Hand Points: 390 More actions · Answer 14

You lost me but I put in all of the columns and got this error:

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near '?'.

One of my column name for some reason; I didn't design this database, ends with ? When I delete it off in the query, it can't find the field. Any ideas. I can tell i'm getting close