Restore Database problem - LOADING forever

I had to restore a database late this afternoon. I have the database set to FULL recovery. Database backups are performed nightly and transaction log backups are performed every other hour. I decided to perform a point-in-time restore. When I restored this way everything seems to go ok and it finishes. Then the database is grayed and says "Loading". Although I tried 4 separate times, one time allowing over an hour, the grayed out database and "Loading" never goes away.

Freaking out I deleted the "Loading" database (didn't delete logs and backup files) and tried a manual restore from the previous night's backup file. It attached and restored properly and was ready to go in 2 minutes.

Of course I wanted to get the transaction log files restored too, since it had work from earlier today. So I tried another restore via point-in-time and got the same old messages. Currently, the database is running with the previous night's backup restored but the users aren't too keen on having to do 5 hours worth of work to catch up to the previous transaction log backup come Monday morning.

Any suggestions?

Thanks,
JBHow are you doing the restore? And why do you want to restore to a point in time?

Do it via query analyser with standby - this will enable you to look at the database between restores.

Do the full restore - look at it.

Now do the tr logs in turn - checking the database between each one.
Now you can do the last one with the point in time recovery.

If that fails then go back and redo up to the last one that restored successfully. At least you will lose at the most 2 hours.|||The poster probably did not click on Refresh menu item on Databases folder in EM.sql

Restore

Good morning everyone,
I need to restore a database on a nightly basis from a nightly backup. I
was going to use the following script but then realized that my .bak
file name is dynamic. (i.e My backup runs nightly, so the file name gets
stamped with a date, so the it is not always the same)
USE master
RESTORE DATABASE SomeDatabase
FROM DISK = 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\SomeDatabase.bak'
WITH RECOVERY,
MOVE 'SomeDatabase_Data' TO 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\SomeDatabase.mdf',
MOVE 'SomeDatabase_Log' TO 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Log\SomeDatabase.ldf'
GO
So, how do I then script this job? Please let me know. Thanks!
*** Sent via Developersdex http://www.codecomments.com ***
See backupset in Books OnLine. There is a name column in there you can use
to dynamically create the correct RESTORE statement.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
news:ecG%23rlxcIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Good morning everyone,
> I need to restore a database on a nightly basis from a nightly backup. I
> was going to use the following script but then realized that my .bak
> file name is dynamic. (i.e My backup runs nightly, so the file name gets
> stamped with a date, so the it is not always the same)
> USE master
> RESTORE DATABASE SomeDatabase
> FROM DISK = 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\SomeDatabase.bak'
> WITH RECOVERY,
> MOVE 'SomeDatabase_Data' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\SomeDatabase.mdf',
> MOVE 'SomeDatabase_Log' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Log\SomeDatabase.ldf'
> GO
> So, how do I then script this job? Please let me know. Thanks!
> *** Sent via Developersdex http://www.codecomments.com ***
|||The physical name of the backup file is stored in the backupmediafamily table
in the MSDB database. You can put the physical files name in a variable and
then dynamically build the command you want. This command will get you the
name of the last full backup for database 'SomeDatabase' If you have
transaction logs as well this must be modified.
declare @.physical_device_name nvarchar(128)
select @.physical_device_name = c.physical_device_name
from msdb..backupset a join msdb..backupmediaset b on a.media_set_id =
b.media_set_id
join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
where type='D' and backup_start_date =
(select top 1 backup_start_date from msdb..backupset
where database_name = 'SomeDatabase' and type = 'D'
order by backup_start_date desc)
select @.physical_device_name
"Audrey Ng" wrote:

> Good morning everyone,
> I need to restore a database on a nightly basis from a nightly backup. I
> was going to use the following script but then realized that my .bak
> file name is dynamic. (i.e My backup runs nightly, so the file name gets
> stamped with a date, so the it is not always the same)
> USE master
> RESTORE DATABASE SomeDatabase
> FROM DISK = 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\SomeDatabase.bak'
> WITH RECOVERY,
> MOVE 'SomeDatabase_Data' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\SomeDatabase.mdf',
> MOVE 'SomeDatabase_Log' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Log\SomeDatabase.ldf'
> GO
> So, how do I then script this job? Please let me know. Thanks!
> *** Sent via Developersdex http://www.codecomments.com ***
>

Restore

Good morning everyone,
I need to restore a database on a nightly basis from a nightly backup. I
was going to use the following script but then realized that my .bak
file name is dynamic. (i.e My backup runs nightly, so the file name gets
stamped with a date, so the it is not always the same)
USE master
RESTORE DATABASE SomeDatabase
FROM DISK = 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\SomeDatabase.bak'
WITH RECOVERY,
MOVE 'SomeDatabase_Data' TO 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\SomeDatabase.mdf',
MOVE 'SomeDatabase_Log' TO 'E:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Log\SomeDatabase.ldf'
GO
So, how do I then script this job? Please let me know. Thanks!
*** Sent via Developersdex http://www.developersdex.com ***See backupset in Books OnLine. There is a name column in there you can use
to dynamically create the correct RESTORE statement.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Audrey Ng" <odd26uk@.yahoo.co.uk> wrote in message
news:ecG%23rlxcIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Good morning everyone,
> I need to restore a database on a nightly basis from a nightly backup. I
> was going to use the following script but then realized that my .bak
> file name is dynamic. (i.e My backup runs nightly, so the file name gets
> stamped with a date, so the it is not always the same)
> USE master
> RESTORE DATABASE SomeDatabase
> FROM DISK = 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\SomeDatabase.bak'
> WITH RECOVERY,
> MOVE 'SomeDatabase_Data' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\SomeDatabase.mdf',
> MOVE 'SomeDatabase_Log' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Log\SomeDatabase.ldf'
> GO
> So, how do I then script this job? Please let me know. Thanks!
> *** Sent via Developersdex http://www.developersdex.com ***|||The physical name of the backup file is stored in the backupmediafamily table
in the MSDB database. You can put the physical files name in a variable and
then dynamically build the command you want. This command will get you the
name of the last full backup for database 'SomeDatabase' If you have
transaction logs as well this must be modified.
declare @.physical_device_name nvarchar(128)
select @.physical_device_name = c.physical_device_name
from msdb..backupset a join msdb..backupmediaset b on a.media_set_id =b.media_set_id
join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
where type='D' and backup_start_date = (select top 1 backup_start_date from msdb..backupset
where database_name = 'SomeDatabase' and type = 'D'
order by backup_start_date desc)
select @.physical_device_name
"Audrey Ng" wrote:
> Good morning everyone,
> I need to restore a database on a nightly basis from a nightly backup. I
> was going to use the following script but then realized that my .bak
> file name is dynamic. (i.e My backup runs nightly, so the file name gets
> stamped with a date, so the it is not always the same)
> USE master
> RESTORE DATABASE SomeDatabase
> FROM DISK = 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\SomeDatabase.bak'
> WITH RECOVERY,
> MOVE 'SomeDatabase_Data' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\SomeDatabase.mdf',
> MOVE 'SomeDatabase_Log' TO 'E:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Log\SomeDatabase.ldf'
> GO
> So, how do I then script this job? Please let me know. Thanks!
> *** Sent via Developersdex http://www.developersdex.com ***
>