SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restore with no data


Restore with no data

Author
Message
lklein
lklein
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 286
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
Greg Charles
Greg Charles
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5517 Visits: 5927
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

Greg
lklein
lklein
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 286
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
SQL_ABD
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1514 Visits: 1349
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
thecosmictrickster@gmail.com
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3696 Visits: 939
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
Adam Haines
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3202 Visits: 3135
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
Verena_techie
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 23
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
steve smith-401573
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1007 Visits: 744
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
homebrew01
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4873 Visits: 9108
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
Verena_techie
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 23
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! Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search