calling all gurus! - simple synchronisation and backups - best plan needed!

Hi,
I'm from a unix background but have worked with SQL server for months in the
past.
I've recently have been asked to implement simple backup and synchronisation
routines for a database.
Basically, there are two servers both running SQL Server 2000.
Every night the data on server 1 needs to be backed up to a file which is
then archived for backup purposes.
An exact copy of the database also needs to be copied onto server 2 which
will overwrite the copy already on there.
This means that server 2 is ready to be used as a hot-swap backup if needed.
Please note - the built-in synchronisation of SQL server is not wanted. The
synchronisation has to happen once during the night and not again until the
following night.
This is because the client runs a few large DTS packages during the day and
can not easily see if the various transactions have completed or not. The
database is between 10/20GB in size and contains masses of numbers relating
to telecoms data.
The transactions are carried out by a ton of spaghetti code written into thr
DTS packages - these involve writing to files and screen after screen of
convoluted transactions.
In the event of a problem all the client wants to be able to do is to revert
to how the data was at 9.00am and rerun his DTS packages. The situation
can be reverted by restoring the backup or by switching to the second
server in the event o something serious happening to the first server.
So - to emphasise the point - I know DTS packages can be made as single
transactions and logs can show if transactions have completed etc but the
client simply wants the data synchronised once during the night and that's
it.
Now I have run the process manually - i.e. backed up the database to a file,
copied the file to the server 2, then restored this file over the old
database on server 2. The file can obviously be copied to an archive
location.
What I am basically asking is where is the best place to script this?
Could I write a batch file on server 2 which could run OSQL commands on
server 1 to backup the DB, copy the file, copy to an archive, then use OSQL
to restore into server 2.
Or should I use scheduled jobs built-in to SQL server. Can these reference
and carry out commands on the other DB? Also, can I run dos commands to
copy files, make dirs etc?
What about permissions?
Most importantly, how do I get feedback from each step of the sequence? Can
results be written to an event log? I know that sending them out via email
is problematic but could this be done?
The daily result email should contain lines like:
08/10/2006 01:01:01 Dumping database...
08/10/2006 02:02:02 Copying file...
08/10/2006 03:03:03 Restoring database on to server 2...
08/10/2006 04:04:04 Finished.
Obviously any error messages from failed steps would appear in the email.
Any pointers towards a neat way of scripting these actions would be
appreciated.
Kevin
How about a SQL Server agent job. This can have many jobsteps and each jobstep can redirect output
to an output file (option to append/overwrite). so you can overwrite for first jobstep and append
the rest. Final jobstep can be to send the email (xp_smtp_sendmail). Assuming job owner has sysadmin
privileges, the OS type jobsteps will execute as Agent service account. So, you can fir instance use
OSQL.EXE from such a job step to login to the "other" SQL Server instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"kevin bailey" <kbailey@.freewayprojects.com> wrote in message
news:egdfe7$h5l$1$8300dec7@.news.demon.co.uk...
> Hi,
> I'm from a unix background but have worked with SQL server for months in the
> past.
> I've recently have been asked to implement simple backup and synchronisation
> routines for a database.
> Basically, there are two servers both running SQL Server 2000.
> Every night the data on server 1 needs to be backed up to a file which is
> then archived for backup purposes.
> An exact copy of the database also needs to be copied onto server 2 which
> will overwrite the copy already on there.
> This means that server 2 is ready to be used as a hot-swap backup if needed.
> Please note - the built-in synchronisation of SQL server is not wanted. The
> synchronisation has to happen once during the night and not again until the
> following night.
> This is because the client runs a few large DTS packages during the day and
> can not easily see if the various transactions have completed or not. The
> database is between 10/20GB in size and contains masses of numbers relating
> to telecoms data.
> The transactions are carried out by a ton of spaghetti code written into thr
> DTS packages - these involve writing to files and screen after screen of
> convoluted transactions.
> In the event of a problem all the client wants to be able to do is to revert
> to how the data was at 9.00am and rerun his DTS packages. The situation
> can be reverted by restoring the backup or by switching to the second
> server in the event o something serious happening to the first server.
> So - to emphasise the point - I know DTS packages can be made as single
> transactions and logs can show if transactions have completed etc but the
> client simply wants the data synchronised once during the night and that's
> it.
> Now I have run the process manually - i.e. backed up the database to a file,
> copied the file to the server 2, then restored this file over the old
> database on server 2. The file can obviously be copied to an archive
> location.
>
> What I am basically asking is where is the best place to script this?
> Could I write a batch file on server 2 which could run OSQL commands on
> server 1 to backup the DB, copy the file, copy to an archive, then use OSQL
> to restore into server 2.
> Or should I use scheduled jobs built-in to SQL server. Can these reference
> and carry out commands on the other DB? Also, can I run dos commands to
> copy files, make dirs etc?
> What about permissions?
> Most importantly, how do I get feedback from each step of the sequence? Can
> results be written to an event log? I know that sending them out via email
> is problematic but could this be done?
> The daily result email should contain lines like:
> 08/10/2006 01:01:01 Dumping database...
> 08/10/2006 02:02:02 Copying file...
> 08/10/2006 03:03:03 Restoring database on to server 2...
> 08/10/2006 04:04:04 Finished.
>
> Obviously any error messages from failed steps would appear in the email.
> Any pointers towards a neat way of scripting these actions would be
> appreciated.
> Kevin
>
|||Tibor Karaszi wrote:

