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

Surendra NathTechnology 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.

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
Easwaran ParamasivamAuthor Commented:
cool. I would like to automate the copying the table data on daily basis. How to achieve this? Please suggest.
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.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Scott PletcherSenior DBACommented:
Snapshot Replication would likely be the easiest way in your case.
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.
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
Easwaran ParamasivamAuthor Commented:
Thanks.
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.