Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> SQL Server Disaster Recovery and Availability

Restoring an SQL 2005 Server Express DB with a LOG that is to large

SQL 2005 Express - Database Restore size problem
Was this post helpful ?

Hi,

I'm trying to restore a SQL Server DB Backup from a SQL Server DB Server on to my Laptop (SQL 2005 Express)

When I execute arestore filelistonly command on the backup file, It seems that the Database included is 1GB, but the Log file is 91 GB in size, which exceeds my diskspace.

I can restore the Data on its own without the log file, but the the Database stays in "restoring" mode. I've tried to switch the restore flag off (updatesys.databasesset state= 0wherename='G001'), but I can`t seem to be able to do it, even if I try to allow updates via:

sp_configure'allow updates', 1

GO

RECONFIGUREWITH OVERRIDE

GO

Any ideas how I can restore the database without restoring the enormous logfile?

Thanks in advance...

[3913 byte] By [ddoo] at [2008-2-14]
«« SqlBulkCopy: the locale id '0' of the source column '<column name> ' and the local
»» overload function call
# 1

I'm trying to figure out how you ended up with a 91GB log for a 1GB database. I'm guessing it was in full recovery and never had a log backup?

The first suggestion I would have would be to restore this database on a server with enough space for the log, back up the log, and then shrink it. You should then be able to back it up and have a reasonable package to restore on your laptop.

If that is not possible for some reason, your only option would be to put the database in emergency mode and mine it for data. (in other words, read data out of it into an empty database. It won't be updateable in emergency mode.)

KevinFarlee at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Disaster Recovery and Availability...
# 2

Hi Kevin,

The DB is a Customer BD and they sent it over recently, just before the System Admin went on holidays. I guess the Tx log is never shrunk, therefore holding 91 GB of Log. Since there is no easy way around restoring the database on my current configuration, I'll get an external Hard-disk, format it as NTFS, and then put all the data back on-line before shinking the Log and moving it all back to the laptops hard-disk.

Regards.

ddoo at 2007-8-30 > top of Msdn Tech,SQL Server,SQL Server Disaster Recovery and Availability...
SQL Server
SQL Server Disaster Recovery and Availability

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified