Batch files for backup SQL Server

Hello

In order to backup properly my SQL Database I need to create 2 batch files which will run on 2 scheduled task and will do the following tasks

1)      Daily at 23h PM : STOPSQL.BAT : Stop SQL SERVER Service and Stop SQL Server Agent Service
2)      Daily at 6h AM: STARTSQL.BAT: Start SQL SERVER Service and Start SQL Server Agent Service

What is the recommended solution for the content of these 2 batch files?
Thank you
Gad SAADIAManagerAsked:
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.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Stop SQL SERVER Service and Stop SQL Server Agent Service

Kindly confirm whether you have Express edition of SQL Server or some other edition..
Asking it since your requirement is slightly weird..
1. There is no need to shut down SQL Server Services to take proper backup of SQL Server databases. We can take Full/Diff or Log backups without bringing down SQL Server Service.
2. Why you need a Batch file when you have Maintenance plans and SQL Server Agent Jobs available in SQL Server.
Maintenance Plans:
https://digitalsupport.ge.com/en_US/Article/How-to-Create-a-Maintenance-Plan-to-Backup-SQL-Databases?Type=Article__kav
SQL Server Agent Jobs via Scripts:
https://ola.hallengren.com/sql-server-backup.html
Gad SAADIAManagerAuthor Commented:
Hello and thank you for your answer

SQL Server 2014 regular edition

My ISV suggest to turn off both services before backup and then to start them again

- before backup of ther SQL server
net stop sqlserveragent
net stop mssqlserver

-  after backup of the SQL server
net start mssqlserver
net start sqlserveragent

Is it OK ?
THank you very much
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> My ISV suggest to turn off both services before backup and then to start them again

If your ISV is suggesting that, then your backup is at File level or drive snapshot level, kindly confirm..

>> Is it OK ?

Command seems fine, but I am not OK with the approach as it is very strange and haven't seen anyone doing like this till now..
I've specified the recommended backup approaches earlier and the approach you are saying is very different..
If you can give more details on how you would take the backup after bringing down services, we can check it out once..
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Gad SAADIAManagerAuthor Commented:
ISP is telling the following thing:
backup at the file level, by regular windows copy or xcopy. No Drive snapshot
 
For your information:
Size of database folder: 57Go
SIze of main database : 3Go (1 single MDB file)
About 900 small databases of 40K to 80K each (average 800 MDB files of 60K each file. Each MDB file on a different folder, see attached PDF)
So total size of main SQL database folder: 60K x 900+ 3Go = 57Go

 COuld I make a maintenance plan for so many small databases?
Is it safe? Is it OK?

Thank you for your help

Data structure on PDF attached
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> backup at the file level, by regular windows copy or xcopy. No Drive snapshot

That's a pretty strange recommendation from them..

>>  COuld I make a maintenance plan for so many small databases? Is it safe? Is it OK?

Yes, we can create a single Maintenance plan to take backup for all databases available in the instance.
If the database files are occupying around 57GB, then taking backup should occupy only around 10-15 GB instead of your File copy size of 57 GB. So, with SQL Server backup, you can save a lot of disk space and there is no need to shut down SQL Server services at all.

>> Each MDB file on a different folder, see attached PDF)

Seems you forgot to attach the pdf file.. Kindly reattach it to verify once..
Gad SAADIAManagerAuthor Commented:
Here the PDF..
sql.pdf
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes, should be fine, we should be good by taking the backup of all databases via Maintenance plans..
Gad SAADIAManagerAuthor Commented:
Thank you again

Can I make the maintenance plan on a UNC share folder (\\192.168.1.10\backup)  or on a Mapped Drive letter (R: = \\192.168.1.10\backup)?
Since 60Gb is quite a lot and if I schedule it for 5 days it could saturate my SQL drive (60Gb x 5 = 300Gb)

Regards
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes, you can take it to the UNC Share folder directly.. Mapped drive won't work..
Kindly grant necessary rights for the SQL Server service to access the UNC Shared folder to take the backup directly..
Gad SAADIAManagerAuthor Commented:
Thank you

Regarding rights for UNC, if I am able to access the network share R/W in Windows explorer by doing \\192.168.1.10\backup it is enough? Or do I have to give special rights to the SQL Server Service? Not clear for me

Best regards
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> if I am able to access the network share R/W in Windows explorer by doing \\192.168.1.10\backup it is enough?

Yes, Hope your SQL Server and SQL Server Agent service accounts are domain accounts, kindly confirm..
Similar to your account, the account that is running SQL Server and Agent services should be granted R/W access to the shared path to get it work..
Hope this clarifies..
Gad SAADIAManagerAuthor Commented:
Are these accounts OK ?

See attached PDF

Thank you  very much
sql1.pdf
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Nope, NT Service accounts can access only the resources in your Server and can't access the UNC path.
So, you would need to change it to some windows accounts like DOMAIN\user_name and then ensure that the account has access to both SQL Server and UNC path to get it work..

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
Gad SAADIAManagerAuthor Commented:
thank you for all !!!
I will try all this information
Thank you for your time
Regards
Gad SAADIAManagerAuthor Commented:
thank you for all!!
Very very helpful. I will try to implement all you told me

Excellent consultant and expert
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Thanks, gadsad for the kind words.. Glad to assist!!
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
Query Syntax

From novice to tech pro — start learning today.