SQL 2005 import/export data

Have you tried DBCC?

Here's a good article on it:
http://articles.techrepublic.com.com/5100-10878_11-6142604.html

I'm thinking if it is corrupt you are not going to be able to export import.  Try DBCC to see if it can be repaired.  By all means before you try anything make a backup of the db.  There are many way to do that.
If it is a server that you can shutdown for a few minutes, just stop the SQLServer service and copy the .ldb and .mdb files for that database somewhere else.  Then restart SQLServer service.

There is also the

    bcp

command-line tool:
http://technet.microsoft.com/en-us/library/ms187042.aspx

Thanks for the quick reply.  I have tried the following.  I did a complete backup from the working server and setup another server in shop to test with.

I restrored the data and started here.

EXEC sp_resetstatus db_bad;

ALTER DATABASE db_bad SET EMERGENCY

DBCC checkdb (db_bad)

ALTER DATABASE db_bad SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (db_bad, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE db_bad SET MULTI_USER


I was hoping this would fix it but still it will not work for Archiver.  Any other suggestions.

Also in trying to import/export the data from the bad db into a good one I keep getting the following.

- Execute the transfer with the TransferProvider. (Error)
Messages
ERROR : errorCode=-1073548784 description=Executing the query "drop table [db3].[sys].[arc_add]
" failed with the following error: "Cannot drop the table 'db3.sys.arc_add', because it does not exist or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
 
ERROR : errorCode=-1073548784 description=Executing the query "CREATE TABLE [db3].[sys].[arc_add] (
[id] int NOT NULL,
[email] nvarchar(255) NOT NULL,
[type] tinyint,
[name] nvarchar(255)
)
" failed with the following error: "The specified schema name "sys" either does not exist or you do not have permission to use it.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
 
ERROR : errorCode=-1073548784 description=Executing the query "ROLLBACK TRANSACTION" failed with the following error: "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
 
ERROR : errorCode=-1073548784 description=Executing the query "drop table [db3].[sys].[arc_add]
" failed with the following error: "Cannot drop the table 'db3.sys.arc_add', because it does not exist or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
 

this may be a dumb question, but you are logged in as

 sa

right?

If so, I can't imagine why you wouldn't have permissions unless the system tables are damaged or something.  have you tried something like bcp?
It was always a pain for me to use, but I know it will do exports.

is the new table named the same as the old one/same db?
If so probably the script tries to do a DROP to drop the table before creating it and then importing.  If it is the same name, then perhaps it is trying to drop the table it is also trying to use or something--yes I am just guessing.  What it you edited the script to remove or comment out the DROP command preceding the CREATE TABLE for that table which is giving your grief?

I am way more novice than you think.  

Yes I am logging in as SA.  what is BCP?

Yes it is the same db name.  Should I start a blank database without any tables and try that?

Not sure about your last line but if you can provide some assistance maybe I can get through it.

I created a brand new db and started the import and here is what I got.

TITLE: SQL Server Import and Export Wizard
------------------------------
 
Error 0xc002f210: Drop table(s) SQL Task: Executing the query "drop table [db5].[dbo].[arc]
" failed with the following error: "Cannot drop the table 'db5.dbo.arc', because it does not exist or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 
 
------------------------------
BUTTONS:
 
OK
------------------------------

Select allOpen in new window

My current db is bad with mail archiver I was going to try to import the data from that set into a new db to see if this fixes the error.  I am going into the new db newly created and clicking import data.  From there I import from the bad mounted db.  

I have tried with both a new db with all the right tables\columns and one without and I keep getting this error.  

Error 0xc002f210: Drop table(s) SQL Task: Executing the query "drop table [db5].[dbo].[arc]
" failed with the following error: "Cannot drop the table 'db5.dbo.arc', because it does not exist or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  

I am going to try the drop right now.

This is what the code snippet looks like as I try to do table by table.  There is only about 20.

 
CREATE TABLE [db5].[dbo].[arc_add] (
[id] int NOT NULL,
[email] nvarchar(255) NOT NULL,
[type] tinyint,
[name] nvarchar(255)
)

Select allOpen in new window

I also know it is only failing on the very top or first table.

Is there a DROP command inside this script?
Is there a CREATE DATABASE command in there?

if there is a DROP command is there some like
DROP IF EXISTS([db5].[dbo].[arc]

or just
DROP [db5].[dbo].[arc]

if there is a CREATE DATABASE statement, you don't need to create the database first...if not, you do.

if there is no exist statement with the drop, then that is why the error.

BTW, this script can be run piece by piece in Query Analyzer (browser) or whatever that is called. I have been using MySQL for a while and forgotten.  You could start at the top of the script, copy, paste and run each command (top to bottom) and see where it is failing.

The db looked good all the way around.  Archiver was not having it.  I had to break it apart by months with GFI support.  Thanks,