Backup/restore parition

Question

Backup/restore parition

Tara-1044200

SSCoach

Points: 15951
More actions
January 20, 2015 at 9:37 am

#294624

I do partitions on all my tables based on a date column and the created 1 file for each month. Now i want to backup and restore only 1 year worth of data (12 files/ file groups), is that possible? if so how?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

I believe it's as simple as backing up and restoring by file name. If you need to do a restore and the partition file isn't read_only, then you'll need to do a Tail Log Backup first and that won't take long at all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

These two movies by Kimberly Tripp should help you a lot.

https://technet.microsoft.com/en-us/sqlserver/gg545009.aspx

https://technet.microsoft.com/en-us/sqlserver/gg545008.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tara-1044200 SSCoach Points: 15951 More actions · Answer 3

is it possible to do file group restore keeping the database in "simple" recovery model? we always keep the databases in simple mode as the data is not changing on a daily basis, changes only once in a month.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

No, not unless the file groups in question are read only.

To restore a DB from file/filegroup backups, the following is required:

1) If the DB is in simple recovery, then any filegroup backed up at a different time to PRIMARY has to have been read-only at the time of backup or it cannot be brought online

2) If the DB is in full recovery, then log backups are needed over the interval from the oldest backup used to the newest.

It's never a case of just restoring the file/filegroup. If you're restoring to a new database, the Primary filegroup has to be restored first, then any file/filegroup backups, the the log backups need to be restored in order to bring everything online. Restoring over an existing DB is even more complex.

There's also restrictions on Enterprise vs Standard edition.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tara-1044200 SSCoach Points: 15951 More actions · Answer 5

Thanks Gail. I think i will have to go with your 1st option, here is what i think please correct me if i understood wrong

1. My database is in simple recovery and i will make the entire database read only

2. I will backup the full database as usual no differential or transaction backups as i we change data only once a month like a monthly load when users will not access the databases.

3. restore only the file groups that is needed.

4. yes i do have sql server 2005 enterprise. I will be backing up from 2008 R2 and restore on 2005 Ent.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

GilaMonster (1/21/2015)
No, not unless the file groups in question are read only.
To restore a DB from file/filegroup backups, the following is required:
1) If the DB is in simple recovery, then any filegroup backed up at a different time to PRIMARY has to have been read-only at the time of backup or it cannot be brought online
2) If the DB is in full recovery, then log backups are needed over the interval from the oldest backup used to the newest.
It's never a case of just restoring the file/filegroup. If you're restoring to a new database, the Primary filegroup has to be restored first, then any file/filegroup backups, the the log backups need to be restored in order to bring everything online. Restoring over an existing DB is even more complex.
There's also restrictions on Enterprise vs Standard edition.

You're not including online piecemeal restores in that, correct?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Tara-1044200 (1/21/2015)
I will be backing up from 2008 R2 and restore on 2005 Ent.

No, you won't, because SQL databases cannot be downgraded. If you have a SQL 2008 R2 database you can restore to SQL 2008 R2, SQL 2012 or SQL 2014.

If you're going to take a full database backup and then only restore some partitions, then the read-only requirement is not relevant. That requirement comes into play when you're backing up filegroup 1 on day 1, filegroup 2 on day 2, etc and then you want to restore those backups into a single database.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Jeff Moden (1/21/2015)
GilaMonster (1/21/2015)
If you're restoring to a new database, the Primary filegroup has to be restored first, then any file/filegroup backups, the the log backups need to be restored in order to bring everything online. Restoring over an existing DB is even more complex.
You're not including online piecemeal restores in that, correct?

No, because the OP had no mention of restoring a bit now and a bit later. That's even more complex and is what has the Enterprise restriction.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

GilaMonster (1/21/2015)
Jeff Moden (1/21/2015)
GilaMonster (1/21/2015)
If you're restoring to a new database, the Primary filegroup has to be restored first, then any file/filegroup backups, the the log backups need to be restored in order to bring everything online. Restoring over an existing DB is even more complex.
You're not including online piecemeal restores in that, correct?
No, because the OP had no mention of restoring a bit now and a bit later. That's even more complex and is what has the Enterprise restriction.

Got it. Thanks, Gail. Thought I had reeled it out and stepped on it again.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

katia.puente SSC Enthusiast Points: 155 More actions · Answer 10

I am very frequently using "Ahsay software" and quite satisfied with their . Thus i am leaving below this comment just have a look at the link and the software too.