Can I restore without transaction log space? 1

[bessebo]

I want to restore my production database to a Test Server that is limited in disk space. The database that I want to restore from has 10GB of data and 2GB of log space. I want to restore my production backup to my testserver but I want to exclude the 2GB of transaction log. Can I do this?

Thanks in advance,
Bessebo

 

[tlbroadbent]

You can't restore without the transaction log. However, you can immediately detach the databsae after restoring it. The delete the transacion log (ldf) file and attach just the MDF using sp_attach_single_file_db. See BOL for details.

Syntax:
sp_attach_single_file_db [@dbname =] 'dbname',
[@physname =] 'physical_name'

EXEC sp_attach_single_file_db @dbname = 'pubs',
@physname = 'c:\mssql7\data\pubs.mdf'

SQL will create a new minimum sized transaction log. Be aware that transactions will be recorded in the log on the test server. You'll want to set truncate log on checkpoint ON (SQl 7) or recovery model to Simple (SQL 2000).

Another option: This is only possible if you can afford to have the production DB down for a few minutes. Detach the database on production. Copy only the MDF to the test Server. Reattach the production database. Use sp_attach_single_file_db on the test server. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[bladesk8er]

Hi,
I have a (perhaps) similar problem ... I have my database files intact on disk, but the transaction log files have been deleted. The database opens in status 'suspect' ... is there any way I can 'use' my known good database files to re-constitute my database?

 

[toetag]

bladesk8er,

not sure if it applies to you, but i've taken a suspect database. deleted it. re-created it, then restored with a known good full DB backup.