Can someone tell me the best way to automate a daily scheduled remote backup of our SQL Server 2005 database? I would like to backup the database to our Windows 2003 Server, or my local PC running Vista Business.
Can anyone recommend any software?
I noticed that sql server Management Studio has a 'copy database' wizard. I have Express edition installed locally on a Windows Vista PC, but it won't work with Express edition. Does anyone know if the copy database wizard works with SQL Server 2005 Developer edition, and whether you can schedule this somehow to run daily?
One limitation I have is that the remote database is in a shared hosting environment. The host provider will not grant sysadmin access to customers for their databases, but have told me that the user I log in as "does have enough privileges to remotely backup sql databases."
I have tried using Red Gate SQL Backup (http://www.red-gate.com/sqlbackup
) but this requires sysadmin access.
I have tried using 'Simple SQL Copy' (http://projects.c3o.com/files/3/plugins/entry11.aspx
), but I cannot get this to work - it assumes that the tables in the source database are already in the destination database, and doesn't create them when it finds they are missing.
Another frustration is using the import/export wizard in SQL Server Management Studio and not being able to export identity keys on any of the 200 or so database tables. I would like to be able to make an exact duplicate including identity keys, nulls.
The ability to be able to rotate local backups say every 3-5 days would be a great feature.
Any advice would be greatly appreciated.