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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
LD16Author Commented:
Thank you, but for datestamp should I declare a variable?
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.