Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Taking simple table backup
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

79 Posts

Posted - 08/12/2014 :  18:06:54  Show Profile  Reply with Quote
I have a table say my_table with few columns such as below
ID, col1,col2,col3,col4 - ID is identity and pk
I need to back this table up on regular basis.Once in 3 months

So i created a backup table called my_table_bak with columns
ID, col1,col2,col3,col4, Timestamp- ID is not identity. Timestamp is defaulted to getdate()

if i need to reload the table my_table with recent backup i thought I would use the Timestamp column. But in case the back ups were taken very quickly say almost immediately after the first and not after 3 months,it would become difficult for me to figure the recent set of data. What is the cleanest way to deal with this problem. Is it a good idea to insert a all NULL row to the table to differentiate between the sets.

Also the data in my_table_bak is all jumbled up because it had no primary key. Should i put in pk,identity


Constraint Violating Yak Guru

352 Posts

Posted - 08/13/2014 :  09:16:32  Show Profile  Reply with Quote
Inserting an "all NULL" will not do as SQL Server does not save data records as you might think.

What I would suggest is have a stored procedure where you define the datetime to input into Timestamp as a variable for each backup. That way all records would have the same datetime.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000