无法再导出SQL数据库 [英] Can't export SQL Database anymore

问题描述

我不确定是否有其他人遇到此问题，但是从7月3日开始，任何导出SQL数据库的尝试（通过门户网站或PowerShell）都会超时并抛出一个"对象引用"未设置为对象的实例。"。

I am not sure if anyone else has had this problem, but starting from the 3rd of July, any attempt to export an SQL Database (via the portal or powershell) times-out and throws a "Object reference not set to an instance of an object.".

我也尝试通过Visual Studio导出，它会导致VS崩溃，或者在导出架构步骤中显示相同的错误。

I tried exporting through Visual Studio too and it either crashes VS or it shows the same error during the export schema step.

如果我尝试通过SQL重新创建数据库模式，新数据库上的导出功能可以正常工作（当然没有数据）。

If I try to recreate the db schema via SQL, the Export functionality on the new db works fine (without the data of course).

所以，我坚持使用一个完全正常工作的生产数据库，我无法导出/备份......这实际上没用。

So, I am stuck with a fully working production database that I can't export/backup ... which is not really useful.

我认为问题是在我不小心在数据库上创建了一个同步组然后立即将其删除时启动的。但是，鉴于Sync DB功能目前正在被更新的东西取代，我认为它以某种方式破坏了db。
例如，它没有清理许多与Sync相关的表。我尝试删除它们，以防出口问题，但这也没有帮助。

I think that the problem started when I accidentally created a Sync Group on the DB and then deleted it right away. However, given that the Sync DB feature is currently in the process of being replaced by something newer, I think it corrupted the db somehow. It left for example a number of Sync-related tables which it did not clean up. I tried removing them, in case that was the export problem, but that did not help either.

所以，我的问题是，有没有人最近有类似的问题吗？有没有办法恢复数据库？

So, my question is, did anyone have any similar issues lately? Is there any way to recover the data of the db?

谢谢

推荐答案

您好，

请尝试运行以下脚本手动删除SQL Data Sync对象，然后尝试再次导出数据库：

-- Triggers

DECLARE @TRIGGERS_SQL VARCHAR（MAX）=（
SELECT
'DROP TRIGGER ['+ SCHEMA_NAME（so.uid）+']。['+ [so]。[name] +']'
FROM sysobjects AS [so]
INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id
WHERE [so]。[type] ='TR'
AND [so] .name LIKE'％_ dss _％_ trigger'
FOR XML PATH（''）
）
PRINT @TRIGGERS_SQL
IF LEN（@TRIGGERS_SQL）> 0
BEGIN
EXEC（@TRIGGERS_SQL）
END


- 表
DECLARE @TABLES_SQL VARCHAR（MAX）=（
SELECT
'DROP TABLE ['+ table_schema +'] 。['+ table_name +']'
FROM
information_schema.tables where table_schema ='DataSync'
FOR XML PATH（''）
）
PRINT @TABLES_SQL
IF LEN（@TABLES_SQL）> 0
BEGIN
EXEC（@TABLES_SQL）
END

- 存储过程
DECLARE @PROC_SQL VARCHAR（MAX）=（
SELECT'DROP PROCEDURE ['+ routine_schema +']。['+ routine_name +']'
FROM INFORMATION_SCHEMA .ROUTINES，其中ROUTINE_SCHEMA ='DataSync'和routine_type ='PROCEDURE'
FOR XML PATH（''）
）
PRINT @PROC_SQL
如果LEN（@PROC_SQL）> 0
BEGIN
EXEC（@PROC_SQL）
END


- 类型
DECLARE @TYPE_SQL VARCHAR（MAX）=（
SELECT
'DROP TYPE ['+ SCHEMA_NAME（so.uid）+']。['+ [so]。[name] +']'
FROM systypes AS [so]
其中[so] .name LIKE'％_ dss_bulktype％'
AND SCHEMA_NAME（so.uid）='Datasync'
FOR XML PATH（''）
）
PRINT @ TYPE_SQL
如果LEN（@TYPE_SQL）> 0
BEGIN
EXEC（@TYPE_SQL）
END

- 架构
DROP SCHEMA DataSync

DECLARE @TRIGGERS_SQL VARCHAR(MAX) = ( SELECT 'DROP TRIGGER [' + SCHEMA_NAME(so.uid) + '].[' + [so].[name] + '] ' FROM sysobjects AS [so] INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id WHERE [so].[type] = 'TR' AND [so].name LIKE '%_dss_%_trigger' FOR XML PATH ('') ) PRINT @TRIGGERS_SQL IF LEN(@TRIGGERS_SQL) > 0 BEGIN EXEC (@TRIGGERS_SQL) END -- Tables DECLARE @TABLES_SQL VARCHAR(MAX) = ( SELECT 'DROP TABLE [' + table_schema + '].[' + table_name + '] ' FROM information_schema.tables where table_schema = 'DataSync' FOR XML PATH ('') ) PRINT @TABLES_SQL IF LEN(@TABLES_SQL) > 0 BEGIN EXEC (@TABLES_SQL) END -- Stored Procedures DECLARE @PROC_SQL VARCHAR(MAX) = ( SELECT 'DROP PROCEDURE [' + routine_schema + '].[' + routine_name + '] ' FROM INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = 'DataSync' and routine_type = 'PROCEDURE' FOR XML PATH ('') ) PRINT @PROC_SQL IF LEN(@PROC_SQL) > 0 BEGIN EXEC (@PROC_SQL) END -- Types DECLARE @TYPE_SQL VARCHAR(MAX) = ( SELECT 'DROP TYPE [' + SCHEMA_NAME(so.uid) + '].[' + [so].[name] + '] ' FROM systypes AS [so] where [so].name LIKE '%_dss_bulktype%' AND SCHEMA_NAME(so.uid) = 'Datasync' FOR XML PATH ('') ) PRINT @TYPE_SQL IF LEN(@TYPE_SQL) > 0 BEGIN EXEC (@TYPE_SQL) END -- Schema DROP SCHEMA DataSync

希望这会有所帮助。

问候，

Alberto Morillo

SQLCoffee.com

Alberto Morillo
SQLCoffee.com

这篇关于无法再导出SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！