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?

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

hello i m new in oracle can any body tell me what is filelistonly

regads shubham