Restore with no data

Question

Restore with no data

lklein

Ten Centuries

Points: 1234
More actions
December 3, 2007 at 9:11 am

#127216

Can I do a restore without restoring the data in the tables?
Thinking there should be an option to see in this restore code...
RESTORE DATABASE [PLC_LIVE] FROM
DISK = N'filelocation\x.BAK' WITH FILE = 1,
NOUNLOAD, REPLACE, STATS = 10
GO

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Not with native SQL Server backup and restore. You can script tables in Enterprise Manager or Query Analyzer and run the script to create tables without data or you can use the "Copy objects and data..." option in the DTS Import/Export Wizard to create tables without copying data.

Greg

lklein Ten Centuries Points: 1234 More actions · Answer 2

Here's the deal - we had a problem a few weeks ago and one of the guys deleted the log file. So, we created a new Database same name on different server. It doesn't have alot of room so the restore won't work there. All I really need is the table structure and stored procedures.

So, a moment ago I tried deleting the original database and it gave me an error 'alter database is not permitted while a database is in the restoring state' - how do I stop that. My guess is another IT person here tried a restore - and so it's been sitting in limbo for who knows how long. How do I stop that?

SQL_ABD SSCertifiable Points: 5832 More actions · Answer 3

Hi,

From Bol

http://msdn2.microsoft.com/en-us/library/ms174269.aspx

When a database is in the RESTORING state, most ALTER DATABASE statements will fail. The exception is setting database mirroring options. A database may be in the RESTORING state during an active restore operation or when a restore operation of a database or log file fails because of a corrupted backup file.For more information, see Responding to SQL Server Restore Errors Caused by Damaged Backups

Regards,

Ahmed

thecosmictrickster@gmail.com SSChampion Points: 10386 More actions · Answer 4

Since you are going to restore the database again anyway, you could try:

RESTORE DATABASE dbname WITH RECOVERY

That should bring the database online. Then you can delete it & try again.

Scott Duncan

MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare

Adam Haines SSC-Insane Points: 23217 More actions · Answer 5

As Scott said, RESTORE DATABASE dbname WITH RECOVERY, this will remove the database from a recovering state. Delete the database. Then restore from backup.

My blog: http://jahaines.blogspot.com

Verena_techie SSC Veteran Points: 277 More actions · Answer 6

So, it's still not really clear to me... I want to attach to a db (very large, sql 2000) on another server and create an empty db on the new server (which is sql 2005) which has limited disk space. What is the best way to do this? Is there a way to restore with no data?

In addition, I require a subset of data. To get a subset I'll likely have to copy the extract sql and use that to create an extract script of my own for testing. Better ideas?

Thanks

steve smith-401573 SSChampion Points: 10889 More actions · Answer 7

The 'brute force' or 'inelegant' way of solving your problem is to copy your backup to a second version of the db on the 2000 server, delete all data (truncate?), and back up that database. Then port the 'empty' backup over to your sql2005 server and load the structure. Alternatively, it's attempting to capture all the scripts only to recreate an empty database.

I know there are some interesting options that allow you to capture information about the backup, without actually performing a backup, but you want to perform a 'hybrid' backup of structures and programs only.

Alternatively, you could create a publication, if the two servers can talk to each other, and put filters on each article to send 0 data over. But this is another flavor of a brute force solution.

In 2000, DTS allows you to script out the database without too much difficulty, if I remember correctly from a thread on the replication forum - that may be your best route.

homebrew01 SSC Guru Points: 55539 More actions · Answer 8

I haven't been on 2000 in a little while, but I think you can script out all the objects from your production database to a single .SQL file. Then create a new database. Then run that script in your new empty database to create empty tables, SPs, views etc...

Verena_techie SSC Veteran Points: 277 More actions · Answer 9

Thanks for your suggestions. DTS was giving me failures on copying some of the objects.... This db has multiple file groups, anyway, never made it past the failure. (and it said copy files and data where I only wanted files). There are multiple file groups, which may be an issue in itself?

The db is too big to do anything with as far as moving, copying etc. And we don't have enough extra space on ANY server! 17 gig db with only a few gig available at the best of times on the current db server. I know.... not the best situation, but it's why we are moving to a new 64 bit server.

I will try the scripting method, it might take all day to get a script but it's likely my only option. Then do I have to fix the logins for sql 2005?

Thanks again in advance for your help. If we were given the appropriate disk space none of this would really be an issue at all! 🙂

mrosenthal SSC Rookie Points: 32 More actions · Answer 10

Object level Recovery tool in LiteSpeed SQL is the solution.

Ray Mond SSCertifiable Points: 7159 More actions · Answer 11

SQL Compare 7 from Red Gate Software will allow you to do that. Create an empty database and then compare the backup file to this empty database.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.