0

Can anyone help me with a script to take a full or complete backup of all databases once in a week and to take differential backups daily. ?

2
  • 1
    The normal way to do this would be a maintenance plan. Is there a reason you can't use that? For example, are you running Express?
    – Bridge
    Feb 16, 2016 at 8:54
  • Yes. I am using Express Edition of SQL Server.
    – leeg4625
    Feb 16, 2016 at 8:58

3 Answers 3

2

I believe @Les H has already answered and Yes, ola's script should do the work here without any issues.

However as per the MS Link you can also do something like:

  1. Use SQL Server Management Studio Express or Sqlcmd to create the following stored procedure in your master database named sp_BackupDatabases. The script to create the SP is available on the link.

  2. create a batch file that will execute the above SP, also specifying the location of backup and its mode, that is Full , differential or log.

  3. Schedule a job by using Windows Task Scheduler to execute the batch file that you created in step 2

3

You should look at Ola Hallengren's scripts

https://ola.hallengren.com/

The FAQ has examples on using these scripts with SQL Express.

https://ola.hallengren.com/frequently-asked-questions.html

2
  • The code Maintenancesolution.sql has many no.of lines of code which depletes the performance and make it slower. Can you give me a much simple solution..?
    – leeg4625
    Feb 18, 2016 at 9:03
  • What are you basing the performance on? We've had it running across our entire estate for the last year without issue. Why do you think that many lines of code make it slower? You might also want to check out minionware.net/#miniontabs|2 for a second option
    – Pixelated
    Feb 18, 2016 at 10:37
0

Why don't you use one of many third-party tools? You can set up scheduled backups as you need full backup once a week and differential backups daily.

You can use SqlBak (SAAS model) or SQLBackupAndFTP they both have a free plan. Or you can try something more expensive like SQL Backup from RedGate or Idera.

So test them and chose the best one that you need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.