The backup and restore process of a big database or assortment of databases on sql server is essential for disaster &lifier recovery reasons. However, I haven't found a strong solution which will ensure the whole process is really as efficient as you possibly can, 100% reliable and simply maintainable and configurable accross multiple servers.
Microsft's Maintenance Plans does not appear to become sufficient. The very best solution I have tried personally is a which i produced by hand using many jobs with lots of steps per database running around the source server (backup) and destination server (restore). The jobs use saved methods to complete the backup, copying &lifier rebuilding. This runs daily (full backup/restore) and intraday every 5 mins (transaction log shipping).
Although my current process works and reviews any job failures via email, I understand the entire process is not very reliable and can't easily be maintained/set up on all of our servers with a non-DBA without getting in-depth understanding from the process.
I must determine if others have this same backup/restore process and just how others overcome this problem.
The important thing a part of your real question is the power for that backup means to fix be handled with a non-DBA. Any native SQL Server answer like backup scripts is not likely to meet that require, because backup scripts require T-SQL understanding.
Due to that, you need to look toward third-party solutions such as the ones Mitch Wheat pointed out. Sometimes for Mission (the manufacturers of LiteSpeed) so obviously I am a fan of that certain - it's not hard to show to non-DBAs. Before I left my last company, I'd a ten minute session to exhibit the sysadmins and designers the way the LiteSpeed console labored, which was that. They haven't known as since.
Another approach is utilizing the same backup software the relaxation of the shop uses. TSM, Veritas, Backup Professional and Microsoft DPM all have SQL Server agents that let your Home windows admins manage the backup process with different levels of ease-of-use. If you want a non-DBA to handle it, this really is most likely probably the most dead-good way to get it done, even though you sacrifice lots of performance the SQL-specific backup tools provide you with.
I have used an identical key to keep dev/test/QA databases 'zero-stepped' on the nightly grounds for designers and QA folks to make use of.
Documentation is paramount - if you wish to remove what Scott Hanselman calls 'bus factor' (i.e. the risk the creator from the system can get hit with a bus and everything begins to suck).
Nevertheless, for normal database backup copies and disaster recovery plans, I have discovered that SQL Server Maintenance Plans exercise pretty much. As lengthy while you include: 1) Decent documentation 2) Routine testing.
I have layed out a few of the ways to carry out doing that (for anybody attracted for this question searching for a good example of how to pull off developing a disaster recovery plan):
SQL Server Backup Guidelines (Free Tutorial/Video)
I'm doing the same factor and also have various issues semi regularly despite this method.
How can you handle the spacing between copying the file from Server A to Server B and rebuilding the transactional backup on Server B.
Every every now and then the transaction backup is bigger than usual and takes a longer period copying. The restore job then will get an operating-system error the file is being used.
This isn't this type of large deal because the file is instantly applied next time around however it might be better to possess a more elegant solution generally and something that particularly fixes this problem.