SQL DB Error

Our SQL Production pc lost a hard drive but the raid is still intact and the system is still operational.

I need to move the data to another SQL server without taking the current server offline. I cant make a backup in Management Tools, get the error below.

Tried to copy the data via the copy function in SQL Management but get a error there two. I attached the summary screen of the copy command under copy.png.

I am trying the copy command again, will paste the error info if I receive it again.
YeaktomIS/IT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YeaktomIS/IT ManagerAuthor Commented:
Copy still failing, getting the following errors on destination server

  Event Name: OnError
 Message: ERROR : errorCode=-1073548784 description=Executing the query "CREATE USER [Excel] FOR LOGIN [Excel] WITH DEFAULT..." failed with the following error: "'Excel' is not a valid login 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.

Excel is a user I created on the DBA that we use for ODBC reports.

Further errors.

  Event Name: OnProgress
 Message: Database transfer failed for 1 database(s).
 Operator: domainname\SQLAgent
 Source Name: SourceServer_DestinationServer_Transfer Objects Task
 Source ID: {8C9C9789-39F7-48B0-ADD7-543C3DB2F537}
 Execution ID: {78B06876-3544-4ECF-98E3-A53CA6912BA6}
 Start Time: 2014/01/08 09:05:30 AM
 End Time: 2014/01/08 09:05:30 AM
 Data Code: 0

 Event Name: OnWarning
 Message: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

 Operator: Domainname\SQLAgent
 Source Name: old2new
 Source ID: {194D4C19-D2F9-4F8F-B0DF-91052A161529}
 Execution ID: {78B06876-3544-4ECF-98E3-A53CA6912BA6}
 Start Time: 2014/01/08 09:05:30 AM
 End Time: 2014/01/08 09:05:30 AM

SQL Server Scheduled Job 'old2new' (0x9A2089A27EFC2D4B9EC677F33AB91EF9) - Status: Failed - Invoked on: 2014-01-08 08:50:17 - Message: The job failed.  The Job was invoked by User sa.  The last step to run was step 1 (old2new_Step).
Surranoapplication managerCommented:
Can you read the file reported as erroneous using OS level tools? (e.g. F3 in Total Commander, or copy it to a different location)
YeaktomIS/IT ManagerAuthor Commented:
I will have to stop the SQL service before I can try to copy the file.

I ran a dbcc checkdb and found the following errors.

Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (3:1269782) with latch type SH. 1(failed to retrieve text for this error. Reason: 15105) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (3:1269787) with latch type SH. 1(failed to retrieve text for this error. Reason: 15105) failed.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.

CHECKDB found 0 allocation errors and 8 consistency errors in database 'databasename'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (databasename).
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Surranoapplication managerCommented:
So it sounds like you'll either have outage because you can't access (part of) data or because you restart the server. In the latter case, though, there may be a chance to find more useful pieces of info, or even that server will realise the situation and recover by itself.

Can't you hotswap the failed drive in the RAID? What is the actual RAID configuration? (e.g. 2-disk SATA mirror, or 6-disk SCSI RAID-5, or...)
YeaktomIS/IT ManagerAuthor Commented:

Yes I can hotswop the drive, waiting for a new drive from IBM.... sadly the ETA is 7 - 10 Days!
YeaktomIS/IT ManagerAuthor Commented:
I will run the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS later tonight when production stopped.

Any other suggestions?
Surranoapplication managerCommented:
well if you can afford data loss...
YeaktomIS/IT ManagerAuthor Commented:
Should I rather try REPAIR_FAST or REPAIR_REBUILD first?
Surranoapplication managerCommented:
I'm not familiar with the repair options but "allow data loss" sounds scary.
Jim P.Commented:
First off -- you don't want to stop the SQL Services unless you have to.

The errors mean that part of that DB is corrupted. Trying to bring it back online afterward may not happen.

Do you have another undamaged drive large enough to hold all the files available? Or even  use an external USB drive for this case.

I had to do this back in this Q.

The way I would do it is use the SQL Server to script out the DB creation and structure (its in the right click menu). Then modify the drives and DB name, etc. to create an empty DB in parallel to the current DB.

Then after that I scripted the straight tables with no identity columns using this code:
		'SELECT * FROM [' + TABLE_CATALOG + '].[' + TABLE_SCHEMA + '].[' + TABLES.TABLE_NAME + '];' + CHAR(13) +
		'-------------------------------- END ' + TABLES.TABLE_NAME + ' --------------------------------'
AND		TABLES.TABLE_NAME NOT IN( 'dtproperties')

Open in new window

