Automate daily table backup

Question

Automate daily table backup

Diyas

SSCarpal Tunnel

Points: 4851
More actions
July 25, 2018 at 4:12 pm

#358859

Hi,
i have task is assigned to take the table backup daily wise.
below is the format
tablename_yymmdd
please suggest

Viewing 15 posts - 1 through 15 (of 15 total)

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

Joe Torre SSChampion Points: 10248 More actions · Answer 1

Are you asking about exporting the data in tables to text files? It's easy in SSIS or from the command line. Can you describe more what you are trying to do?

Diyas SSCarpal Tunnel Points: 4851 More actions · Answer 2

Diyas

SSCarpal Tunnel

Points: 4851

July 25, 2018 at 4:28 pm

#1999010

single table backup.

Joe Torre SSChampion Points: 10248 More actions · Answer 3

Joe Torre

SSChampion

Points: 10248

July 25, 2018 at 4:33 pm

#1999011

Backup to what format?

Joe Torre SSChampion Points: 10248 More actions · Answer 4

If it's a text file try the sqlcmd command line utility.

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 5

Diyas - Wednesday, July 25, 2018 4:12 PM
Hi,
i have task is assigned to take the table backup daily wise.
below is the format
tablename_yymmdd
please suggest

Do you mean to another table that's named tablename_yymmdd?
If so you can do it with some dynamic SQL.
DECLARE @sSql as nvarchar(MAX) SET @sSql = 'SELECT * INTO dbo.tablename_' + CONVERT(nvarchar(6),GETDATE(),12) + ' FROM dbo.tablename' EXEC (@sSql)
Just paste this into a SQL agent job and schedule it to run daily.

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

Diyas - Wednesday, July 25, 2018 4:12 PM
Hi,
i have task is assigned to take the table backup daily wise.
below is the format
tablename_yymmdd
please suggest

We need some clarification... are you being required to backup to files on disk or are you just required to make a copy of the table?

If the later, then use a bit of dynamic SQL to do a "SELECT INTO" from the original table to a table with the calculated name. I also suggest that you ask the people giving you this requirement how long you have to keep the backups for. If you can post more accurate requirements, we'll be able to post more accurate answers.

--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)

sgmunson SSC Guru Points: 110640 More actions · Answer 7

Jonathan AC Roberts - Wednesday, July 25, 2018 5:33 PM
Diyas - Wednesday, July 25, 2018 4:12 PM
Hi,
i have task is assigned to take the table backup daily wise.
below is the format
tablename_yymmdd
please suggest
Do you mean to another table that's named tablename_yymmdd?
If so you can do it with some dynamic SQL.
DECLARE @sSql as nvarchar(MAX) SET @sSql = 'SELECT * INTO dbo.tablename_' + CONVERT(nvarchar(6),GETDATE(),12) + ' FROM dbo.tablename' EXEC (@sSql)
Just paste this into a SQL agent job and schedule it to run daily.

This fails to reproduce any indexes, constraints or keys., and as we really don't have sufficiently specific requirements, I'm not sure I'd accept such as a "backup", simply because it's missing those elements. In theory, the purpose of a "backup" is to allow for a restore. Such a backup as you propose would NOT give you that ability in it's entirety.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004367 More actions · Answer 8

sgmunson - Thursday, July 26, 2018 6:33 AM
Jonathan AC Roberts - Wednesday, July 25, 2018 5:33 PM
Diyas - Wednesday, July 25, 2018 4:12 PM
Hi,
i have task is assigned to take the table backup daily wise.
below is the format
tablename_yymmdd
please suggest
Do you mean to another table that's named tablename_yymmdd?
If so you can do it with some dynamic SQL.
DECLARE @sSql as nvarchar(MAX) SET @sSql = 'SELECT * INTO dbo.tablename_' + CONVERT(nvarchar(6),GETDATE(),12) + ' FROM dbo.tablename' EXEC (@sSql)
Just paste this into a SQL agent job and schedule it to run daily.
This fails to reproduce any indexes, constraints or keys., and as we really don't have sufficiently specific requirements, I'm not sure I'd accept such as a "backup", simply because it's missing those elements. In theory, the purpose of a "backup" is to allow for a restore. Such a backup as you propose would NOT give you that ability in it's entirety.

