Backup SQL table along with Partitions

Question

Backup SQL table along with Partitions

Rathirojini Sangilipandy 241

HI ,

I need to backup SQL table from One DB to another along with the partition. Here are some info

1) Table size is very big each partition has more than 5m rows and there are almost 30 partitions.
2) Table need to be backup along with the partition
3) SQL server Management studio 18 version will be used .
4) can this backup be treated as normal backup or since the partition is enabled some things need to be consider?

Looking for the best way to backup this table .

Thanks!

0 comments

Answer accepted by question author

Dan Guzman 9,516

You mention backup but do you actually mean copy the partitioned table from one database to another?

There is no object-level backup/restore in SQL Server. A normal backup includes the entire database and a restore includes all objects. Specialized filegroup/file backups can be leveraged to reduce recovery time but the key point is backups are essentially storage-level operations used to restore an entire database as part of a recovery plan, not to selectively copy objects from one database to another.

There a number of options to make an copy of a table, partitioned or not. The appropriate one depends on how how you plan to use the backup copy. For example, if your goal is to have a backup copy of the data in another database on the same instance to recover from a problem ETL process, you could simply use SELECT...INTO, which will be minimally logged when the target db is in the SIMPLE recovery model. There's no need to bother with partitioning the backup table in that case.

Rathirojini Sangilipandy 241 Reputation points

2021-11-08T15:21:37.92+00:00

@Dan Guzman ,

Yeah you are right! what I have meant here is copying a table from one DB to another DB. or Exporting a data from one DB to another DB for a single table.
Our goal is to reduce the size of existing DB so we want to move old data's in log table to another DB and delete those data from here.

We still need to copy the data along with Partition . so i guess i would need to

1) export or copy data from DB1. table1 to DB2.table1
2) create partitions manually or script the partition function , schema from DB1 to DB2
3) delete records from DB1.Table1

Thanks !
Dan Guzman 9,516 Reputation points

2021-11-08T16:31:28.827+00:00

So this is an archive process in which rows would exist in one table but not the other once the process completes. Is your existing partitioning based in a date? If so, wouldn't the partition source/target boundaries be different?
Rathirojini Sangilipandy 241 Reputation points

2021-11-09T09:17:38.15+00:00

@Dan Guzman , Partition is a new topic to me so i'm not sure what you mean by boundaries.

Yes Partition is based on column 'entrydate' datetime field. Do you mean partition Range as boundaries?

and All the partitions are mapped to PRIMARY filegroup and there's not any separate filegroup for each partition.
Dan Guzman 9,516 Reputation points

2021-11-09T11:23:38.297+00:00
Boundaries are the values listed in the partition function. Each boundary value creates a corresponding partition in partition schemes that use the function. An additional partition is created for values less than the first boundary (with a RANGE RIGHT function) or greater than the last boundary (with a RANGE LEFT function) so tables/indexes partitioned with the scheme will have one more partition than function boundaries. See Table Partitioning Best Practices for more information on how boundaries work and common gotchas.

Your other comment is correct that you can simply create the same function, scheme, and table in the other database to create an identical empty partitioned table. One way to move rows for a given partition from the main table into archive table is with a DELETE...OUTPUT statement. For example:

--monthly partition range right example DECLARE @ArchiveDateBoundary date = '2020-09-01'; DELETE dbo.LogTable OUTPUT deleted.EntryDate, deleted.LogMessage INTO ArchiveDatabase.dbo.LogTableArchive (EntryDate, LogMessage) WHERE EntryDate >= @ArchiveDateBoundary AND EntryDate < DATEADD(month, 1, @ArchiveDateBoundary);

Remember that you'll need to change the partition function and scheme in both databases as you make boundary changes with MERGE and SPLIT, such as in a sliding window.
Rathirojini Sangilipandy 241 Reputation points

2021-11-10T11:04:19.64+00:00

@Dan Guzman ,

Thanks for the details !
I suppose that reason you have suggested DELETE ..OUTPUT statement for import data is because the Data volume is huge ?
Because i was in impression that SQL Export wizard can handle 100M row transfer (am not sure !) or i could also use BCP utility.

Do you think Bulk data export might cause Deadlock in SQL ?
Dan Guzman 9,516 Reputation points

2021-11-10T12:18:01.027+00:00

I suggested DELETE ..OUTPUT because it's a very easy and a transactional all-or-none operation. 5 million rows is not exceedingly large for a single transaction. The T-SQL statement can be scheduled as a SQL Agent job step and added to your existing partition maintenance job if you've automated those tasks. T-SQL, SSIS, and BCP can all handle large volumes of data but you'll need to ensure the transaction log is sized to accommodate the entire operation and, if not, perform the operation in smaller batches.

The Export Data wizard method is convenient for a one-time manual copy but my understanding is you want to repeat the copy regularly and automate it. For automation, you can either create an SSIS package from scratch or use the wizard to create and save the initial package, modify it to parameterize the archive date range, and add a step to delete the archived data. Albeit more complex, SSIS or BCP are options if you are comfortable enough with SSIS package development or shell scripting to calculate the date range and implement batching if needed.

I assume the data being archived is read-only so deadlocking should not be a concern. There may be some blocking of select queries during the delete if the data for the date range being archived is actively being queried by sessions not using a snapshot isolation level. For this reason, archival and partition maintenance are typically scheduled during a maintenance window or time of low activity.
Rathirojini Sangilipandy 241 Reputation points

2021-11-10T14:14:14.627+00:00

@Dan Guzman , Thanks for the Precise Details. I will keep these in mind in my activity.