Then look at the other Q to get the code for handling tables with an identity column.

That will probably allow you to recover about 99% of your data. Then you can do what you want with the new DB and or do the CHECKDB with a little more comfort level.
YeaktomIS/IT ManagerAuthor Commented:
Hi Jim P

Yes I have another SQL server that can hold the data. I tried copying the data over with the copy data command.

That is exactly what I am scared of, stopping the server to copy the files out.... will I get the DB online again. I am very cautious of running the repair without a backup, but I cant run a backup with part of the DB corrupted.

Honestly I am not sure what to do next... should I take a change and stop the SQL Service to make a backup or must I bite the bullet and run the repair on the live DB without a new backup.

Could I use the import export command after I scripted the DB?
Scott PletcherSenior DBACommented:
1) Is that db's recovery model full or bulk-logged, rather than simple?

2) Is your SQL edition Enterprise Edition, or is it Standard or another version?

3) Do you have a recent backup of the database from before the drive error?
Jim P.Commented:
I'm not saying another server. I'm saying make a copy by creating the structure of the DB, blank tables, etc. Then from the running DB using a series of insert statements to fill the new DB tables from the old one.

You do that first. Then run the checks or copies. But the shutdown is the last thing to do.
YeaktomIS/IT ManagerAuthor Commented:
Hi Scott

1 Simple
2 Standard
3 Last backup was Friday
YeaktomIS/IT ManagerAuthor Commented:
HI Jim

Ok. Will try your suggestion.
Scott PletcherSenior DBACommented:
Well, #1 and #2 definitely rule out page restores.  Too bad, might have saved the situation.

Be sure to try a backup using the command rather than the GUI:

BACKUP DATABASE [<database_name>]
TO DISK = 'x:\full\path\to\backup\file\<database_name>_backup.BAK'
YeaktomIS/IT ManagerAuthor Commented:
Hi Scott

I got the following error.

Msg 3203, Level 16, State 1, Line 1
Read on "E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\dbname1.ndf" failed: 1(failed to retrieve text for this error. Reason: 15105)
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Jim P.Commented:

That is why I developed the technique that I did.

We had a customer that had a self hosted DB using our app. They called in that they had other data issues. I had one of the techs pull a backup of DB and we tried restoring it on our test server.  It was getting essentially a torn page error. The backup looked ok, but the corruption wouldn't let it restore.

I went through and recovered all but about 5K rows out of hundreds of tables and millions of rows by copying it into the empty DB. Then I did the checkdb. The DB was not recoverable, but most of the data that was lost was from static tables.

It's a manual process, but it should work.
Scott PletcherSenior DBACommented:
Sorry, in too big a hurry, I left off the key option.  It usually allows the backup to work, although of course nothing guarantees results in every specific case:

BACKUP DATABASE [<database_name>]
TO DISK = 'x:\full\path\to\backup\file\<database_name>_backup.BAK'

Naturally you want to get as full a backup as possible before you attempt any repairs/recovery.
YeaktomIS/IT ManagerAuthor Commented:
Still no luck.

Msg 3203, Level 16, State 1, Line 1
Read on "E:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\dbname1.ndf" failed: 1(failed to retrieve text for this error. Reason: 15105)
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
YeaktomIS/IT ManagerAuthor Commented:
Guessing the copy function is not working due to the corruption?

Kim P : I hear what you are saying, the only issue is the time it will take to create a import for each table, Between 500 - 600 tables.
Jim P.Commented:
In the query analyzer window set the output column width to like 3000 characters. Then set it to text output (<Ctrl>+T). Then the SELECT       'INSERT .....  ---'  FROM INFORMATION_SCHEMA.TABLES LEFT JOIN query in my post above will build all the queries you need for tables that don't have an identity column.

Then in the other Q I linked to it has a function and another query to build the rest of the insert statement.

You copy the results to another widow and then run it. There are going to be some table(s) that it will error out on. But it should cover the large majority of it.

Basically it is a couple of queries to make queries.
YeaktomIS/IT ManagerAuthor Commented:
Good Day All

Thanks for all your comments.

I used a program called J File Recovery to transfer the mdf and ndf from the production server. I then did a DBCC CHECKDB ('DBNAME', REPAIR_REBUILD) on the data, that was able to repair. At first I tried it with only repair, but the database was extremely slow... guessing some of the indexes was dropped and the rebuild fixed it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim P.Commented:
I'm glad you were able to recover it with no data loss.
YeaktomIS/IT ManagerAuthor Commented:
In my case the J File Recovery did the trick, to get the data of the production server.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.