Alternative for taking the backup of SQL DB's on backup drive

Question

We just got new servers to take care of, regarding their backups, handed over by the company B who used to manage the backups, but they never did via any mant'ce plans or SQL jobs.They did that task through NBU client. Thus backups were never backed up on the drives on that respective SQL server.

Now, as per the security/audit findings we need to have backups on the drive.

But the issue is we never had any backup drive on those servers. Moreover many of the servers only have a single data drive.

Therefore we came up with a plan to back them over NW onto the server with ample amount of space as of now, till we get the backup drives installed on each server.

But again we came with a challenge of resources that is being utilized while copying / taking backups over the NW like CPU and Memory usage got high.

Can someone help with their expertise on what alternative can be used, because we are short of space as of now and adding a drive per our process takes a month.

Kindly suggest!

Thank you!

Answer 1

Speaking as someone who has taken over from multiple third parties who have done NetBackup database backups you can safely assume three things.

• Many of your servers and individual databases have not been backed up. You would do best to confirm yourself through msdb checks.
• The "tapes" being backed up to are probably not accessible, do not work, or otherwise don't exist.
• You can probably find a lot of NetBackup failure notifications in ErrorLog which have been ignored for days, months, and years.

Regarding your specific situation backups are meant to be taken to network shares and backing up to the same server is wrong. What is going to happen when your server VM crashes or becomes corrupt? You've lost all your backups.

Whatever security / audit issues have come up are obviously wrong and you should be rallying against them. Or - print out your ironclad disclaimer and get your manager to sign it - because they're going to be fired down the line when they lose everything and you don't want this to destroy your career along with their poor decisions.

Best practice? A network share like you mentioned, and standardise on Ola Hallengren's scripts like others mentioned.

Don't be afraid to modify the setup script a little (or add your own to run afterwards) to set up the jobs the way you like them and to schedule them; you can also stagger them server by server if you like to spread the load. I wrote a corresponding script in PowerShell to help do the deployments (create a folder on each server to store the output text files; grant the service account access; create the network folder and grant the service or network account access; then deploy the jobs in the way we want them and with appropriate schedules).

You should also prioritise setting up your registered server list so that in the mean time you can run mass checks of msdb catalogs to make sure you haven't missed anything.

Network and CPU going high doesn't mean anything unless it impacts Production; the only important thing aside from gigabit network interfaces is that the network share is somewhere relatively close to whatever network segments you have. And the initial FULL backups will always be an issue, that's why they're scheduled for weekends, then daily DIFF will be fine.

Otherwise the only databases where this really becomes a problem are when they start going over 1TB (especially if it's much higher, 5TB, 20TB) and those need special attention.

Answer 2

You have several questions or issues in your post. Unfortunately, I am unsure that I clear on every issue.

• I understand that you are no longer using NBU. (Is this Symantec NetBackUp?) -- SQL Server native backup, starting with 2008 R2, supports compressed backups with Standard and Enterprise editions. This will add some load to the CPUs but reduce load on the network and probably use only about 1/3 the storage of non-compressed backups.
• "We need to have backups on the drive." -- If you mean backing up to a drive on the server, this should be only a temporary resting place for backups. You actually need your backups to be on some other media. I used to backup to a dedicated file server, but now use a SAN to store the backups. Then we replicate the backups from that SAN to a remote SAN.
• CPU and Memory Usage got high. -- Of course that is true, since the computer is doing a lot of work. However, I have not found that this increased activity during backups has caused a significant impact on other work going on. We backup late at night.

Some of your comments obviously depend on your infrastructure of servers, network, storage, and so forth. You need space for backups. You need to determine how long you will maintains the backups (perhaps 1 month would be a good target) and then get enough storage to handle the needed backups.

In terms of automating your process and making sure that the databases are maintained, I use and recommend the free solution from Ola Hallengren to manage all of those issues. You can find that solution at: https://ola.hallengren.com/

This is a very widely used solution and is well supported by Ola.