December 14, 2008 at 11:34 pm
Dear All,
I have XYZ database. I want to take backup of only 4 tables instead of entire database.
Simarly. If i want to restore , i have to restore only those 4 tables.
Please help me to solve this problem
Regards
Prakash
December 14, 2008 at 11:42 pm
You can move those four tables in a different database file. take file/filegroup backup. You'll be able to restore them.
http://msdn.microsoft.com/en-us/library/ms189906.aspx
http://msdn.microsoft.com/en-us/library/ms179401.aspx
.
December 15, 2008 at 12:25 am
ps (12/14/2008)
You can move those four tables in a different database file. take file/filegroup backup. You'll be able to restore them.
You'd still need to backup and restore the primary filegroup as well. Since primary contains the system tables, there's no way to restore a database without it. You'll also need transaction log backups if the filegroups aren't read-only.
The only real way to backup just tables is to bcp the data to file and save those files somewhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2008 at 12:54 am
Need some clarification on this...
-------------
Assuming I've 3 filegroups, primary1, secondary1 and secondary2
I take filegroup backups with log backups.
If i need to restore all files from secondary1 filegroup(assuming this filegroup resided on a particular disk which crashed; primary1 and secondary2 filegroups are placed on different disks and are healthy),
do i just restore the secondary filegroup along with logs backups
OR
do i have to restore primary, secondary1 and the log backups for this?
December 15, 2008 at 1:02 am
Is the database still online?
SQL 2000, 2005 or 2008?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2008 at 1:23 am
Its 2000 and the database is online.
Was browsing through sql server 2000 KB, and I think i got the answer....
http://support.microsoft.com/kb/281122
pasting an excerpt from the link..
you have a database that consists of a primary filegroup, filegroup A and filegroup B. A table that resides on filegroup B is accidentally deleted. If you have filegroup and transaction log backups available, you can restore only filegroup B along with the primary filegroup to regain the deleted table.
Even if the database is online; since i'm restoring the primary filegroup, i need to put the database in single_user mode right?
This leads to another query....
Backup of secondary1 filegroup gets over at 2PM and primary1 filegroup at 4 PM. Filegroup1 crashes at 5 PM.
To restore filegroup1, i need all log backups from 2PM-5PM or from 4PM-5PM?
December 15, 2008 at 4:09 am
ps (12/15/2008)
Even if the database is online; since i'm restoring the primary filegroup, i need to put the database in single_user mode right?
It's never necessary to put a DB into single user mode to restore. You just need to be sure that no one's using it. The restore will need an exclusive database lock
Backup of secondary1 filegroup gets over at 2PM and primary1 filegroup at 4 PM. Filegroup1 crashes at 5 PM.
To restore filegroup1, i need all log backups from 2PM-5PM or from 4PM-5PM?
From the earliest of the backups you're restoring. In this case, the secondary data file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2008 at 4:45 am
Thanks Gail for your valuable comments 🙂
I think i understand the backup fundamentals much better.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply