大网数据广告活动
数据库与应用安全

Error 3156 when you restore a database

· 52CE 我爱测

This article provides workarounds for the 3156 error that occurs when you restore a database with a memory-optimized filegroup in SQL Server.

This article provides a workaround for the 3156 error, which occurs when you try to restore a database that has a memory-optimized filegroup in SQL Server.

Applies to:   SQL Server

Symptoms

When trying to restore a database that has a memory-optimized filegroup in SQL Server, you receive the following error message:

Msg 3156, Level 16, State 6, Line 1
File '<Database File>' cannot be restored to '<Driver>:\<Folder Path>\<Database Folder>'. Use WITH MOVE to identify a valid location for the file.

Cause

During the process of a database restoration, SQL Server Database Engine will create a folder for a memory-optimized filegroup. This issue occurs if there's already a folder with the same name in the same folder path, and the folder is used by SQL Server or other processes.

Workaround

Use a different folder name or a different folder path when restoring a database.

Script example

  • Script example of creating a database with a filegroup

    USE [master];
    GO
    CREATE DATABASE Contoso
    ON PRIMARY
      ( NAME = 'Contoso_Primary',
        FILENAME=
           'C:\SQLserver\Contoso\Contoso_data.mdf',
        SIZE=4MB,
        MAXSIZE=10MB,
        FILEGROWTH=1MB),
    FILEGROUP Contoso_FG1
      ( NAME = 'Contoso_FG1',
        FILENAME =
           'C:\SQLserver\Contoso\Contoso_FG1.ndf',
        SIZE = 1MB,
        MAXSIZE=10MB,
        FILEGROWTH=1MB)
    
    ALTER DATABASE Contoso 
    ADD FILEGROUP [Contoso_FG1] CONTAINS MEMORY_OPTIMIZED_DATA 
    GO
    
  • Script example of a database restoration

    USE [master]
    GO
    RESTORE DATABASE [Contoso] FROM  DISK = N'C:\backup\compress\Contoso\Contoso.bak' WITH FILE = 1,
    MOVE N'Contoso_data' TO N'C:\SQLserver\Contoso\Contoso_data.mdf',
    MOVE N'Contoso_log' TO N'C:\SQLServer\Contoso\Contoso_log.ldf',
    MOVE N'<Database File>' TO N'<Driver>:\<Folder Path>\<Database Folder>',
    Replace,
    NOUNLOAD, 
    STATS = 5
    GO
    

    [!NOTE] If the <Database Folder> is not used, the Replace keyword in the script will ensure that the process of restoration is completed without error.

See also

MSSQLSERVER_3156

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors (upstream author metadata: aartig13). Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 04/15/2021
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。