What are the challenges you face when working across database platforms? Take the survey
Options

Help required for RESTORE

zaza Posts: 2
edited September 30, 2008 12:03AM in SQL Backup Previous Versions
hi i am testing redgate backup

I wanted to test the files/filegroup option of backups. To do that i did the following

I added a new filegroup to a database using

USE CustomerDB_OLD;
GO
ALTER DATABASE CustomerDB_OLD
ADD FILEGROUP FG_ReadOnly
GO

Then i added a file
ALTER DATABASE CustomerDB_OLD
ADD FILE
(
NAME = FG_READONLY1,
FILENAME = 'C:\CustDB_RO.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
) TO FILEGROUP FG_READONLY;
GO


And i moved a table to the new file using the following
-- Table without a clustered index + drop index
CREATE CLUSTERED INDEX IDX_ProductID ON dbo.OrdersDetail(ProductID)
ON FG_ReadOnly
GO
DROP INDEX IDX_ProductID ON dbo.OrdersDetail(ProductID)
GO

Then i did a backup and after that performed a restore. Everything went well accept when i now try to view the contents of the table it
displays the following error message

The query processor is unable to produce a plan for the table or view 'OrdersDetail' because the table resides in a filegroup which is not online.

Then i used the following query to view the status

SELECT * FROM sys.database_files

the STATE_DESC column shows "RESTORING" for the orderdetail table. It is stuck on RESTORING for hours and not changing to online. File only contains one table with very less data.

Comments

  • Options
    peteypetey Posts: 2,358 New member
    Have you tried performing the same steps using native SQL Server backup and restore functionality, and do you end up in the same situation?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.