SQL Server 2008 Export data problem

20 pts.
Tags:
SQL Server 2008
SQL Server backup
SQL Server Data Storage
SQL Server Export
Hi I'm trying to get used to sql server 2008 having been forced to upgrade by my isp. I need to back up my key db on a weekly remote schedule, so have been trying to use ssms to export data from my remote db to a local copy. Firstly I created a new empty db on the local machine. Then I scripted the structure of the db on the remote machine and ran the resulting query on the local machine to copy the table structure into the new db. This gave me empty target tables to map to (see below). I used the export data wizard and selected my remote tables, then highlighted the tables to be copied and used the Edit Mappings option. Here I need to do two things, firstly checking 'Enable Identity Insert' (because I have a unique id in most tables which is identity seeded, and I've read that if this is not checked sql server will helpfully renumber your entire table, rendering all your foreign key references useless). Secondly I need to decide what to do with the data already in the destination table. SQL 2k DTS simply dropped the destination table and recreated it. SQL 2008 will either keep the structure and delete the rows therein, or drop the table and recreate it. Because I intend to save this package and call it on a schedule in the future, I need to choose one of these options, because next time the target db won't be empty. If I choose the delete rows option, when the package runs I get the following error when the process gets ot the first table with an identity ID which is used elsewhere in the db as a foreign key. Error 0xc002f210: Preparation SQL Task 4: Executing the query "Truncate TABLE [dbo][tblCompany] failed with the following error 'Cannot truncate table tblCompany because it is being referenced by a foreign key constraint. Possible failure reasons: Problems with the query, "Result Set" property not set correctly, parameters not set correctly or connection not established correctly' Half a dozen preceding tables did seem to transfer fine, but the process falls down on this one table. What I want is for the process to ignore constraints whilst it does the backup. Undeterred I tried the other option, dropping each table and creating a new version. This time I get the error: Error 0cx0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code 0x80004005. An OLE DB record is available. Source MS SQL Server Native Client 10.0. Hresult 0x80004005 Description "The statement has been terminated". An OLE DB record is available. Source MS SQL Server Native Client 10.0. Hresult 0x80004005 Description "Violation of Primary Key constraint 'PK_LoginClasses'. Cannot insert duplicate key in object 'dbo.LoginClasses'. Then I try unchecking the Enable Identity Insert option to see what happens. This happens: - Validating (Error)Messages Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "UserID". (SQL Server Import and Export Wizard) Error 0xc0202045: Data Flow Task 1: Column metadata validation failed. (SQL Server Import and Export Wizard) Error 0xc004706b: Data Flow Task 1: "component "Destination - Login" (40)" failed validation and returned validation status "VS_ISBROKEN". (SQL Server Import and Export Wizard)Error 0xc004700c: Data Flow Task 1: One or more component failed validation. (SQL Server Import and Export Wizard) Error 0xc0024107: Data Flow Task 1: There were errors during task validation. (SQL Server Import and Export Wizard) I can't see any more options to try. So how do I get SSMS 2008 to do what Enterprise Manager 2k did with no drama? Any help gratefully received. Thanks in advance
ASKED: February 19, 2009  1:43 PM
UPDATED: February 19, 2009  10:28 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Have you asked your ISP if they can just put a database backup on your web server that you can download once a week?

Try looking at the Object Transfer component within SSIS. It should be able to drop the objects and recreate them in the correct order.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following