Creating a back up with date attached to it


I need to create a backup table dynamically , each time the SQL job is ran, let say every wed, I need to back up this table to that date as prefix example:
Select * into Person_getdate() ...
from person..

How can I do this every time a SQL job runs so it created new back up with that date attached to it?

DECLARE @sql nvarchar(4000)=N'SELECT * INTO Person_' + CONVERT(nchar(8), GETDATE(), 112) + N' FROM person;'
1 Like

Thanks Robert!

another thing is I need to cerate a SQL job (Query) to update a table here is the criteria:

column 1 = counter
column 2= Print
if counter is >=2 then print_ind = 'N'

so I am doing this:

update payroll
set print_ind ='N'
where counter >=2

is this good enough or needs more statement?

That looks good to me.

To create a backup with a date attached to it, you can use the command line or a scripting language to automate the process. Here's an example of how you might do this using the command line on a Linux or Mac system:


# set the date variable
DATE=$(date +"%Y-%m-%d")

# set the backup file name

# create the backup
tar -czf $BACKUP_FILE /path/to/backup/folder

The above script will create a backup file named "backup-YYYY-MM-DD.tar.gz" in the current directory, where YYYY-MM-DD is the current date. The backup file will contain the contents of the "/path/to/backup/folder" directory.

Hello @findtricks and welcome!

Take a look at the original post. This is to create an online "Backup Table". External scripts aren't required here. If it were for an actual backup, a bit of dynamic SQL would do the trick and compression can be built in.