Solved

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

Posted on 2013-12-21
7
269 Views
Last Modified: 2013-12-27
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.
0
Comment
Question by:Easwaran Paramasivam
  • 3
  • 3
7 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 400 total points
Comment Utility
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
 
LVL 16

Author Comment

by:Easwaran Paramasivam
Comment Utility
cool. I would like to automate the copying the table data on daily basis. How to achieve this? Please suggest.
0
 
LVL 16

Expert Comment

by:Surendra Nath
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
Comment Utility
Snapshot Replication would likely be the easiest way in your case.
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
Comment Utility
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
 
LVL 16

Expert Comment

by:Surendra Nath
Comment Utility
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
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
Comment Utility
Thanks.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now