About restoring

I′m using SQL server 2005.

I′ve tried to restore a database based on other database,
but the source database appears with "restoring...".

Is there a log for this?
How can I cancel this operation?

thanks!!!!
this operation "restoring" is running for more than 10 hours, but the database is small.|||This is probably because the database you are restoring is being used. Do not select the database from the Object Explorer when restoring either using the UI or RESTORE DATABASE command|||hi,

but is there a way to cancel?

How can users access this database?

thanks!!!!|||In the log file viewer, there is a line with the following:

The database 'dbnamedevelop' is marked RESTORING and is in a state that does not allow recovery to be run.

How can I to do to use or to enable this database?

thanks!!!! :-)|||What is the sequence of events you performed on the 'dbnamedevelop' database?|||while restoring you have to mention WITH RECOVERY for the last file which you are restoring. See the eg from BOLfor first Data file we mention as WITH NORECOVERY but for log file WITH RECOVERY RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH NORECOVERY, MOVE 'MyAdvWorks' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf', MOVE 'MyAdvWorksLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf' RESTORE LOG MyAdvWorks FROM MyAdvWorksLog1 WITH RECOVERY Madhu|||

OK. That helps to clarify the issue.

The advice to use WITH NO RECOVERY for the initial files and WITH RECOVERY only for the final file applies to backup files, not database files.

So, for example if you had a full database backup and a series of log backups, you would restore the database backup WITH NO RECOVERY and then restore each log file using RESTORE LOG WITH NO RECOVERY.

On the last log backup, you would use RESTORE LOG WITH RECOVERY.

Each of these is a separate restore statement.

The WITH [NO] RECOVERY determines the state in which the RESTORE command leaves the database:

Recovered (all non-committed transactions rolled back and the database ready for use)

or Restoring (No roll-back performed, ready to apply the next log backup)

In your example, you specify that one RESTORE command should do both, which is not possible.

|||

Madhu K Nair wrote:

while restoring you have to mention WITH RECOVERY for the last file which you are restoring. See the eg from BOLfor first Data file we mention as WITH NORECOVERY but for log file WITH RECOVERY RESTORE DATABASE MyAdvWorks FROM MyAdvWorks_1 WITH NORECOVERY, MOVE 'MyAdvWorks' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf', MOVE 'MyAdvWorksLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf' RESTORE LOG MyAdvWorks FROM MyAdvWorksLog1 WITH RECOVERY Madhu

ya... there was some confusion... ofcouse it applies to Backup files... i should have mentioed full backup instead data file... The example which i mentioned was copy pasted from BOL and there was nothing wrong in it. There were Two restore not One...

Madhu

|||

So, did the second RESTORE command succeed? If it did, the database would be online.

If it did not, what messages are in the SQL log and Event log?

|||

Can you see any activity on the files under operating system, check under the pathwhere the data files are located. I have had this problem once and due to the Anti-virus & Anti-spyware on this server (development) the whole process was hung. You can kill it but before that ensure there is no activity or other wise get another fresh backup from the source database server.

Tadeu wrote:

I′m using SQL server 2005.

I′ve tried to restore a database based on other database,
but the source database appears with "restoring...".

Is there a log for this?
How can I cancel this operation?

thanks!!!!

|||

i'm having this problem, two databases are hung in the middle of a restore.

how do i cancel this?

appreciated,

m