SQL Server, Restore a .BKP file into an MDF file but not LDF (no space for it)

Question

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.

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?

gbn · Answer 1 · 2009-08-03T15:03:15.883

2

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.

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.

edited Aug 03 '09 at 15:03

answered Apr 10 '09 at 14:15

gbn

422,506
82
585
676

And to clarify - an NTFS compressed folder is not recommended for production servers. It's going to be some ugly performance. – Brent Ozar Apr 14 '09 at 12:53
Good point. Absolutely. It's a workaround *only* to get running to allow remedial action – gbn Apr 14 '09 at 14:28

score 0 · Answer 2 · answered Jan 29 '09 at 23:57

0

Have you looked at the WITH NORECOVERY options?

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

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

answered Jan 29 '09 at 23:57

Cade Roux

88,164
40
182
265

WITH NORECOVERY only allows further RESTORE LOG files. This is nothing to do with the LDF file – gbn Apr 10 '09 at 17:00

beach · Answer 3 · 2009-01-30T00:08:45.837

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.

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.

SQL Server, Restore a .BKP file into an MDF file but not LDF (no space for it)

3 Answers3