Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Restore with no data Expand / Collapse
Author
Message
Posted Monday, December 3, 2007 9:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 22, 2014 3:06 PM
Points: 64, Visits: 263
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
Post #428800
Posted Monday, December 3, 2007 9:28 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 4,065, Visits: 5,284
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
Post #428808
Posted Monday, December 3, 2007 9:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 22, 2014 3:06 PM
Points: 64, Visits: 263
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?
Post #428823
Posted Monday, December 3, 2007 11:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:13 AM
Points: 1,326, Visits: 1,283
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
Post #428890
Posted Monday, December 3, 2007 1:42 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 5:05 PM
Points: 2,644, Visits: 824
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
Post #428964
Posted Thursday, December 6, 2007 10:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 2:10 PM
Points: 2,278, Visits: 3,054
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
Post #430306
Posted Tuesday, August 26, 2008 12:29 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 8, 2008 12:34 PM
Points: 7, 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
Post #559110
Posted Tuesday, August 26, 2008 2:25 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 21, 2012 1:52 PM
Points: 696, Visits: 743
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.
Post #559192
Posted Tuesday, August 26, 2008 6:30 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:10 PM
Points: 2,826, Visits: 8,463
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...


Post #559287
Posted Tuesday, August 26, 2008 9:52 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 8, 2008 12:34 PM
Points: 7, 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! :)
Post #559321
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse