Home » Windows OSRSS

File and Filegroups Backup and Restore in a Partitioned Database

Hi T-SQL Experts,

BACKGROUND:

I have a common database for several units. These units have common partitioned tables. These units data will be directed to related file and filegroup using 'partition scheme'. e.g. when unit#1 add a record, this record will be saved in file 'Unit001' at filegroup 'Unit001' i.e. each unit have one and only one file and also filegroup.

THE DESIRE:

I want to backup and restore each unit individually without any effect on other units. e.g. when unit#1 wants to backup it's data, I want to backup file 'Unit001' at filegroup 'Unit001' into 'Unit001.bak' for example.

THE PROBLEM:

I tried to do this for unit#2 as below:

At first I backed up it's file using this:
BACKUPDATABASE [MYDBOLTP]
 FILE = N'Unit002',
 FILEGROUP = N'Unit002'TODISK = N'C:\Users\Yasser\Desktop\unit002.bak'WITH NOFORMAT, INIT,
 NAME = N'MYDBOLTP-Full Unit002 Filegroup Backup',
 SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM--OUTPUT--100 percent processed.--Processed 120 pages for database 'MYDBOLTP', file 'Unit002' on file 1.--Processed 2 pages for database 'MYDBOLTP', file 'MYDBOLTP_log' on file 1.--BACKUP DATABASE...FILE=<name> successfully processed 122 --pages in 1.115 seconds (0.854 MB/sec).
Then I restored it's data like below:
RESTOREDATABASE [MYDBOLTP]
 FILE = N'Unit002',
 FILEGROUP = N'Unit002'FROMDISK = N'C:\Users\Yasser\Desktop\unit002.bak'WITHFILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
--OUTPUT--100 percent processed.--Processed 120 pages for database 'MYDBOLTP', file 'Unit002' on file 1.--The roll forward start point is now at log sequence number (LSN) 224000000011400001. Additional roll forward past LSN 224000000033200001 is required to complete the restore sequence.--RESTORE DATABASE ... FILE=<name> successfully processed 120 pages in 0.595 seconds (1.575 MB/sec).
And finally, I reached the problem, I can't select any data from Unit002 and raises following error:
select * from SKGOLTPSchema.Unit002.MoshakhasatKarkonan
--OUTPUT--Msg 679, Level 16, State 1, Line 1--One of the partitions of index 'PK_MoshakhasatKarkonan' for table 'Core.MoshakhasatKarkonan'(partition ID 72057594081443840) resides on a filegroup ("Unit002") that cannot be accessed because it is offline, restoring, or defunct. This may limit the query result.

What is the problem?! I just want to restore early backup file created by T-SQL itself using itself. I WONDERED HOW TWO TRUE JOBS CAUSE A FALSE JOB. I.E. BACKUP WITHOUT ERROR & RESTORE WITHOUT ERROR => OFFLINE FILE(GROUP)!!!

Thanks in adnavce.

Sincerely,

Yasser


LEARN HOW WPF IS FLEXIBLE IN PRESENTATION DURING A QUICK SIMPLE WALKTHROUGH:
Walkthrough: Displaying multi column ComboBox by using Windows Presentation Foundation (WPF) data templating
 

17 Answers Found

 

Answer 1

The output  of the restore  command says:

Additional roll forward past LSN 224000000033200001 is required to complete the restore sequence.

You have to restore your transaction log and do a WITH RECOVERY to have the file  online.

Tom

 

Answer 2

Thank you Tom,

