Forum Discussion
Attach a database containing file streams
My original database environment has a problem, can not be backed up, fortunately there are mdf, ldf and StreamFile files of the database; After I redeployed the database, I found that I could not attach the database. The error is as follows
===================================
服务器 "--------------" 的 附加数据库 失败。 (Microsoft.SqlServer.Smo)
------------------------------
有关帮助信息,请单击: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=17.100.40.0&Evtsrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=附加数据库+Server&LinkId=20476
------------------------------
程序位置:
在 Microsoft.SqlServer.Management.Smo.Server.AttachDatabaseWorker(String name, StringCollection files, String owner, AttachOptions attachOptions)
在 Microsoft.SqlServer.Management.Smo.Server.AttachDatabase(String name, StringCollection files)
在 Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.Attach()
在 Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.SendDataToServer()
===================================
执行 Transact-SQL 语句或批处理时发生了异常。 (Microsoft.SqlServer.ConnectionInfo)
------------------------------
程序位置:
在 Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
在 Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)
在 Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries, Boolean retry)
在 Microsoft.SqlServer.Management.Smo.Server.AttachDatabaseWorker(String name, StringCollection files, String owner, AttachOptions attachOptions)
===================================
一个或多个文件与数据库的主文件不匹配。如果是尝试附加数据库,请使用正确的文件重试该操作。如果这是现有数据库,则文件可能已损坏,应该从备份进行还原。
出现文件激活错误。物理文件名 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\*****_*****_StreamFile' 可能不正确。请诊断并更正其他错误,然后重试此操作。
无法打开新数据库 '*****_*****'。CREATE DATABASE 中止。 (Framework Microsoft SqlClient Data Provider)
------------------------------
有关帮助信息,请单击: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-5173-database-engine-error
------------------------------
服务器名称: ***************
错误号: 5173
严重性: 16
状态: 3
行号: 1
------------------------------
程序位置:
在 Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
在 Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
在 Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
在 Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
在 Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
在 Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
在 Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
在 Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
在 Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
Hello 1711643472
definitively backup/restore is the best option for databases with FS but I found a couple of links that might help you. I tried both options and they worked well in my lab environment
3 Replies
- 1711643472Copper ContributorSQL Server 2016
Hello 1711643472
definitively backup/restore is the best option for databases with FS but I found a couple of links that might help you. I tried both options and they worked well in my lab environment
- 1711643472Copper Contributor
Hello Javier_Villegas
Thank you very much for your help. Due to a problem with my version of SQL Server, I was unable to set up an identical database. Luckily, I found a 21 year old backup and used it to create a consistent database and then resolved the issues encountered via the link you shared, thanks