How to restore a database with minimal log?

[DBFan]

My production db has 20 GB log space which it needs. When I want to restore its copy on to Dev in different versions as required by the business needs each time I have trouble with log space on the disk. Is there a way to restore prod backup copy on the Dev with minimal log as Dev doesn't need so much log.

I know, we can shrink prod and take a backup after shrinking the log and then restore on the Dev. But this work-around is not always possible. Any ideas are greatly appreciated!

Thanks,

[rmiao]

Not as I aware of other ways without shrinking log file on source server.

[DBFan]

Any one to help??

[skhanal]

If you can detach the production database, then detach it, copy the .MDF file to your dev environment, attach the production db.

Then in dev attach it using

EXEC sp_detach_db @dbname = 'pubs'
EXEC sp_attach_single_file_db @dbname = 'pubs',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

[DBFan]

Nope, we can't detach the production db. Client will lose millions in minutes!

[MAK]

Wait for SQL2005. Database snapshot is available.

http://databasejournal.com/features/...le.php/3524431

[DBFan]

Thanks MAK,

Is there a work-around to achieve the same thing in SQL Server 2000 as we can't move to 2005 any time soon.?

thanks,

[rmiao]

Not sure if any third party backup tools can do that.

[homebrew]

My production db has 20 GB log space which it needs. When I want to restore its copy on to Dev in different versions as required by ....(snip)

Why do you need a 20 G log ?? I always like a small log. We have full recovery, backup logs avery 10 minutes, and copy the logs to another server.

[DBFan]

It's a Peoplesoft application which has SQL Server backend and runs payroll for 40,000 employees. We have no control on code. Single big transactions take place which need so much log, so truncation or backing up of log doesn't help here.

Thanks,

[rmiao]

But you can't put db snapshot on remote server in sql2k5.