SQL Server，将.BKP文件还原到MDF文件但不是LDF（没有空间）

I'm in an issue where I don't have enough space to accomodate my MDF and LDF files from a LiteSpeed backup we had done.

我遇到的问题是,我没有足够的空间来容纳我们完成的LiteSpeed备份中的MDF和LDF文件。

I've come up with the following sproc:

我想出了以下内容:

exec master.dbo.xp_restore_database

@database = 'OSiteDB',
@filename = 'L:\OSiteDB_2009_01_07_Wed_LiteSpeed_Full.BKP',
@with = 'move "O1_SITEDB" to "S:\OSiteDB_Data.mdf"',
@with = 'move "O1_SITEDB_Log" to "Some dev null location??"

Is there a way I can specify the LDF location to some null location? I don't want the LDF, alternatively, is there a way I can tell it not to fetch the ldf at all?

有没有办法可以将LDF位置指定到某个空位置?我不想要LDF,或者,有没有办法告诉它不要取得ldf?

3 个解决方案

#1

You need to restore the LDF as well as the MDF. The log is an integral part of the database: it's not a "database" in the RDBMS sense without it.

您需要恢复LDF以及MDF。日志是数据库不可或缺的一部分:没有它,它不是RDBMS意义上的“数据库”。

As an emergency, you need to plug in an external drive or restore to an NTFS compressed folder. Then, you can shrink the database files. However, this is only a quick fix and getw you going so you can do it properly.

在紧急情况下,您需要插入外部驱动器或还原到NTFS压缩文件夹。然后,您可以收缩数据库文件。但是,这只是一个快速修复,让你去,所以你可以正确地做到这一点。

#2

Have you looked at the WITH NORECOVERY options?

你看过WITH NORECOVERY选项了吗?

In particular, I believe you can restore the database WWITH NOCEOVERY and then the LOG WITH RECOVERY (with no log file).

特别是,我相信你可以恢复数据库WWITH NOCEOVERY,然后恢复LOG WITH RECOVERY(没有日志文件)。

http://msdn.microsoft.com/en-us/library/ms191253.aspx

#3

I don't think you can avoid restoring the LDF file. But, as you mentioned, you might be able to restore it into a temporary location.

我认为你不能避免恢复LDF文件。但是,正如您所提到的,您可以将其还原到临时位置。

From here:

--Step 1: Retrive the Logical file name of the database from backup.
RESTORE FILELISTONLY
FROM DISK = ‘L:\OSiteDB_2009_01_07_Wed_LiteSpeed_Full.BKP’
GO

--Step 2: Use the values in the LogicalName Column in following Step.
—-Make Database to single user Mode
ALTER DATABASE OSiteDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

—-Restore Database
RESTORE DATABASE OSiteDB
FROM DISK = ‘L:\OSiteDB_2009_01_07_Wed_LiteSpeed_Full.BKP’
WITH 
  MOVE ‘O1_SITEDB’ TO ‘S:\OSiteDB_Data.mdf’,
  MOVE ‘O1_SITEDB_Log’ TO ‘C:\OSiteDB_Log.ldf’

/*If there is no error in statement before database will be in multiuser mode.
If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE OSiteDB SET MULTI_USER
GO

Change the destination of the LDF file and see what happens.

更改LDF文件的目标,看看会发生什么。

#1