database in recovery

yesterday i recieved a .mdf and .ldf file of a sql server database that we are now to host. when i attached it it says that it is in recovery mode. this is the first problem but the major problem is when i came in this morning one of the other databases is not telling me that that is in recovery as well

can anyone tell me how to get my major db out of recovery and what on earth is going on

one last thing its sql server 2005

thanks
LVL 3
andrew67Asked:
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can try this:

RESTORE DATABASE yourdb WITH RECOVERY;

and see if that helps, or if it gives any errors...
0

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
andrew67Author Commented:
got the following error message

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
0
andrew67Author Commented:
ive just looked in the activity monitor and there doesnt appear to be any connections to my db
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you must connect to "master" database to start the recovery ...
apart from that: can you check in the ERRORLOG of the instance if you see anything that could be related?
0
andrew67Author Commented:
hi thanks for your help

ive just run that query on the master db and got the same error. im looking in the error logs now and there seems to be loads of these

Message
Process 15:0:0 (0x850) Worker 0x03D820E8 appears to be non-yielding on Scheduler 1. Thread creation time: 12915054844140. Approx Thread CPU Used: kernel 0 ms, user 15 ms. Process Utilization 0%. System Idle 99%. Interval: 50895296 ms.

could this be related?
0
Chris MConsulting - Technology ServicesCommented:
Please check the error log and find out what could have caused this.
Check all entries in the error log related to the database name and restore/recovery/attachment.
0
Chris MConsulting - Technology ServicesCommented:
If you have access to the original db files, I suggest that you detach this one, move the files elsewhere and then attach a fresh copy of DB files from the old server.

It's possible that they are corrupted as you copied them on to the new server.
Check your disk from the command prompt using CHKDSK DRIVE_LETTER:


0
andrew67Author Commented:
ive just checked and there are another 3 db's that are now in recovery, luckily ive replicated them with a slightly different name from lastnights backup. ive just tried to detach one of the databases and i get the error

cannot detach the database 'dbname' because it is currently in use (microsoft sql server, error: 3703)
0
andrew67Author Commented:
just out of interest all the processes that are running are sleeping apart from one the sa which is running on the tempdb

i cant understand why this should be running on there will it be ok if i kill this and other processes then try and run that command again???

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>cannot detach the database 'dbname' because it is currently in use (microsoft sql server, error: 3703)
usually, this is just the "GUI" of the enterprise manager/sql managmenet studio that, by selecting the database in the browser, there is a connection with that database as context, which makes the database cannot be restored or detached.

0
andrew67Author Commented:
that sounds about right, how do i get around that problem
0
andrew67Author Commented:
ive just run this sql

USE Master
ALTER DATABASE TestTheNations SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE TestTheNations WITH RECOVERY

and recieved the following error

Msg 5011, Level 14, State 7, Line 2
User does not have permission to alter database 'TestTheNations' or the database does not exist.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
Msg 3101, Level 16, State 1, Line 3
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
What this tells me is that SQL Server itself is not done with the recovery process that always happens before the SQL Server allows access to any database.  So without this recovery process finishing (this happens on SQL Server startup as well as at attach time or restore time) the SQL Server will be the only one with access to it.

You can look in the SQL Server error logs and find the place where it starts recovering that database and follow the chain of events after that.
0
Chris MConsulting - Technology ServicesCommented:
In terms of permissions, I suggest that you connect to the database server either as SA or any other windows account with administrative privileges on the instance.

If you are trying to do something on the database, ensure not to have any other application (not even SSMS) connecting to the database you're trying to alter.

From SSMS, run a query to alter a database, but use master database.

Everything should be fine.
Ensure to backup your current database (if you can) before you mess it up.

On this note, I suggest that you offline the database, bring it online then immediately run the recovery process as follows:

USE Master
ALTER DATABASE TestTheNations SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE TestTheNations SET ONLINE;
RESTORE DATABASE TestTheNations WITH RECOVERY;

NB: setting it offline helps you close any unkownn connections leaving only your instance to manage it when it comes back online. This is cool!
0
andrew67Author Commented:
Hi Pastorchris

thanks for that, however im still getting pretty much the same error which seems to start with telling me that i dont have permission to alter the database or it doesnt exist.

