How to backup all SQL Server DB to shared network drive daily ?

People,

I have Microsoft SQL Server 2012 Standard Edition (64-bit) (SP2) - 11.0.5343.0 single DB instance (default MSSQLSERVER) with about 15 DB running in it.

How can I perform nightly SQL Server backup to the other server using UNC path ?

\\Backupserver\SharedFolder\SQL

Which permission do I need to grant ?

Thanks.
LVL 12
Senior IT System EngineerSenior Systems EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Take a look at the link below for instructions on how to backup to a network drive.

https://www.mssqltips.com/sqlservertip/3499/make-network-path-visible-for-sql-server-backup-and-restore-in-ssms/

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jose TorresCertified Database AdministratorCommented:
My personal thoughts.
Unless I have no choice (i.e., gun to my head) I generally avoid backing up to shared drives....
To all other members that's just me and my personal preference.

That being said the quickest and easiest way is to create a maintenance plan
Make sure SQL Server Service is running using an AD account then make sure that account has access to the share.

Sorry forgot.
The maintenance plan select all the databases you want
For folder it will accept the UNC \\<ServerName>\<FolderName>
Senior IT System EngineerSenior Systems EngineerAuthor Commented:
Hi,

The current SQL Server (MSSQLSERVER) service is running using the NT Service\MSSQLSERVER account by default.

So can I just grant the Computer AD account where the SQL Server running with full permission on both Shared & NTFS permission ?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Senior IT System EngineerSenior Systems EngineerAuthor Commented:
OK,ih ave set it up working now but how can I enable the email alerting ?

I've created operator as myself but when I click on the send email alert I got this error:

---------------------------
Windows Internet Explorer
---------------------------
Could not perform this operation because the default mail client is not properly installed.
---------------------------
OK  
---------------------------
Jose TorresCertified Database AdministratorCommented:
Is dbmail setup
Senior IT System EngineerSenior Systems EngineerAuthor Commented:
How to check it ?

I prefer not to install MS office in a DB server.
Jose TorresCertified Database AdministratorCommented:
Database Mail is part of SQL Server
Database Mail
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.