Batch files for backup SQL Server

Gad SAADIA
Gad SAADIA used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> 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 SAADIAManager

Author

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 Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> 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..
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Gad SAADIAManager

Author

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 Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> 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 SAADIAManager

Author

Commented:
Here the PDF..
sql.pdf
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Yes, should be fine, we should be good by taking the backup of all databases via Maintenance plans..
Gad SAADIAManager

Author

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 Guide
Awarded 2009
Distinguished Expert 2018

Commented:
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 SAADIAManager

Author

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 Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> 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 SAADIAManager

Author

Commented:
Are these accounts OK ?

See attached PDF

Thank you  very much
sql1.pdf
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
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..
Gad SAADIAManager

Author

Commented:
thank you for all !!!
I will try all this information
Thank you for your time
Regards
Gad SAADIAManager

Author

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 Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Thanks, gadsad for the kind words.. Glad to assist!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial