Table backup

Question

Table backup

SQL Guy 1

SSCoach

Points: 15979
More actions
September 2, 2011 at 10:51 am

#390332

In SQL Server, is it possible to backup a single table? A set of tables? Without moving them between filegroups?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

just the methods you probably already know:

SELECT INTO MyTABLE_bak FROM MyTABLE, or if you want to put them in a different database, SELECT INTO SELECT INTO SpecialDatabase.dbo.MyTABLE_bak FROM MyTABLE, and then backup just that special database...

but that doesn't guarantee related data via FK constraints and the like get backed up along with it.

you could consider replicating the suite of related tables...that'd be a backup from my perspective.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 2

That actually means that it's impossible to backup. I wanted to backup a set of tables from differnt filegroups, along with all their (possible) constraints, triggers, indexes, etc.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

SQL Guy 1 (9/2/2011)
That actually means that it's impossible to backup. I wanted to backup a set of tables from differnt filegroups, along with all their (possible) constraints, triggers, indexes, etc.

Can't - minimum backup granularity is at filegroup level

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Lowell SSC Guru Points: 323518 More actions · Answer 4

even a filegroup backup might not be all you need; if all the tables are backup up in a file group, and then a change occurs to one of them, you need a Tail backup created after the file back up before you can restore the file group, right?

other wiser you get an issue like this:

/*prevents recovery due to transactions occurring
Msg 4303, Level 16, State 1, Line 1
The roll forward start point is now at log sequence number (LSN) 24000000028200001. Additional roll forward past LSN 24000000032000001 is required to complete the restore sequence.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
*/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!