SQL: backup sql table with datestamp variable

Hello experts,

I was wondering how to backup a sql table by reporting date time stamp automatically
Date stamp format YYYYMMDDHHMMSS



SELECT *
INTO TABLE1_ YYYYMMDDHHMMSS & '_before_migration'
FROM TABLE1;




The result should create TABLE1_20170711176349_before_migration with information of table 1.

If you have questions, please contact me.

Thank you very much for your help.
LVL 1
LD16Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
DECLARE @backup_table_name varchar(100)
DECLARE @backup_table_name_template varchar(100)
DECLARE @datetime_char varchar(30)
DECLARE @source_table_name varchar(100)
DECLARE @sql varchar(8000)
DECLARE @table_schema varchar(100)
DECLARE @table_suffix varchar(100)

--sample values for testing
IF @table_schema IS NULL
    SET @table_schema = 'dbo'
SET @source_table_name = 'TABLE1'
SET @table_suffix = '_before_migration'
SET @backup_table_name_template = '[$schema$].[$table$_$datetime$$suffix$]'

SET @datetime_char = CONVERT(varchar(8), GETDATE(), 112) + REPLACE(CONVERT(varchar(30), GETDATE(), 8), ':', '')
SET @backup_table_name = REPLACE(REPLACE(REPLACE(REPLACE(@backup_table_name_template,
    '$schema$', @table_schema),
    '$table$', @source_table_name),
    '$datetime$', @datetime_char),
    '$suffix$', @table_suffix)

--SELECT @backup_table_name AS backup_table_name

SET @sql = '
SELECT TOP (0) *
INTO ' + @backup_table_name + '
FROM [' + @table_schema + '].[' + @source_table_name + '] WITH (NOLOCK);'
--PRINT @sql
EXEC(@sql)

/* add code here to create clustered index -- it's much more efficient to create the clus index before loading the table */

SET @sql = '
INSERT INTO ' + @backup_table_name + ' WITH (TABLOCK) /*DO NOT REMOVE TABLOCK!!, it is required for minimal logging*/
SELECT *
FROM [' + @table_schema + '].[' + @source_table_name + '];'
--PRINT @sql
EXEC(@sql)
0
 
Pawan KumarDatabase ExpertCommented:
SELECT * INTO ..is best and easiest way to take backup of a table..

SELECT *
INTO TABLE1_ YYYYMMDDHHMMSS & '_before_migration'
FROM TABLE1;

Open in new window


You can also use above method to move backup table into another DB.

other methods can be Generate Script :  Right click on the database -> Tasks -> Generate Scripts -> Advanced -> Enable Script and Data -> Choose table -> Click Ok.

You can also use Import/Export Wizard or SSIS package.

Other options could be BCP OUT using master..xp_cmdshell.
0
 
Pawan KumarDatabase ExpertCommented:
edited my last comment.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LD16Author Commented:
Thank you, but for datestamp should I declare a variable?
0
 
Pawan KumarDatabase ExpertCommented:
Yes create a Datetime Variable.
0
 
ITSysTechSenior Systems AdministratorCommented:
I prefer using the SQL Server Agent and then use this script. You can alter it to your database name.
Here is what the output looks like. mydatabase_Full_20171008_234500.bak so first is the year 2017 next is month 10 then day 08. Then the time 23:45 with 00 seconds. This method will make your life easier for recoveries.



declare @Path varchar(500) , @DBName varchar(128)

select @DBName = 'mydatabase'
select @Path = 'B:\SQLBackup\mydatabase\'

declare @FileName varchar(4000)

select @FileName = @Path + @DBName + '_Full_'
                              + convert(varchar(8),getdate(),112) + '_'
                              + replace(convert(varchar(8),getdate(),108),':','')
                              + '.bak'
                                          
backup database @DBName to disk = @FileName
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If you have some time to invest and the table size is huge I'd recommend Table Partitioning, as that would save you all the I/O of having to perform INSERTs and DELETEs to pull this off.
1
 
Pawan KumarDatabase ExpertCommented:
Full code for you ...
DECLARE @SQL AS VARCHAR(MAX) = '
SELECT *
INTO TABLE1_' + FORMAT(GETDATE() ,'yyyyMMddHHmmss') +  '_before_migration' + 
' FROM tableName ' 

EXEC(@SQL)

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
And the table will be kept in the same database?
If affirmative, what you going to do with the table? I guess the application (if any) wouldn't know about the existence of this new table, right?
0
 