I tried following but raises error again :(

RESTOREDATABASE [MYDBOLTP]
 FILE = N'Unit002',
 FILEGROUP = N'Unit002'FROMDISK = N'C:\Users\Yasser\Desktop\unit002.bak'WITHFILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
RESTORELOG [MYDBOLTP]
  FROMDISK = N'C:\Users\Yasser\Desktop\unit002.bak'WITH RECOVERY
GO
--OUPUT--100 percent processed.--Processed 120 pages for database  'MYDBOLTP', file  'Unit002' on file 1.--The roll forward start point is now at log sequence number (LSN) 224000000011400001. Additional roll forward past LSN 224000000033200001 is required to complete the restore  sequence.--RESTORE DATABASE ... FILE=<name> successfully processed 120 pages in 0.665 seconds (1.409 MB/sec).--Msg 4305, Level 16, State 1, Line 1--The log in this backup  set begins at LSN 224000000025800070, which is too recent to apply to the database. An earlier log backup that includes LSN 224000000011400001 can be restored.--Msg 3013, Level 16, State 1, Line 1--RESTORE LOG is terminating abnormally.

Could you please rewrite my SQL Statements in my first post for unit#3 for example?

Thanks.

 

Answer 3

BOL 2008: If data  is partitioned, piecemeal backup  in SQL Server can be implemented. Piecemeal backup and restore  operations in SQL Server offer more flexibility for managing partitions. Piecemeal operations imply that individual partition(s), when confined to their own filegroup(s), can be backed  up and restored individually  without affecting the entire database. The filegroup  has to be in read-only mode for piecemeal backup to work in a Simple Recovery model. In the case of either the Bulk-Logged Recovery model or the Full Recovery model, it is necessary to back up the transaction log(s). Doing so is essential to restore the filegroup successfully.

Yasser - are you trying to restore into a live database? As Tom noted, what happens to transactions which hit the partition  in between the backup and restore?


Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
 

Answer 4

Hi Yasser,

The log you are trying to apply is too recent. You must restore  all transaction logs (in order) after the full backup  (chain must be in order), the last one WITH RECOVERY

Tom

 

Answer 5

SQL-USA,

I do nothing between the backup  and restore. I'm new to this issue and just tried to backup and immediately restore the file/filegroup that I reached this problem, I can select from other units  (partitions) but cannot from restored unit.

Actually, I'm confused how I should back to previous state (online filegroup  with restored data)!

Thanks.


LEARN HOW WPF IS FLEXIBLE IN PRESENTATION DURING A QUICK SIMPLE WALKTHROUGH:
Walkthrough: Displaying multi column ComboBox by using Windows Presentation Foundation (WPF) data  templating
 

Answer 6

Yasser, you can only do it that way for read-only partitions. See previous note.

 

Answer 7

Tom,

I don't back up and don't have any other backup  file except unit001.bak and unit002.bak. I could restore  T-LOG from unit001.bak but couldn't from unit002.bak.

Please help me on followings:

How I can get my previous database  state with these two backup files? If it's impossible because of my mistake, no problem, how I should backup and restore a new unit  e.g. unit#3 which doesn't cause the problem?

Thanks.

 

Answer 8

You need to go back to the last full backup  and restore  it. As Tom indicated if you have transaction log backups, those need to be restored in order as well, the very last one with RECOVERY.
 

Answer 9

SQLUSA,

This is the complete scenario which I done so far:

I installed the application which creates the partitioned  database with no error. I converted units  data using application itself from my previous foxpro DOS database. I tried to backup  and restore  unit#1 which I reached the error. I tried same for unit#2  but no hope.

As you see, I don't have any full-backup or T-LOG backup from the database  but only two units filegroups  backup.

Actually, in a standard manner at software engineering, any mistake by user should be undoable. I have my backups ready, so, how I should undo my actions?!

Thanks.


LEARN HOW WPF IS FLEXIBLE IN PRESENTATION DURING A QUICK SIMPLE WALKTHROUGH:
Walkthrough: Displaying multi column ComboBox by using Windows Presentation Foundation (WPF) data  templating
 

Answer 10

BOL 2008: If data  is partitioned, piecemeal backup  in SQL Server can be implemented. Piecemeal backup and restore  operations in SQL Server offer more flexibility for managing partitions. Piecemeal operations imply that individual partition(s), when confined to their own filegroup(s), can be backed  up and restored individually  without affecting the entire database. The filegroup  has to be in read-only mode for piecemeal backup to work in a Simple Recovery model. In the case of either the Bulk-Logged Recovery model or the Full Recovery model, it is necessary to back up the transaction log(s). Doing so is essential to restore the filegroup successfully.

Yasser - are you trying to restore into a live database? As Tom noted, what happens to transactions which hit the partition  in between the backup and restore?


Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM


SQLUSA, yes, that is my desire  :)

I saw that article but it seems that it doesn't have any example  about how to 'piecemeal backup'.

Could you help me here please if it's a short code? Or if you know some more resources at web.

Thanks.

 

Answer 11

Yasser,

Piecemeal backup  of live (not read-only) database  is very complex, senior DBA domain. Not recommended.

BOL 2008: "The primary disadvantage of file  backups compared to full database backups is the additional administrative complexity. A media failure can make a complete database unrecoverable if a damaged file lacks a backup. You must therefore maintain a complete set of file backups, and, for the full/bulk-logged recovery model, one or more log backups covering minimally the interval between the first full file backup and last full file backup."

 

Answer 12

Thank you SQLUSA,

But how I should implement it, that is each unit  should can backup/restore it's data  with no concern about other units?

What is your recommended scenario for a newbie?

Thanks :)

 

Answer 13

What is your recommended scenario for a newbie?


 

Use database  Maintenance Plan Wizard (SSMS Object Explorer, Management) to prepare a backup  plan.

BOL 2008 link:

The Maintenance Plan Wizard helps you set up the core maintenance tasks to make sure that your database performs well, is regularly backed  up, and is free of inconsistencies. The Maintenance Plan Wizard creates one or more SQL Server Agent jobs that perform these tasks on local servers

 

 

Answer 14

Hi SQLUSA,

I found it but doesn't address my issue. Actually, I'm not completely newbie :)

It doesn't address my issue because units  can be removed or added by end-user him/herself. So, I need a flexible T-SQL solution, not SQL Server wizards or GUIs to allow to user to backup/restore his/her unit.

Thanks.

 

Answer 15

It doesn't address my issue because units  can be removed or added by end-user him/herself. So, I need a flexible T-SQL solution, not SQL Server wizards or GUIs to allow to user to backup/restore his/her unit.


 

How about giving each user  his/her own database?


Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
 

Answer 16

How about giving each user  his/her own database?
Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM


Unfortunately, this mission is impossible :), because of:

Over 20 units  cause a verbose SQL Server specially that they have databases from other companies too. Changing the architecture of the software needs money and time which we don't have, specially the time. Hard support implementation for units manager end-user who wants to query against one, two or more units.

Does T-SQL support my issue? if so, how?

Thanks for your attention.

 

Answer 17

But how I should implement it, that is each unit  should can backup/restore it's data  with no concern about other units?

Yasser, to be frank you have two options:

1) Give each unit their own database.

2) Accept that BACKUP/RESTORE concerns all units.

Yes, SQL Server includes options for restoring a single filegroup, and this is useful in the case when the disk  with the filegroup  crashes. Now you can restore  thar filegroup only, and and then apply the the transaction log backups to bring the database back to life.

But say that a user in Unit #4 runs a DELETE without a WHERE clause, and asks for his filegroup to be restored to the point in time before the DELETE, this cannot work out, because the database  will no longer be in a consistent state.

And this the key: after each completed restore sequence, SQL Server must guarantee that the database is transactionally conistent. If it can't, it will not let you do it.

Finally, I like to point out that this is not really the best forum for the topic. This group is for T-SQL programming, and this an admin issue.

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter