Remote sql back-up

[Dukester0122]

I'm installing a remote sql back-up for disaster recovery, we have vpn connection with this remote site. I want the back-up to run everyday except sat & sun. Can I just create a Job for the back-up or do I need to use a special procedure or query to do this?

 

[JayKusch]

You should be able to adjust the schedule of the job to run whenever you need it to. Double click on the job, choose the schedule tab and then hit the Change button for the "recurring" radio button. In there you can set what days of the week it should run as well as the start time.

Thanks

J. Kusch

 

[Dukester0122]

But where do you specify which server this job goes to. Remember this is a new server which is in a remote site, I can access this server from here so I should be able to point the back-up to it but I can't find which tab to change the server.

 

[nigelrivett]

When I do this I run it locally but use sp_executesql to excute on the remote server.

exec ('exec ' + @remsvr + '.master.dbo.sp_executesql N''backup database .....''')

or just
exec remsvr.master.dbo.sp_executesql N'backup database .....')

But you can try making the server a master (right click agent and multi-aerver..) then use the target multiple servers option on the first tab for the job properties.
Never tried it.

But for backups you would norally schedule them on the server to be backed up
In your case it sounds like you want to backup the database then copy the backup file to the remote site to restore.
You can do this on the local site by xp_cmdshell 'copy...'
Also have a look at this which does a copy of the latest backup file and restore

http://www.nigelrivett.net/s_TestRestore.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.