Exclude .LOG file while restoring a database

views:

answers:

+1 Q:

Exclude .LOG file while restoring a database

I have a database backup file (MyDatabase.bak). When restoring the database through SSMS it gives me "Not enough space on disk" message. I looked for an option to exclude .LOG file from restore but could not find one.

I even tried to remove the "MOVE .LOG" from restore query but got an error message "could not find .LOG file use WITH MOVE option".

Is there an option to exclude the log file while restoring the database?

Thanks

+2 A:

No: the log is an essential part of the database and is used during RESTORE process (redo/undo phases) to ensure consistency.

Use WITH MOVE to restore to a compressed folder to gain "more" disk space temporarily. Or an external USB drive etc

Edit:

By compressed, I mean NTFS compression (for XP but still applies).

WITH MOVE is part of restore exposed as "Restore the database files as" in SSMS

Edit 2:

SQL Server can not use network drives unless you enable trace flag 1807. Now, use at your own risk to get running again only

gbn 2010-08-10 19:08:42

+1 - Also a missing log can lead to a DB being marked suspect in SQL Server.

JNK 2010-08-10 19:12:18

@gbn: I tried to search for "WITH MOVE restore to compressed folder" but could not find anything useful. Can you pls. suggest how do i go about restoring to a compressed folder?

stackoverflowuser 2010-08-10 19:37:44

Additionally I tried to give a network path (Mapped network drive) but it gave up error message "Directory lookup for file R:\Log\MyDatabase.LDF" failed. System cannot find the path specified". I have ensured the path is accessible. Are network path not valid while restoring ??

stackoverflowuser 2010-08-10 19:43:27

Messing with compression is a recipe for disaster. Go back to the source of the DB, run a shrink to clean out the log file, then move it again.

JNK 2010-08-10 19:56:07

@JNK: I already looked into that option. But it seems the db is long gone. Only .bak file is available.

stackoverflowuser 2010-08-10 19:59:34

@stackoverflowuser - Then you really need to do whatever you have to in order to create space just long enough to restore the DB and shrink the log.

JNK 2010-08-10 20:05:12

@stackoverflowuser, @JNK: agree. A compressed or network folder is *only* to be used to get you going again...

gbn 2010-08-11 03:36:41

@JNK, @GBN: Thanks guys. Took the backup and shrinked the log. Worked out good !!

stackoverflowuser 2010-08-11 17:36:02

ansaurus

tags:

views:

answers:

Exclude .LOG file while restoring a database

related questions