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

Easwaran Paramasivam
Easwaran Paramasivam used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
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.
cool. I would like to automate the copying the table data on daily basis. How to achieve this? Please suggest.

Commented:
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.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Snapshot Replication would likely be the easiest way in your case.
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.

Commented:
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
Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial