Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have 2 database environments a "Production" Database and a "Reporting" Database located on different physical windows server 2003 boxes. The reporting Db is derived off the Production database and contains some modified tables schema's and additional "Pre Cooked" Data tables which would be too costly to otherwise process on the fly.

What I had originally created was a database script (MS SQL) on our Reporting Server to grab the Live Production database at 1AM and restore it in our Reporting environment by running all the scripts to get it up and going such as the tables restores, and modification of the data. The problem with this approach is that I quickly began to realize a) how long this approach takes (there is no option of putting a 'modifieddate' on any production records) and b) how a junior DB developer (me) should probably not dive into this stuff yet because he's more of a programmer :).

Would it be part of the 'best practice methodology' to create a windows service which basically runs the restore at 1AM and sends the email if the process failed? I'm not sure if writing a c# component is going to allow me the level of granularity that MS SQL Scripts allow. So by using a programming language to accomplish this task - is this nuts and should this all be done in the database?

How would you recommend I run the syncing of the two environments?

Let me know what you think!

share|improve this question

migrated from stackoverflow.com Mar 16 '13 at 2:02

This question came from our site for professional and enthusiast programmers.

3  
You don't need to write a Windows service for this, just set up a Scheduled Task, or use SQL Server's "SQL Agent" functionality. –  Dai Mar 15 '13 at 19:42
1  
I totally agree with @Dai. You don't have to reinvent the wheel. Instead go with SQL Server Agent or Windows Schedule Task to do the job. Plus if you create an application then you might need to adjust it for other factors in future like login, database connection string etc, which will add overhead to you or someone else. –  JackLock Mar 15 '13 at 19:52
    
The Reporting Database is on a different server than the Production Database. What I did previously was Restore the production database on the reporting server (Created a network share in the mssql script). This means its very hard for the 2 servers to "talk" with each other –  user1438150 Mar 15 '13 at 20:03
    
If both machines are on same domain then you can still use one task combining backup and restore steps. But if both machines are not on same domain then I think it will be easy to create two separate tasks one for backup and one for restore. Backup task will create backup at some network location + send email and restore task will look for that backup file, and if it exists then it will restore database + send email –  JackLock Mar 15 '13 at 21:02
add comment

1 Answer 1

For those kind of tasks, you should use the tools you are most comfortable with.

A DB would of course run a scheduled database script or use SSIS. A C# dev creates a small C# tool and a sysadmin creates a PowerShell script.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.