> How about a SQL Server agent job. This can have many jobsteps and each
> jobstep can redirect output to an output file (option to
> append/overwrite). so you can overwrite for first jobstep and append the
> rest.
Sounds good.

> Final jobstep can be to send the email (xp_smtp_sendmail).
Sounds even better - didn't know that this Sp existed!

> Assuming
> job owner has sysadmin privileges, the OS type jobsteps will execute as
> Agent service account. So, you can fir instance use OSQL.EXE from such a
> job step to login to the "other" SQL Server instance.
>
Thanks for that.
|||Tibor Karaszi wrote:

> How about a SQL Server agent job. This can have many jobsteps and each
> jobstep can redirect output to an output file (option to
> append/overwrite). so you can overwrite for first jobstep and append the
> rest. Final jobstep can be to send the email (xp_smtp_sendmail)
Hmmm... can't find that xp
Presume you mean xp_sendmail.
That needs a mail profile to be set up which the server admin can't get to
for now.
For a minute I thought there was an inbuilt smtp engine. If there is a
simple way for SQL server to send emails it would be useful. But currently
to only bodgearound is to use something like an external app and run it
from the command line.

> . Assuming
> job owner has sysadmin privileges, the OS type jobsteps will execute as
> Agent service account. So, you can fir instance use OSQL.EXE from such a
> job step to login to the "other" SQL Server instance.
>
|||You find xp_smtp_sendmail at www.sqldev.net. It is so commonly used nowadays, so I sometimes take if
for granted that people know about it already... ;-)
2005 has built-in SMTP support, btw...
Oh, I forgot. Perhaps obvious, but you can attach the output file created by earlier jobsteps in the
final e-mailing jobstep (see the parameters to xp_smtp_sendmail). This way you have your email
including details of what each jobstep did, and possible error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"kevin bailey" <kbailey@.freewayprojects.com> wrote in message
news:egdofr$l9j$1$8302bc10@.news.demon.co.uk...
> Tibor Karaszi wrote:
>
> Hmmm... can't find that xp
> Presume you mean xp_sendmail.
> That needs a mail profile to be set up which the server admin can't get to
> for now.
> For a minute I thought there was an inbuilt smtp engine. If there is a
> simple way for SQL server to send emails it would be useful. But currently
> to only bodgearound is to use something like an external app and run it
> from the command line.
>
>
|||Tibor Karaszi wrote:

> You find xp_smtp_sendmail at www.sqldev.net. It is so commonly used
> nowadays, so I sometimes take if for granted that people know about it
> already... ;-)
Thanks for the link.

> 2005 has built-in SMTP support, btw...
> Oh, I forgot. Perhaps obvious, but you can attach the output file created
> by earlier jobsteps in the final e-mailing jobstep (see the parameters to
> xp_smtp_sendmail). This way you have your email including details of what
> each jobstep did, and possible error messages.
>
Will try this out.
Kevin