Link to home
Create AccountLog in
Avatar of LenTompkins
LenTompkinsFlag for United States of America

asked on

SQl Database in Recovery

I have SQL server installed on a PC and was running a job
 and by mistake, closed the application while the job was
running.  Now it is stuck in recovery mode.
1.  Since I have a backup of the database, can I just delete
the database and restore it from my backup?
2.  I also noticed that when I opened the SQL job I am getting
an error that it can't perform this operation while SQLServer
Agent is starting. It looks like it is started.  I gather this may be
because the database is in recovery mode.  

What is the best way to restore my database and when that
is done, will SQL Server Agent restart?  
Avatar of Nathan Riley
Nathan Riley
Flag of United States of America image

Just restore it from a query window with the below code.  After it's done restoring just right click the sql server agent and click to stop it then start it again.
RESTORE DATABASE StrategicAR FROM disk='C:\Program Files\Microsoft SQL Server\MSSQL10.CCSQL2008D\MSSQL\BACKUP\backup.bak'
WITH MOVE 'Backup_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.CCSQL2008D\MSSQL\Data\Backup.mdf',
MOVE 'Backup_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.CCSQL2008D\MSSQL\Data\backup_log.ldf',
REPLACE
go

Open in new window

Avatar of LenTompkins

ASKER

This is a simple database and gets recreated every night.  What does the second line do?
Can I just perform the first line since it is a simple database?
Yeah could just do:
RESTORE DATABASE DBName FROM disk='C:\Program Files\Microsoft SQL Server\MSSQL10.CCSQL2008D\MSSQL\BACKUP\Backup.bak'
go

Open in new window

When I opened a new window I have it pointing to the database that needs to be restored, and
I entered
RESTORE DATABASE CPR_Reporting FROM disk='D:\SQLBackups\CPR_Reporting11042010.bak'
but I received a timeout error, saying that it timed out prior to completion of the operation or the server
was not responding.  Any thoughts?
ASKER CERTIFIED SOLUTION
Avatar of Nathan Riley
Nathan Riley
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Can I just delete the database and recreate it before I restore it?
you don't have to recreate it to restore it, you could delete it then just run that restore command.
I went to the server level and tried to stop it, and I got an error that I don't have access, yet I installed
SQL server using this logon I believe.  Do I have to login as Administrator?
Great thanks.  That worked.