Create SQL Server Job to perform backups of all the databases in a server instance

Dale Fye
Dale Fye used Ask the Experts™
on
SQL Server 2008

I would like to create 2 scheduled jobs on a clients SQL Server to conduct nightly backups.  I had this configured several years ago and it was working great, but this past weekend my client had a power outage that lasted longer than the 6 hour UPS and the UPS software failed to shut things down elegantly when they reached the 20% power.  This caused me to have to recreate the database as the system databases were corrupted.  At any rate, that process is complete but now I'm trying to recreate the stored procedures I had configured to loop through each of the databases (including system dbs) and:

1.  On a weekly basis conduct a full backup to a folder labeled:

D:\MSSQL\Backups\DatabaseName\

2.  On a nightly basis conduct an incremental backup to the same folder.

The current file names look like "databasename_backup_yyyy_mm_dd_hhmmss_XXXXXXX.bak" where the X's represent a 7 digit number which I have no idea about.

The way this was configured was that we would retain two of the full weekly backups and all of the daily backups between those so we would have backup files like:

A_backup_2017-03-03_040000.bak   incremental
A_backup_2017-03-02_040000.bak   incremental
A_backup_2017-03-01_040000.bak   incremental
A_backup_2017-02-28_040000.bak   incremental
A_backup_2017-02-27_040000.bak   incremental
A_backup_2017-02-26_040000.bak   incremental
A_backup_2017-02-25_040000.bak   full
A_backup_2017-02-24_040000.bak   incremental
A_backup_2017-02-23_040000.bak   incremental
A_backup_2017-02-22_040000.bak   incremental
A_backup_2017-02-21_040000.bak   incremental
A_backup_2017-02-20_040000.bak   incremental
A_backup_2017-02-19_040000.bak   incremental
A_backup_2017-02-18_040000.bak   full

Then, when the full backup occurs on March 4th, the full backup from 2/18 and all of the daily backups from 2/19-2/24 would be deleted.

Thanks for your help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Tom CieslikIT Engineer
Distinguished Expert 2017

Commented:
Create MSSQL folder and make ist share with right FULL to everyone and security RW to everyone
Inside create subfolder BACKUPS
Open SQL server management Studio
Log in as SA
Go to
SQL Server Agent / Jobs

Create new Job
Name it,,, SQL Full Backup Weekly
Ins steps Create step Full DATABASE BACKUP
in Command put this command

{Replace word SERVER with your Database server name}

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'SYSTEM_DATABASES', @Directory = N'\\SERVER\MSSQL\Backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 100, @CheckSum = 'Y', @LogToTable = 'Y'" -b


CleanupTime = 100 is time after job will delete old backup so if you want create full backup ever week then you must decide how many old backup you ant to keep. If you'll decide keep 2 old backup then put 14 days x 24 hours x 60 = 2140
This is time in minutes where job will delete old backup

Then in Schedule step put this backup to run in day and time you want.

For Incremental backup you need to create 2 backups for LOGS and Databases
Create new Job


For DATA FILES

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = '%FV%,%ReportServer%,%iDashboards%', @Directory = N'\\SERVER\MSSQL\Backups', @BackupType = 'DIFF', @Verify = 'Y', @CleanupTime = 24, @CheckSum = 'Y', @LogToTable = 'Y'" -b

CleanupTime - you need to decide where to clean old backup in minutes

FOR LOGS

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = '%FV%,%ReportServer%,%iDashboards%', @Directory = N'\\SERVER\MSSQL\Backups', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 24, @CheckSum = 'Y', @LogToTable = 'Y'" -b

Put this 2 jobs to schedule

Capture.JPG
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Tom,

Just to make sure I understand.  These scripts will backup the 4 system databases and all of the production databases on this particular instance of SQL Server, right?
Tom CieslikIT Engineer
Distinguished Expert 2017