2 additional answers

Your answer

Rathirojini Sangilipandy 241 Reputation points

2021-11-08T15:21:37.92+00:00

@Dan Guzman ,

Yeah you are right! what I have meant here is copying a table from one DB to another DB. or Exporting a data from one DB to another DB for a single table.
Our goal is to reduce the size of existing DB so we want to move old data's in log table to another DB and delete those data from here.

We still need to copy the data along with Partition . so i guess i would need to

1) export or copy data from DB1. table1 to DB2.table1
2) create partitions manually or script the partition function , schema from DB1 to DB2
3) delete records from DB1.Table1

Thanks !
Dan Guzman 9,516 Reputation points

2021-11-08T16:31:28.827+00:00

So this is an archive process in which rows would exist in one table but not the other once the process completes. Is your existing partitioning based in a date? If so, wouldn't the partition source/target boundaries be different?
Rathirojini Sangilipandy 241 Reputation points

2021-11-09T09:17:38.15+00:00

@Dan Guzman , Partition is a new topic to me so i'm not sure what you mean by boundaries.

Yes Partition is based on column 'entrydate' datetime field. Do you mean partition Range as boundaries?

and All the partitions are mapped to PRIMARY filegroup and there's not any separate filegroup for each partition.
Dan Guzman 9,516 Reputation points

2021-11-09T11:23:38.297+00:00

Boundaries are the values listed in the partition function. Each boundary value creates a corresponding partition in partition schemes that use the function. An additional partition is created for values less than the first boundary (with a RANGE RIGHT function) or greater than the last boundary (with a RANGE LEFT function) so tables/indexes partitioned with the scheme will have one more partition than function boundaries. See Table Partitioning Best Practices for more information on how boundaries work and common gotchas.

Your other comment is correct that you can simply create the same function, scheme, and table in the other database to create an identical empty partitioned table. One way to move rows for a given partition from the main table into archive table is with a DELETE...OUTPUT statement. For example:

--monthly partition range right example DECLARE @ArchiveDateBoundary date = '2020-09-01'; DELETE dbo.LogTable OUTPUT deleted.EntryDate, deleted.LogMessage INTO ArchiveDatabase.dbo.LogTableArchive (EntryDate, LogMessage) WHERE EntryDate >= @ArchiveDateBoundary AND EntryDate < DATEADD(month, 1, @ArchiveDateBoundary);

Remember that you'll need to change the partition function and scheme in both databases as you make boundary changes with MERGE and SPLIT, such as in a sliding window.
Rathirojini Sangilipandy 241 Reputation points

2021-11-10T11:04:19.64+00:00

@Dan Guzman ,

Thanks for the details !
I suppose that reason you have suggested DELETE ..OUTPUT statement for import data is because the Data volume is huge ?
Because i was in impression that SQL Export wizard can handle 100M row transfer (am not sure !) or i could also use BCP utility.

Do you think Bulk data export might cause Deadlock in SQL ?
Dan Guzman 9,516 Reputation points

2021-11-10T12:18:01.027+00:00

I suggested DELETE ..OUTPUT because it's a very easy and a transactional all-or-none operation. 5 million rows is not exceedingly large for a single transaction. The T-SQL statement can be scheduled as a SQL Agent job step and added to your existing partition maintenance job if you've automated those tasks. T-SQL, SSIS, and BCP can all handle large volumes of data but you'll need to ensure the transaction log is sized to accommodate the entire operation and, if not, perform the operation in smaller batches.

The Export Data wizard method is convenient for a one-time manual copy but my understanding is you want to repeat the copy regularly and automate it. For automation, you can either create an SSIS package from scratch or use the wizard to create and save the initial package, modify it to parameterize the archive date range, and add a step to delete the archived data. Albeit more complex, SSIS or BCP are options if you are comfortable enough with SSIS package development or shell scripting to calculate the date range and implement batching if needed.

I assume the data being archived is read-only so deadlocking should not be a concern. There may be some blocking of select queries during the delete if the data for the date range being archived is actively being queried by sessions not using a snapshot isolation level. For this reason, archival and partition maintenance are typically scheduled during a maintenance window or time of low activity.
Rathirojini Sangilipandy 241 Reputation points

2021-11-10T14:14:14.627+00:00

@Dan Guzman , Thanks for the Precise Details. I will keep these in mind in my activity.

Answer 1

Seeya Xi-MSFT 16,676

Hi anonymous user ,

Welcome to Microsoft Q&A!
I agree with your guess.
There are also some other methods of copying tables that I put here.
https://www.sqlshack.com/how-to-copy-tables-from-one-database-to-another-in-sql-server/

Best regards,
Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Rathirojini Sangilipandy 241 Reputation points

2021-11-09T09:23:29.113+00:00

@Seeya Xi-MSFT , Thanks for the link. Yes these are the methods i will be using to copying tables but it doesn't explain anything about partition functions and Schemes.

So i would be first generate script from old DB for partition function and scheme then execute it on new DB
Then i will be generate script for create table with partition scheme and import the data

Hope this way i will have table in my new DB along with partition

Thanks

Answer 2

Tom Phillips 17,786

All "backups" include everything in the database. There is nothing special about partitioned tables.

Rathirojini Sangilipandy 241 Reputation points

2021-11-08T15:23:05.473+00:00

@Tom Phillips ,

It's not entire DB backup. i just need to copy one table from one DB to another DB and looking for option to export partition as well .

Share via

Backup SQL table along with Partitions

2 additional answers

Your answer