Scott PletcherSenior DBACommented:
You should not create the table using SELECT ... INTO because this will leave locks on system objects and other internal db structures for the entire time of the insert (how severe that locking/blocking is depends on your specific SQL version, but it's not good on any of them).

Instead, first create just the table structure, then insert data into it, like so:

DECLARE @backup_table_name varchar(100)
DECLARE @datetime_char varchar(30)
DECLARE @sql varchar(8000)

SET @datetime_char = CONVERT(varchar(8), GETDATE(), 112) + REPLACE(CONVERT(varchar(30), GETDATE(), 8), ':', '')
SET @backup_table_name = 'TABLE1_' + @datetime_char + '_before_migration'

SET @sql = '
SELECT TOP (0) *
INTO dbo.' + @backup_table_name + '
FROM dbo.TABLE1 WITH (NOLOCK);'
--PRINT @sql
EXEC(@sql)

SET @sql = '
INSERT INTO dbo.' + @backup_table_name + ' WITH (TABLOCK)
SELECT *
FROM dbo.TABLE1;'
--PRINT @sql
EXEC(@sql)
0
 
LD16Author Commented:
@Scott : thank you very much for this useful advice.

Is there a way to add in the query the following variables instead of reporting manually @source_table_name, @schema @end_string (which is the end of the string that will be placed at the end of backup_table_name?

Thank you again for your help.
0
 
Scott PletcherSenior DBACommented:
Sure.  I prefer a template approach, because I think it makes code development and maintenance easier, so I'll demonstrate that too with the changes you requested.

DECLARE @backup_table_name varchar(100)
DECLARE @backup_table_name_template varchar(100)
DECLARE @datetime_char varchar(30)
DECLARE @source_table_name varchar(100)
DECLARE @sql varchar(8000)
DECLARE @table_schema varchar(100)
DECLARE @table_suffix varchar(100)

--sample values for testing
IF @table_schema IS NULL
    SET @table_schema = 'dbo'
SET @source_table_name = 'TABLE1'
SET @table_suffix = '_before_migration'
SET @backup_table_name_template = '[$schema$].[$table$_$datetime$$suffix$]'

SET @datetime_char = CONVERT(varchar(8), GETDATE(), 112) + REPLACE(CONVERT(varchar(30), GETDATE(), 8), ':', '')
SET @backup_table_name = REPLACE(REPLACE(REPLACE(REPLACE(@backup_table_name_template,
    '$schema$', @table_schema),
    '$table$', @source_table_name),
    '$datetime$', @datetime_char),
    '$suffix$', @table_suffix)

--SELECT @backup_table_name AS backup_table_name

SET @sql = '
SELECT TOP (0) *
INTO ' + @backup_table_name + '
FROM [' + @table_schema + '].[' + @source_table_name + '] WITH (NOLOCK);'
PRINT @sql
EXEC(@sql)

/* add code here to create clustered index -- it's much more efficient to create the clus index before loading the table */

SET @sql = '
INSERT INTO ' + @backup_table_name + ' WITH (TABLOCK) /*DO NOT REMOVE TABLOCK!!:
    it's critical to allow minimal logging for efficiency*/
SELECT *
FROM [' + @table_schema + '].[' + @source_table_name + '];'
PRINT @sql
EXEC(@sql)
0
 
LD16Author Commented:
Thank you very much for this query.

I tested and I have a little error message:

Could you please help me to revise it?

BACKUP_TABLE.png
Thank you again for your help.
0
 
Pawan KumarDatabase ExpertCommented:
Please use this , that has syntax errors

DECLARE @backup_table_name varchar(100)
DECLARE @backup_table_name_template varchar(100)
DECLARE @datetime_char varchar(30)
DECLARE @source_table_name varchar(100)
DECLARE @sql varchar(8000)
DECLARE @table_schema varchar(100)
DECLARE @table_suffix varchar(100)

--sample values for testing
IF @table_schema IS NULL
    SET @table_schema = 'dbo'
SET @source_table_name = 'TABLE1'
SET @table_suffix = '_before_migration'
SET @backup_table_name_template = '[$schema$].[$table$_$datetime$$suffix$]'

SET @datetime_char = CONVERT(varchar(8), GETDATE(), 112) + REPLACE(CONVERT(varchar(30), GETDATE(), 8), ':', '')
SET @backup_table_name = REPLACE(REPLACE(REPLACE(REPLACE(@backup_table_name_template,
    '$schema$', @table_schema),
    '$table$', @source_table_name),
    '$datetime$', @datetime_char),
    '$suffix$', @table_suffix)

SET @sql = '
SELECT TOP (0) *
INTO ' + @backup_table_name + '
FROM [' + @table_schema + '].[' + @source_table_name + '] WITH (NOLOCK);'
--PRINT @sql
EXEC(@sql)

SET @sql = '
INSERT INTO ' + @backup_table_name + ' WITH (TABLOCK) ' + '
SELECT *
FROM [' + @table_schema + '].[' + @source_table_name + '];'
--PRINT @sql
EXEC(@sql)

Open in new window

0
 
LD16Author Commented:
It works thank you again for your help!!!
0
 
Scott PletcherSenior DBACommented:
You're welcome.  I don't see that Pawan contributed equal value here (most experts wouldn't try to claim points simply for fixing minor syntax errors in someone else's code), but hopefully he'll be able to do the same for you in the future.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.