I am looking for the simplest method of copying specific tables (or if necessary, the whole database) from a remote server to a local one at a scheduled interval.
The remote server contains our live data and the local ones we use for testing and as a backup.
Both servers are running SQL 2008 express.
I have seen many articles using detach/attach, import/export wizard, etc., but as I said I would like to be able to script it, schedule it, and do a simple copy without affecting what's happening on the remote/live server.
If you want to copy couple of tables, you can use Linked server (4 part name) or sqlcmd/bcp options without effecting your live server.
If you want to do the entire database then it is better to use the backup/restore option which will not effect your live server.