Database just says restoring...for last 20 hours?!

Question

Database just says restoring...for last 20 hours?!

WebTechie

SSChampion

Points: 11405
More actions
January 13, 2010 at 8:00 am

#219237

Hello,
I've got a SQL Server 2005 server that I have remote access onto as well as using SSMS.
I've got a SQL Server 2008 server that I don't have remote access onto and only can use SSMS.
I need to move a database from the old 2005 box to the new 2008 box.
I did a backup to our Backup server (across network). That went OK.
Then I needed to get the backup to the new server, but I can't just remote it and copy from the backup server.
I tried a restore backup with move. That seemed to work. It said it was restoring. That was 20 hours ago. The database is a small database.
1) How do I stop it? I tried taking it offline. It gave me an error message saying that it was busy restoring the database.
2) How do you restore a database through TSQL from a network server? I thought the restore "WITH MOVE" was the right way.
Thanks!
Things will work out. Get back up, change some parameters and recode.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Move is used to move the mdf and ldf files. What was the exact command that you ran to start the restore? Did you use the NO_RECOVERY option?

Is there still a connection running the restore? (check sys.dm_exec_requests)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

WebTechie SSChampion Points: 11405 More actions · Answer 2

Here is the sql that I ran (doctored up for company privacy)

DECLARE @BakDate as datetime

SET @bak1 = '\etworkserver\backup\bak\mydata\mydata.bak'

RESTORE DATABASE [MyData]

FROM

DISK = @bak1

WITH FILE = 1,

NORECOVERY,

MOVE 'Mydata' TO

'\etworkserver\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\MyData.mdf',

MOVE 'Mydata_Log'

TO '\etworkserver\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\MyData.ldf'

I don't see a connection when querying from sys.dm_exec_requests.

Thanks.

Tony

Things will work out. Get back up, change some parameters and recode.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

WebTechie38 (1/13/2010)
RESTORE DATABASE [MyData]
...
NORECOVERY,

You asked SQL to restore the database and then leave it in the restoring state so that you can restore more backups. That's exactly what NORECOVERY means.

SQL has been sitting for 20 hours waiting for you to restore more backups (differential or log)

If you don't have any more backups (differential or log) to restore, then you can tell SQL to bring the database online with this

RESTORE DATABASE MyData WITH RECOVERY

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

WebTechie SSChampion Points: 11405 More actions · Answer 4

Hmmmmmm,

Now that is embarrassing to say the least. Oh well, at least I am still able to learn.

Thanks Gail. It worked perfectly.

Tony

(A very embarrassed Tony)

Things will work out. Get back up, change some parameters and recode.

Ken at work Hall of Fame Points: 3426 More actions · Answer 5

Ken at work

Hall of Fame

Points: 3426

May 31, 2013 at 11:48 am

#1620185

Got it....

dumb