Commented:
Yes, Master model and msdb will only go to Full backup since there is no purpose making differential backup for this databases.
This scripts will create subfolders under your backup location so you'll see what king of databases you'll have on backup
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!

IT Engineer
Distinguished Expert 2017
Commented:
I forgot something

This script will backup only SYSTEM DATABASES

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'SYSTEM_DATABASES', @Directory = N'\\SERVER\MSSQL\Backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 100, @CheckSum = 'Y', @LogToTable = 'Y'" -b

If you want different database backup then you  need add additional step with this script

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = '%FV%,%ReportServer%,%iDashboards%', @Directory = N'\\SERVER\MSSQL\Backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 100, @CheckSum = 'Y', @LogToTable = 'Y'" -b


Here you can put names of all your databases you want backup

@Databases = '%FV%,%ReportServer%,%iDashboards%',
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Tom,

Is there a reason to maintain backups of the logs for more than 24 hours?  I understand that if I had to rebuild my database, I would need the latest full backup and each of the incremental backups to restore the database fully.  But do I need daily incremental backups of the log files as well?

I'm obviously not a SQL DBA and this particular client does not have one, so I'm trying to get this right and get a better understanding of the logs, why I need them, and whether I need to maintain incremental backups.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Tom,

Will this also backup tempdb?  I cannot see a reason to do that, and although you don't specifically mention it above, I wanted to check.

Dale
Tom CieslikIT Engineer
Distinguished Expert 2017

Commented:
If you use  'temp%' then will backup all with TEMP in the beginning of the name.
In my case I've used 'FV%' so I have backuped all databases with name starting from FV

Capture.JPG
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Thanks, for your help Tom.
Tom CieslikIT Engineer
Distinguished Expert 2017

Commented:
You are very welcome.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Tom,

When I run this SQL statement:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q
"EXECUTE [dbo].[DatabaseBackup]
@Databases = '%Accounting_Ledger%,%WHR%',
@Directory = N'D:\MSSQL\Backups',
@BackupType = 'DIFF',
@Verify = 'Y',
@CleanupTime = 2140,
@CheckSum = 'Y',
@LogToTable = 'Y'" -b

I get the following error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'e'.
Msg 103, Level 15, State 4, Line 1
The identifier that starts with 'EXECUTE [dbo].[DatabaseBackup]
@Databases = '%Accounting_Ledger%,%WHR%',
@Directory = N'D:\MSSQL\Backups',
@BackupType = '' is too long. Maximum length is 128.
Tom CieslikIT Engineer
Distinguished Expert 2017

Commented:
I see your problem


@Directory = N'D:\MSSQL\Backups',

It has to be Network share no D drive.
I said on the beginning that you must create share with all rights to perform backup :)
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Don't think that is it.

I changed it to read:
@Directory = N'\\whr-sql\D\MSSQL\Backups',

and I'm still getting the error message.  If it helps, this is what the T-SQL looks like.
SQL-Backup.jpg
Tom CieslikIT Engineer
Distinguished Expert 2017

Commented:
What is this D for ?
Tom CieslikIT Engineer
Distinguished Expert 2017

Commented:
If WHR-SQL is your server name
make sure you have MSSQL share folder.
Create Backups subfolder

then change your path to this

N'\\whr-sql\MSSQL\Backups',
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
The "D" is a subfolder on \\whr-sql\

and is the folder where the previous backups have been going:

\\whr-sql\D\MSSQL\Backups
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Could this have to do with the "SRVR" on the first line of code?  Is that supposed to be the name of the SQL Server?
Tom CieslikIT Engineer
Distinguished Expert 2017

Commented:
No, my server name is DATABASE and code is working perfectly
Maybe you have some extra hidden sign like space or so... My code is not divided by rows

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = '%FV%,%ReportServer%,%iDashboards%', @Directory = N'\\DATABASE\MSSQL\Backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 100, @CheckSum = 'Y', @LogToTable = 'Y'" -b

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