RESTORE DB problem

[Waynest]

Hi

This morning I have attempted to create a test database by restoring a tape backup of an existing database.

The restore failed due to lack of disc space. Problem #1 is that I am now unable to do anything with the test DB. Enterprise manager reports that it is loading and I am unable to delete it or attempt another restore. How do I get rid of it?

If I look at the transaction log for the original database I can see that there is only 230MB used, but 8500MB allocated free space. I think this is why the restore has failed - SQL is trying to create the 8500MB free space for the test DB & there is not enough room for it.

What is the best way to approach this? Shrink the free space on the original db so the restore will work? Can I restore without creating the huge transaction log & then shrink the original? How do I shrink the log??

Thanks for any help

 

[TheBugSlayer]

There are two more ways of creating a DB from another one: 1- script the objects on DB1 and export the data to textfiles and recreate the objects on DB2 by running the scripts and then import the data into the tables; 2- detach DB1, attach it on the other server and reattach DB1.

The second option is the less tedious of the two. The sp_attach_single_file_db stored procedure allows you to attach the DB without the log file but creates a new one for you.

Well, you may need to restart the target server before trying the other solution.

Good luck.