Need some backup advice
I have a second SQL server that is configured the same as the primary SQL server - 8GB, 2 x dual-core processors, blah blah. Both servers are connected via a gigabit network. Right now the database is less than 2GB in size.
I want to create a near real-time copy of the database on a secondary server. I can do this with log shipping job from the primary to the secondary server. The downside to this is should someone delete data from the primary DB, then these changes are shipped to the database copy. I can delay the restore on the secondary server, but the error might not get noticed until it is to late.
So, is the following possible at the same time? Or is one better than the other?
1) Create log shipping job to update STANDBY database on seconday SQL server. This gets my real-time copy of the database in STANDY mode on the secondary server. The log backups setup to run every 10 to 15 minutes to secondary server.
2) Create FULL backup job on the primary database that runs every hour with DIFF backups every 10 mins. These backups files get copied to a 3rd server for safe keeping. So should errors get shipped to the secondary server I still have access to a FULL backup and 5 or 6 DIFF backups.
#1 saves me if the primary server goes belly up on me. Simply activate the STANDBY database and continue. Might lose a few minutes of posts, but better than the alternative.
#2 saves me if something gets shipped from the primary to the secondary server by mistake - ie dropped table or deleted data.
Yes, I am very paranoid about losing data or the database.
Thanks in advance.