Notes on SQL

Background

So, you have a multi-terabyte database and a SQL Server full backup. Just imagine that you do something that wrecks the data in one table (and of course, that has never happened), the only secure copy is within a backup file that will take several hours to restore and you don’t have the time or the space for such an act.

Some backup packages allow individual objects to be restored. One example is Redgate’s SQL Backup product. However, the standard SQL backup process doesn’t cater for such a thing.
If you have your database split into filegroups, it is possible to restore specific filegroups only. So, if you know the filegroup that a specific table is using, you can limit the restore to the primary filegroup and the filegroup that you are after.

Setup

I’m going to create a test database that has three filegroups – Primary and two others.

Listing 1: Creating the test database

use master;
GO

CREATE DATABASE [TestPartial] ON  PRIMARY
( NAME = N'TestPartial_PRIMARY',
FILENAME = N'D:\SQL2017A\FGTest.mdf' ,
SIZE = 1024KB , FILEGROWTH = 1024KB ),
FILEGROUP [TestPartial_FG1] ( NAME = N'TPFG_1',
FILENAME = N'D:\SQL2017A\FGTest_1.ndf' ,
SIZE = 1024KB , FILEGROWTH = 1024KB ),
FILEGROUP [TestPartial_FG2] ( NAME = N'TPFG_2',
FILENAME = N'D:\SQL2017A\FGTest_2.ndf' ,
SIZE = 1024KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TPLog_log',
FILENAME = N'L:\SQL2017A\FGTest_log.ldf' ,
SIZE = 512KB , FILEGROWTH = 10%)
GO

For the next step, I’ll create four tables, spread across the three filegroups and load them with a small amount of data.

Listing 2: Creating the test tables and data

USE TestPartial;
GO

CREATE TABLE dbo.Region(
ID INT NOT NULL,
Area VARCHAR(20) NOT NULL,
Notes VARCHAR(50) NULL
)
ON [Primary];
GO
 
INSERT INTO dbo.Region(ID, Area, Notes)
VALUES(1, 'North', 'Northern Area'),
(2, 'South', 'Southern Area'),
(3, 'East', 'Eastern Area'),
(4, 'West', 'Western Area');
GO
 
CREATE TABLE dbo.Warehouse(
ID INT NOT NULL,
Region INT NOT NULL,
[Name] VARCHAR(50) NOT NULL
)
ON [TestPartial_FG1];
GO
 
INSERT INTO dbo.Warehouse(ID, Region, [Name])
VALUES(1, 1, 'North Warehouse 1'),
(2, 1, 'North Warehouse 2'),
(3, 2, 'South Warehouse 1'),
(4, 2, 'South Warehouse 2'),
(5, 3, 'East Warehouse 1'),
(6, 3, 'East Warehouse 2'),
(7, 4, 'West Warehouse 1'),
(8, 4, 'West Warehouse 2');
GO
 
CREATE TABLE dbo.Products(
ID INT NOT NULL,
[Name] VARCHAR(50) NOT NULL,
[Description] VARCHAR(100) NULL
)
ON [TestPartial_FG1];
GO
 
INSERT INTO dbo.Products(ID, [Name], [Description])
VALUES (1, 'Grommet 1A', 'Steel 5mm'),
(2, 'Widget 12', 'A new gadget'),
(3, 'Oddment 6', 'A thing');
GO
 
CREATE TABLE dbo.ProductWarehouse(
ID INT NOT NULL,
ProductID INT NOT NULL,
WarehouseID INT NOT NULL
)
ON [TestPartial_FG2];
GO
 
INSERT INTO dbo.ProductWarehouse(Id, ProductID, WarehouseID)
VALUES (1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 4),
(5, 2, 1),
(6, 2, 2),
(7, 3, 3),
(8, 3, 4);
GO

Test Example

Firstly, we shall make sure that we have a full backup of this database. Otherwise this will be a very short article. I’m going to verify this backup and because it is so simple, I’ll generate the code from the backup wizard for this example.

Listing 3: Backup the test database and verify the backup

BACKUP DATABASE [TestPartial] 
TO  DISK = N'D:\SQL2017A\Backups\TestPartial.bak' 
WITH NOFORMAT, 
INIT,  
NAME = N'TestPartial-Full Database Backup', 
SKIP, 
NOREWIND, 
NOUNLOAD,  
STATS = 10, 
CHECKSUM
GO

declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'TestPartial' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'TestPartial' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''TestPartial'' not found.', 16, 1) end
RESTORE VERIFYONLY 
FROM  DISK = N'D:\SQL2017A\Backups\TestPartial.bak' 
WITH  FILE = @backupSetId,  
NOUNLOAD,  
NOREWIND
GO

The following code gives a simple example of a common issue – missing the ‘WHERE’ clause from an update.

This code executes three queries.

The first shows the results of a query that displays the information derived from all of the tables.

The second query is an attempt to update the warehouse name for specific entries in the ‘Warehouse’ table. However, in this instance the ‘WHERE’ clause has been turned into a comment, so the query will now change the values of all entries.

The third query is merely the first query repeated, to show the impact that this accidental change has had.

