Have backup of some set of tables and put in another DB daily

Hi Experts,

   In my database I've set of configuration related tables. I would like to take back up of those tables on daily basis and put in another database. I would like to automate this process.

 How to achieve this? Please do assist.
LVL 16
Easwaran ParamasivamAsked:
Who is Participating?
 
Surendra NathConnect With a Mentor Technology LeadCommented:
Hello

There are two solutions to this problem

procedure 1:
1. create a linked server between the two sql servers where the databases are prenset
2. write a stored procedure in any one of the databases, to copy the tables data

procedure 2:
1. Create a filegroup and add re-create all these tables on that file group.
2. now create a backup job only for that file group.
3. while restoring in another database, restore it using the filegroup again.
0
 
Easwaran ParamasivamAuthor Commented:
cool. I would like to automate the copying the table data on daily basis. How to achieve this? Please suggest.
0
 
Surendra NathTechnology LeadCommented:
which procedure you want to choose

for procedure 1, you can simply configure a sql server agent job and schedule it for at a time.
for procedure 2, you can create backup SSIS DTS package and then configure the package in the server agent join and schdule it for at a time.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Scott PletcherConnect With a Mentor Senior DBACommented:
Snapshot Replication would likely be the easiest way in your case.
0
 
Easwaran ParamasivamAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 400 points for Neo_jarvis's comment #a39733506
Assisted answer: 0 points for EaswaranP's comment #a39733526
Assisted answer: 100 points for ScottPletcher's comment #a39736908

for the following reason:

Thanks.
0
 
Surendra NathTechnology LeadCommented:
If the user closes this question and it is removed from the database, it may end up as non-informational for other users....
Please think on these lines, tommorow some guy may want to see this information and learn from it....

SO, I suggest the question to be kept and the answers accepted properly
0
 
Easwaran ParamasivamAuthor Commented:
Thanks.
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.