Help required for RESTORE
za
Posts: 2
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.
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
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8