Transferring SQL 2005 Database

I am trying to transfer data tables from my local instance of sql server 2005 standard editionto the remote instance I have on discountasp.net.


I am using the SQL Server Import and Export Wizard. Exporting works fine with a very small table, but even with a moderate size table (7,000 records), the remote server (discountasp.net's server) is disconnecting me with the following error (I am using the default options in the wizard, and the error pops after maybe 60 seconds of processing):


- Copying to [SQL2005_150328_master].[dbo].[ABBREV] (Error)
<DIR>
<DIR>


Messages


Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host.
".
(SQL Server Import and Export Wizard)



Error 0xc0209029: Data Flow Task: The "input "Destination Input" (179)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (179)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)



Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - ABBREV" (166) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)



Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0209029.
(SQL Server Import and Export Wizard)



</DIR></DIR>


- Post-execute (Error)
<DIR>
<DIR>


Messages


Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)



Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
(SQL Server Import and Export Wizard)



Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)



Error 0xc0047018: Data Flow Task: component "Destination - ABBREV" (166) failed the post-execute phase and returned error code 0xC0202009.
(SQL Server Import and Export Wizard)
</DIR></DIR>








Help please!

 

I got the table to transfer this time by checking "Optimize for many tables" and "Run in a transaction" in the Import and Export Wizard. I had tried that before, though, and it didn't work. Is there a time-out setting that can be changed on your SQL Server ?? I will be copying big tables which may take 4-5 minutes or more to copy, so the time-out needs to be much less restrictive that it may currently be.

Thank you.

 

Hi,

Thanks for the post on moving a SQL 2005 database. I miss my SQL 2000 DTS copy all objects.


Has anyone had any luck moving a SQL database with identity columns? What settings did you find that worked?


I had done what you suggested, make a script, and them move the data. The problem I had is I couldn't figure out how to move the identity columns. It was a few weeks ago now but it seems I either got an error trying to insert into the identity column or playing with the flags on how it inserts the records I was able to add the records but it renumbered identity field.

I didn't need a lot of my history so I basically cleared the database to the minimal data and hack a few things to get the database back up.

The next thing I was going to try is to make all the tables without the identify column setting on, move the data, then run scripts to turn on the identify columns.

Kevin

 

2ktweedy

I think the attached link will help you

http://msdn2.microsoft.com/en-us/library/ms190148.aspx

Some explanation:

<DT>Optimize for many tables
<DD>


Specify whether to use a processing method that is more scalable for a large number of tables and views.When you select more than 20 tables or views, this option is automatically selected for you. When you select more than 100 tables or views, and you uncheck this option, a warning message is displayed.When this option is not selected, the import or export operation uses a single Integration Services package with a single Data Flow task that includes one parallel path, from source to destination, for each table or view to be copied. This simple implementation is intended for a relatively small number of tables.


When this option is selected, the import or export operation </DD>
<DD>


If you should unselect the option and check for each table :</DD>
<DD>


Delete rows in destination table and anable Identity insert</DD>
<DD>


</DD>

 

Here is my problem...

I have a 4.5mb data base that I only have a backup copy of. (Long story)

How do I transfer that?

I loaded up SQL Express, but it has no export functions.

The only way I can think of is to have discount.asp restore the backup (and then I will have to fix the permissions) but that seems against policy.

The interesting thing is that I cannot find the answer to how to move from SQL express to a true 2005 host environment anywhere. Lots of people asking the question on all sorts of forums but no answers.

Anybody out there that can help?

Mitch

 

I'm also having trouble moving tables with identity columns.

The Copy Database Wizard drops Identity from all Identity columns leaving them as Int.

Using the SSIS transfer object task yields the same result, a table with identity dropped.

So far we have been using Dump and Restore to copy our production data to our development environment. Our preference is to move tables and data only (not procs or views since we may have some in-work procs in dev). The SQL2000 Transfer Object Wizard worked well for this purpose. The the SSIS Transfer Object task would work if it would recognize and transfer Identity columns. I hope there is a setting somewhere I am overlooking, but fear it may be a bug....

So far, no luck searching the documentation or MS knowledge base.

-Joe

 

I have followed this step by step to deploy my Dotnetnuke database, seems like all objects and data were transfered with no error. But when I changed web.config to point to the new database, it didn't work. Gave me error message like this.

=========================================================================================================
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]
DotNetNuke.Entities.Tabs.TabInfo.get_IsAdminTab() +67
DotNetNuke.Entities.Portals.PortalSettings.GetPortalSettings(Int32 TabId, PortalAliasInfo objPortalAliasInfo) +3284
DotNetNuke.Entities.Portals.PortalSettings..ctor(Int32 tabId, PortalAliasInfo objPortalAliasInfo) +83
DotNetNuke.HttpModules.UrlRewriteModule.OnBeginRequest(Object s, EventArgs e) +3797
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
=============================================================================================

Obviously, this is something wrong with the new database.