its been sat there for two days now in recovery mode, is there no way i can just delete it, what if i restart the actual server??

i could really do to just clean this up one way or another

thanks
0
Chris MConsulting - Technology ServicesCommented:
Hi Andrew,
I suggest that you do a cleanup.
To delete a database which is bears the status "recovering", just use SSMS and delete it.
I suggest that you re-do the copying of files from the original source and then re-attach.

You could as well do a full backup of the original database and restore the database this side.

Try those two options and let me know how it goes Andrew.
0
andrew67Author Commented:
Hi chris couple of things, not to sure if they are relavent

1. tried to delete the db in SSMS and I got the same error "cannot drop database 'testthenations' because it is currently in use"

2. ive just been told that the orriginal .mdf file came from SQL Server Express Edition, they have just sent me a .bak file of the same database which i tried to restore and I got the error "the media family on device 'path to .bak file' is incorrectly formed..........

what ever was in the original .mdf file seems to still be in the .bak file
0
andrew67Author Commented:
Hi all

ive just found out that the database was created using server 2008, does anyone know if there is an issues trying to install it into server 2005?

0
andrew67Author Commented:
http://www.devx.com/dbzone/Article/40531/0/page/1

i think i have found the reason im having these problems
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
If the database originated in SQL 2008, you cannot backup from there and restore it to SQL 2005.  This is why you get the error that the backup is not in the correct format.

You can transfer objects using SSIS and get the objects to copy from the 2008 to 2005 database, but you have to have access to both systems because you will be copying from live to your 2005.  That way you can get the stuff over.  But plain old backup and restore will not work for you in this one.
0
andrew67Author Commented:
hi thanks for that, what about if I run the Generate SQL Server Scripts for 2005 when in 2008 according to the article ive mentioned above I should then be able to import it into 2005?
0
DBAduck - Ben MillerPrincipal ConsultantCommented:
well you can, but that will not get you the data. but you will get the objects.

you could then transfer the data with import/export in SQL.
0
Chris MConsulting - Technology ServicesCommented:
To get your data from an SQL 2008 database to an SQL 2005 database, you need to have two database instances to be able to do this and here's how:

From your SQL 2008 instance, you will need to export objects one by one and import them into the SQL 2005 database seated on an SQL 2005 instance.

You may use any export import tool/wizard to achieve this.

If you're doing this on an SQL 2005 instance, just create a new database on the SQL 2005 instance, (let's call it "new2K5"), then right click on "new2K5" and choose import, this will launch the SQL 2005 import/export data wizard.

Set the source instance to be the SQL 2008 instance and select all database objects you want to import, starting with tables, views, then later script out the procedures triggers, functions etc.

Then select the destination database as the "new2K5" and run the importation.
This will sort you out.

In case the SQL 2008 instance is far away and not accessible over the network, then I will ask you to ask for a full backup of the SQL 2008 database, then setup a SQl 2008 instance on a test server and restore the SQl 2008 database there.

From the test server, you can now do the export of the databaser objects into the SQL 2005 database.

All the best.
0
Chris MConsulting - Technology ServicesCommented:
Remember first to test all your scripts extracts from SQL 2008 database on the SQL 2005 instance for compatibility before you run them.
These of course include views, stored procedures, triggers and functions.

All the best.
0
andrew67Author Commented:
thanks ill let you know how i get on
0
andrew67Author Commented:
Hi quick update i followed this tutorial and it seemed to work

http://www.devx.com/dbzone/Article/40531/0/page/1

thanks

Andrew
0
andrew67Author Commented:
ok folks this is my final solution that seems to have sorted it. my problem came to a head last night when the server kept crashing and db after db started going into recovery.

my solution. i stopped the server, deleted the original 2008 db that was still in recovery and then restarted the server.

all now and im preying is working ok

word of warning ALWAYS CHECK THE VERSION OF A SUPPLIED DB AND  DO NOT EVER TRY AND PUT A 2008 DB INTO 2005
0
Chris MConsulting - Technology ServicesCommented:
@Andrew,
That makes my last 3 posts equal to the solution so please assign the points to me :-)
0
andrew67Author Commented:
ha well thats a first.

anyway as for the points, thanks for your help and your welcome to them
0
andrew67Author Commented:
most sorted out myself
0
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 2005

From novice to tech pro — start learning today.