如何获取卡住的MS SQL数据库的文件路径恢复

I distribute updates to our database in the form of backups "With Replace", but the users can install it to any location on any hard drive, which means I need to "Exec SP_HelpFile" on it before running "Restore With Move". But SP_HelpFile requires me to "Use TheDatabase" first, which I can't do if it's already stuck in a failed restore. I can't even run "With Recovery" if I can't tell it where to restore. Is there any way to use the Master database to get the filenames? Right now my only solution is to delete and reinstall, but I'd like the automated updater to be able to handle it on its own.

我以备份“With Replace”的形式向我们的数据库分发更新,但是用户可以将其安装到任何硬盘驱动器上的任何位置,这意味着在运行“Restore With Move”之前我需要“Exec SP_HelpFile”。但是SP_HelpFile要求我先“使用TheDatabase”,如果它已经卡在失败的恢复中,我就无法做到。如果我不能告诉它在哪里恢复,我甚至无法运行“With Recovery”。有没有办法使用Master数据库来获取文件名?现在我唯一的解决方案是删除并重新安装,但我希望自动更新程序能够自行处理它。

UPDATE: Server version is 2005 & 2008

更新:服务器版本是2005年和2008年

3 个解决方案

#1

You should be able to get the path+filename without having to use the database. sp_helpfile uses the local sys.sysfiles but for now at least you can use the catalog view sysaltfiles, still in master for backward compatibility reasons... this is assuming SQL Server 2005+ (please always specify the version of SQL Server you're using):

您应该能够获得路径+文件名而无需使用数据库。 sp_helpfile使用本地sys.sysfiles但是现在至少你可以使用目录视图sysaltfiles,仍然在master中以便向后兼容......这是假设SQL Server 2005+(请始终指定你正在使用的SQL Server版本):

SELECT filename 
    FROM master.sys.sysaltfiles
    WHERE db_id = DB_ID('database name');

#2

In SQL 2005+, you can use the system view sys.master_files.

在SQL 2005+中,您可以使用系统视图sys.master_files。

select name, physical_name
from sys.master_files
where db_id = db_id('your_database')

#3

In the SQL Server 2008, you should use as follows:

在SQL Server 2008中,您应该使用如下:

SELECT filename 
    FROM master.sys.sysaltfiles
    WHERE dbid = DB_ID('db_name');

#1

SELECT filename 
    FROM master.sys.sysaltfiles
    WHERE db_id = DB_ID('database name');

#2