Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Restore only 20,000 Records in Database

Author  Topic 

Limuh
Yak Posting Veteran

94 Posts
Posted - 2010-06-24 : 01:39:26
Hi,

I am planning to restore my database using my backup. but my problem is that the disc on the restore destination cannot carry the amount of data on my backup.

Is there any way that i can do to just only restore 20,000 records on my backup to my restore destination?

Or how can i reduce the size of my backup in order for me to be able to restore it on my database. Is there any way that i can do this?

Thank you.

Lumbago
Norsk Yak Master

3271 Posts
Posted - 2010-06-24 : 02:34:07
This is a regular backup file right? In that case there is no way you can limit the restore operation directly. The only way I can think of at the moment is to restore the database to another server with more capacity, then move the data you require using linked server or OPENQUERY or something. Maybe someone else have better ideas...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page

Limuh
Yak Posting Veteran

94 Posts
Posted - 2010-06-24 : 07:43:29
Yes it is, is there anymore options here?
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2010-06-24 : 07:44:41
I think the www.red-gate.com crew have a backup/restore tool which allows selective restores from within a backup object - it may require use of their tool to create the backup in the first place, but they do support in general the Microsoft SQL Server Backup format.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-06-24 : 13:28:56
Variation on Lumbago's suggestion:

Restore to a machine with sufficient space
Delete all-bar 20,000 records
Delete / drop any other tables that are not required
Shrink database
Backup
Restore to "small" machine

or


Restore to a machine with sufficient space
Transfer 20,000 records to a new, empty, database
Backup New database
Restore that to "small" machine
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-06-24 : 13:28:56
Variation on Lumbago's suggestion:

Restore to a machine with sufficient space
Delete all-bar 20,000 records
Delete / drop any other tables that are not required
Shrink database
Backup
Restore to "small" machine

or


Restore to a machine with sufficient space
Transfer 20,000 records to a new, empty, database
Backup New database
Restore that to "small" machine
Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts
Posted - 2010-06-25 : 03:05:34
Hmmmm.....blatent "post-count" bump by Kristen
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2010-06-25 : 10:20:46
I agree.


CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2010-06-25 : 10:20:58
I agree.


CODO ERGO SUM
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-06-26 : 16:55:20
It has happened twice now, clearly a bug in the Forum software.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-06-26 : 16:55:31
.erawtfos muroF eht ni gub a ylraelc ,won eciwt deneppah sah tI
Go to Top of Page

NeilG
Aged Yak Warrior

530 Posts
Posted - 2010-06-28 : 08:37:40
I'd go down the route of restoring the database to another server with the required capacity and then move the required data using the linked server, openquery, openrow set

one of these should do the trick
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site