Listing 4: Show data before and after UPDATE

USE TestPartial;
GO

SELECT Region.Area,
    Warehouse.[Name],
    Products.[Name],
    Products.[Description]
FROM dbo.Region Region
LEFT OUTER JOIN dbo.Warehouse Warehouse ON Warehouse.Region = Region.ID
LEFT OUTER JOIN dbo.ProductWarehouse pw ON pw.Warehouseid = Warehouse.ID
INNER JOIN dbo.Products Products ON Products.ID = pw.ProductID
ORDER BY Region.Area,
     Warehouse.[Name],
     Products.[Name];

UPDATE [dbo].[Warehouse]
SET Name = 'Far Away Warehouse'
--WHERE ID = 4;

SELECT Region.Area,
    Warehouse.[Name],
    Products.[Name],
    Products.[Description]
FROM dbo.Region Region
LEFT OUTER JOIN dbo.Warehouse Warehouse ON Warehouse.Region = Region.ID
LEFT OUTER JOIN dbo.ProductWarehouse pw ON pw.Warehouseid = Warehouse.ID
INNER JOIN dbo.Products Products ON Products.ID = pw.ProductID
ORDER BY Region.Area,
     Warehouse.[Name],
     Products.[Name];

Figure 1: Output from Listing 4

Recovering the Data

So, I need to restore the table ‘Warehouse’, preferably to another database, where I’ll be able to copy the data from and repair the damaged table.
Checking the properties of the table ‘Warehouse’ shows that it is in the filegroup ‘TestPartial_FG1’.

Figure 2: Details of the Warehouse table

You can also see the names of the filegroups and file names by executing the SQL Restore command FILELISTONLY.

Listing 5: Restore FILELISTONLY to see backup contents

USE [master]
RESTORE FILELISTONLY
FROM  DISK = N'D:\SQL2017A\Backups\TestPartial.bak';
GO

Figure 3: Result from Listing 5

To restore a specific filegroup from a backup, the ‘PARTIAL’ option is required.
First of all, the PRIMARY filegroup must be restored. This is unavoidable, so is another reason to keep your PRIMARY filegroup small, if at all possible.
The listing below shows the SQL code required to restore PRIMARY filegroup the backup for this database, to a database of another name. Note that the restore specifies NORECOVERY. This is because I will need to follow this restore with another, specifying the filegroup that I’m really after.

Listing 6: Restore PRIMARY filegroup

USE [master]
RESTORE DATABASE [TestPartial2] 
FILEGROUP='PRIMARY'
FROM  DISK = N'D:\SQL2017A\Backups\TestPartial.bak' WITH
MOVE N'TestPartial_PRIMARY' TO N'D:\SQL2017A\FGTest_2.mdf',  
MOVE N'TPLog_log' TO N'L:\SQL2017A\FGTest2_log.ldf',  
PARTIAL,
NORECOVERY,
STATS = 5

GO

In my testing, it would appear that you don’t require to specify the line ‘FILEGROUP =’PRIMARY’’ (or whatever your primary filegroup is named) but it makes the actions clearer to anybody else if you do. This only applies to the primary filegroup, for all other filegroups the ‘FILEGROUP=’ is mandatory.
Having executed the code in Listing 6, there is now another database named ‘TestPartial2’ in a ‘Restoring’ state.

Figure 4: New database from Listing 6

Now that I have the database partly restored, I can restore the file group that I’m actually interested in.

Listing 7: Restore the required filegroup

USE [master]
RESTORE DATABASE [TestPartial2] 
FILEGROUP='TestPartial_FG1'
FROM  DISK = N'D:\SQL2017A\Backups\TestPartial.bak'    
WITH
MOVE N'TPFG_1' TO N'D:\SQL2017A\FGTest_1_2.ndf',   
MOVE N'TPLog_log' TO N'L:\SQL2017A\FGTest2_log.ldf',  
RECOVERY,  STATS = 5

GO

Once this completes I now have (because of the RECOVERY option) an accessible database and can see the contents of the ‘Warehouse’ table, which I can now use to update the same table in the ‘TestPartial’ database.

Figure 5: The restored ‘Warehouse’ table

To show that only the selected filegroups (‘PRIMARY’ and ‘TestPartial_FG1’) have been restored, attempt to select data from the table ‘ProductWarehouse’, which is on filegroup ‘TestPartial_FG2’ – which was not restored.
You will see the message ‘The query processor is unable to produce a plan for the table or view ‘ProductWarehouse’ because the table resides in a filegroup that is not online.’.
That table has not been restored as part of this exercise.

Conclusion

If the database is designed with filegroups, it is possible to restore a part of the database in Disaster Recovery situations. Of course, the size of the required filegroup will impact on this and it may restore far more data than you require, if several large tables reside in the same filegroup. Therefore, available storage and length of time may still be a factor in this operation.

A backup strategy that is designed for object-level restores may be a better solution and tools are available for this.

References

SQL Restore Statement
Restore Files and Filegroups
Redgate SQL Backup
Redgate SQL Prompt (basic syntax checker that warns of no WHERE clause in UPDATE/DELETE)