How to Restore a Database Without FileStream Data: A Step-by-Step Guide

Are you stuck with a database backup that’s missing crucial FileStream data? Don’t panic! In this comprehensive guide, we’ll walk you through the process of restoring a database without FileStream data, ensuring your precious data is safe and sound.

Understanding FileStream Data

Before we dive into the restoration process, it’s essential to understand what FileStream data is and why it’s crucial for your database.

FileStream is a feature in SQL Server that allows you to store large binary data files, such as images, videos, and documents, outside of the database proper. This approach provides better performance and reduces database size. However, when you back up your database, the FileStream data might not be included, leading to issues during the restoration process.

Preparation is Key

Before you begin the restoration process, make sure you have the following:

• A full database backup that excludes FileStream data
• A compatible version of SQL Server
• Sufficient disk space for the restored database
• The necessary permissions and access rights

Step 1: Create a New Database

Create a new database with the same schema as the original database. This will serve as the target database for the restoration process.

CREATE DATABASE [NewDatabase]
ON
(
    NAME = N'NewDatabase_Data',
    FILENAME = N'C:\NewDatabase\NewDatabase_Data.mdf',
    SIZE = 10240KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10240KB
)
LOG ON
(
    NAME = N'NewDatabase_Log',
    FILENAME = N'C:\NewDatabase\NewDatabase_Log.ldf',
    SIZE = 10240KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10240KB
);

Step 2: Restore the Database Backup

Restore the database backup that excludes FileStream data using the following command:

RESTORE DATABASE [NewDatabase]
FROM DISK = 'C:\Backup\FullBackup.bak'
WITH 
    MOVE 'NewDatabase_Data' TO 'C:\NewDatabase\NewDatabase_Data.mdf',
    MOVE 'NewDatabase_Log' TO 'C:\NewDatabase\NewDatabase_Log.ldf',
    REPLACE;

Note: Make sure to replace the file paths and database names with your own.

Step 3: Rebuild the FileStream Filegroup

Since the FileStream data is not included in the backup, you’ll need to rebuild the FileStream filegroup. Create a new filegroup and add a file to it:

ALTER DATABASE [NewDatabase]
ADD FILEGROUP FileStreamGroup;

ALTER DATABASE [NewDatabase]
ADD FILE 
(
    NAME = N'FileStreamFile',
    FILENAME = N'C:\NewDatabase\FileStream\FileStreamFile.ndf',
    SIZE = 10240KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10240KB
)
TO FILEGROUP FileStreamGroup;

Step 4: Configure FileStream

Configure FileStream to use the new filegroup :

ALTER DATABASE [NewDatabase]
ENABLE FILESTREAM
(
    NON_TRANSACTED_ACCESS = OFF,
    DIRECTORY_NAME = N'FileStream'
);

Step 5: Restore Filesystem Permissions

Restore the necessary permissions and access rights to the FileStream directory:

Permission	Account	Description
Read	SQL Server service account	Allows the SQL Server service to read files
Write	SQL Server service account	Allows the SQL Server service to write files
List Folder Contents	SQL Server service account	Allows the SQL Server service to list files
Read	Allows users to read files

Assign the necessary permissions using the Windows Explorer or the command prompt:

icacls "C:\NewDatabase\FileStream" /grant:r "SQL Server Service Account":R /grant:r DOMAIN\Users:R

Step 6: Verify the Restoration

Verify that the database has been restored successfully and the FileStream is functional:

SELECT * FROM sys.filegroups;
SELECT * FROM sys.database_files;

Conclusion

Restoring a database without FileStream data can be a daunting task, but by following these steps, you’ll be able to successfully restore your database and rebuild the FileStream filegroup. Remember to assign the necessary permissions and access rights to ensure the FileStream functions correctly.

Best Practices

To avoid issues during the restoration process, make sure to:

1. Regularly back up your database, including FileStream data
2. Test your backups to ensure they’re complete and restorable
3. Document your database schema and configuration
4. Have a disaster recovery plan in place

By following these best practices, you’ll be better equipped to handle database restoration and ensure your data is safe and secure.

We hope this comprehensive guide has provided you with the necessary instructions and explanations to restore your database without FileStream data. If you have any further questions or concerns, please don’t hesitate to reach out.

Frequently Asked Question

When it comes to restoring a database without Filestream data, things can get a little tricky. But don’t worry, we’ve got you covered! Here are the answers to the top 5 questions on this topic.