Just curiously, does anyone successfully deployed their SQL 2005 DotNetNuke database by NOT using backup/restore ?

 

I attempted to transfer my SQL Server 2005 db using SQL Server Management Studio as described and had major problems.

Instead, I was able to transfer most of my db by using Export Data for the Tables&amp; Views and then using the Generate Scripts for the Stored Procedures.

HOWEVER, all tables lost their identity settings AND all fields lost their default values.
Also, the Views were imported as Tables.

I'm really surprised that DiscountASP doesn't have documentation for this...

Post Edited (MenuWire) : 3/6/2006 8:31:47 PM GMT

 

I too have been having problems and just tried the following....


1. Scripted my table first


2. Ran import/export checking off "Append" and "Allow Identity Insert"


THis seem to work for a single table and preserve my identity column.


Now to try with all my tables at once.....

 

I tried the same process for all tables at once and painstakingly selected "Allow identity insert" for each one individually, but it renumbered my identity columns. Must be some sort of bug.


Anyone else have similar results?

 

I am struggling with this same issue. From what I've read on the Web, if using Visual Web Developer (VWD)Express Edition with SQL Express, we would right click the database icon in the Solution Explorer window of VWD(that we want to transfer to the host) and select the DETACH menu command. After that, we should open our hosting account's Basic Options page and click on a Browse button located in the "Restore database" section near "Restore database from SQL Server 2005 mdf file" field. The file browser should appear, and we could then locate the file with the "mdf" extension that contains our project's database and then click Attach button to upload it. I've seen this diagram at another host, and was wondering if they have such a tool here that allows us to upload a detached database. We would still update the Web Config file to reflect the new connection string.


George

 

Bruce,


I will email you the URL of the site that has that description, after I look it up.


George

 

Bruce,


I have finally been able to upload my database with all indexes and ident columns. I still believe there's a problem with the import/export wizard but can't confirm it.....whenever you use "optimize for many tables", it doesn't seem to preserve the option "enable identity value insert".


Here's what I did to get my DB uploaded:


1) script the tables to a query (I turned on "script indexes" so my indexes would be created; you may need other options true)


2) run the script on the destination DB


3) open Visual Studio 2005 and create a new integration services project


4)in the solutions explorer pane, right click on the "SSIS Packages" folder, select "SSIS Import/Export Wizard"


5) go through the wizard



- set up your source and destinations


- select the tables as necessary -- do not check "optimize for many tables" (be careful here because if you use the button Select All, it turns on the optimize for many tables flag and the resulting SSIS package is difficult to edit


- in the mapping column, click edit and turn on the "enable identity insert" (or see step 7 below)</BLOCKQUOTE>
6) when the wizard completes, you should now have a new package (package1.dtsx)for copying data (note, no data gets copied yet at this stage)
7) right click on the new package, select view code

do a find and replace for the string:
name="FastLoadKeepIdentity" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the values supplied for identity columns will be copied to the destination. If false, values for identity columns will be auto-generated at the destination. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>
and change the property value "false" to "true" (there should be one for every table)
- save your package</BLOCKQUOTE>
8) run the package (F5)

Creating a SSIS project allowed me to test it locally and then for DASP, I simply changed my desitnation connection by changing the properties for that object (right click it in the Connection Managers pane and select properties). The other benefit is that I now have project I can modify myself without going through the wizard again.
Hope this helps!
David
I did have some problems with table dependancies but if youupload the tables in the right order, it worked out OK (there must be a way to temporarily disable dependancies instead of creating multiple packages?)

 

Hi Bruce,
Thanks for the script. It seems to have worked for me - but I have one problem:

In line 1, the script that your procedure generated had "Use [Person.mdf]"

I replaced"Person.mdf" withmy own database name and then the script worked.
The only problem that I see is thatthe tables and stored procedures don't have a prefix and are in the samefolder as the aspnet membership files.

EXPLANATION
All of the aspnet membership application files are prefixed with "dbo.aspnet.xxx
For example dbo.aspnet_Applications, dbo.aspenet_Membership and so on.
The stored procedures also use the dbo.aspnet.xxxprefix.

Since I'll be adding several "sets of tables" it makes sense to use a prefix naming convention.
For example: instead of just dbo.Albums and dbo.Photos I need dbo.person_Albums and dbo.person_Photos.
There is also asimilar needfor the stored procedures:
For example instead ofdbo.AddAlbum and dbo.AddPhoto (etc) I need dbo.person_AddAlbum and dbo.person_AddPhoto and so on.

Now before I spend several hours(days?) in searchingthrough the web application to identify and change all dependent objects,
I thought that you and your experts might already have a solution forthis problem.

Please let me have your recommendations

Thanks
Ken
===============================
Please will you also clarify what you mean by the following text which is fromstep 7 of your procedure:

------------
***Note If the database you are copying from had objects owned by user other dbo, you will need to create a schema with the same of the user owning the object
--------------