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.