Totally agreed but I will say that "normally" such "table level backups" aren't so concerned about index, FKs, constraints, etc, etc. Normally (and that word is only used in the context of my experiences with such requests) people that ask for such things are only concerned with the data such tables contain.

--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)

andycadley SSCertifiable Points: 5296 More actions · Answer 9

Indeed. And how you solve the problem if you do care might depend on if you have a single table you need to beck up, or a whole raft of different ones.

sgmunson SSC Guru Points: 110640 More actions · Answer 10

Jeff Moden - Thursday, July 26, 2018 6:43 AM
sgmunson - Thursday, July 26, 2018 6:33 AM
Jonathan AC Roberts - Wednesday, July 25, 2018 5:33 PM
Diyas - Wednesday, July 25, 2018 4:12 PM
Hi,
i have task is assigned to take the table backup daily wise.
below is the format
tablename_yymmdd
please suggest
Do you mean to another table that's named tablename_yymmdd?
If so you can do it with some dynamic SQL.
DECLARE @sSql as nvarchar(MAX) SET @sSql = 'SELECT * INTO dbo.tablename_' + CONVERT(nvarchar(6),GETDATE(),12) + ' FROM dbo.tablename' EXEC (@sSql)
Just paste this into a SQL agent job and schedule it to run daily.
This fails to reproduce any indexes, constraints or keys., and as we really don't have sufficiently specific requirements, I'm not sure I'd accept such as a "backup", simply because it's missing those elements. In theory, the purpose of a "backup" is to allow for a restore. Such a backup as you propose would NOT give you that ability in it's entirety.
Totally agreed but I will say that "normally" such "table level backups" aren't so concerned about index, FKs, constraints, etc, etc. Normally (and that word is only used in the context of my experiences with such requests) people that ask for such things are only concerned with the data such tables contain.

Yep. Have to agree... although I will say that asking for a single table backup reflects a lack of knowledge at a level sufficient to justify questioning them as to WHY they want it, and generally saying no unless they have a particularly compelling case.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004367 More actions · Answer 11

andycadley - Thursday, July 26, 2018 6:48 AM
Indeed. And how you solve the problem if you do care might depend on if you have a single table you need to beck up, or a whole raft of different ones.

Again, totally agreed. In fact, it may be that the backup of a "single" table is, in fact, the wrong way to go. An "Audit Table" may be more effective, for example.

--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)

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 12

One immediate concern I would raise with whoever has asked you to back up a table instead of the database. How do you restore it? You need a plan for this as well as a plan for the backup process, regardless of which of the pieces of advice that you've been offered so far you take advantage of.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

andycadley SSCertifiable Points: 5296 More actions · Answer 13

Jeff Moden - Thursday, July 26, 2018 6:54 AM
andycadley - Thursday, July 26, 2018 6:48 AM
Indeed. And how you solve the problem if you do care might depend on if you have a single table you need to beck up, or a whole raft of different ones.
Again, totally agreed. In fact, it may be that the backup of a "single" table is, in fact, the wrong way to go. An "Audit Table" may be more effective, for example.

Or Temporal Tables, if the data doesn't actually change that much.

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 14

andycadley - Friday, July 27, 2018 12:56 PM
Jeff Moden - Thursday, July 26, 2018 6:54 AM
andycadley - Thursday, July 26, 2018 6:48 AM
Indeed. And how you solve the problem if you do care might depend on if you have a single table you need to beck up, or a whole raft of different ones.
Again, totally agreed. In fact, it may be that the backup of a "single" table is, in fact, the wrong way to go. An "Audit Table" may be more effective, for example.
Or Temporal Tables, if the data doesn't actually change that much.

It's common practice in daily data warehouse ETL loads to take a copy of staging tables with the date as an extension to the table name so that any problems between loads can be analysed with the before and after image.