SQL Server Backups...
My problem lies in that I will be co-locating and will not have direct access to tape drives, but only one large hard drive to make all database backups on. Thus I am thinking of doing a full backup of the necessary databases to this backup disk once a day with a few differential backups throughout the day by using some sort of automated process. I am also contemplating using a VPN or FTP to get the latest backup once a week in case the co-located server becomes compromised and all data is erased.
What I am wondering is where you keep your database backups and how many old database backups you keep on average? What sort of process do you use to automate such a task? (Simply copy the .MDF and .LDF files, use Enterprise Manager, use T-SQL, etc...) And finally, is it really safe to be doing differential backups on the same last full backup file? I worry that something might go wrong with the backup and corrupt the latest full backup file.
For your information the database I am using is MS SQL Server 2000 and I plan on using T-SQL to automate the process.
