Avatar of Dale Fye
Dale Fye
Flag for United States of America asked on

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

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.
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Tom Cieslik

8/22/2022 - Mon
Tom Cieslik

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 Fye

ASKER
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 Cieslik

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Tom Cieslik

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Dale Fye

ASKER
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 Fye

ASKER
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 Cieslik

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Dale Fye

ASKER
Thanks, for your help Tom.
Tom Cieslik

You are very welcome.
Dale Fye

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Tom Cieslik

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 Fye

ASKER
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 Cieslik

What is this D for ?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Tom Cieslik

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 Fye

ASKER
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 Fye

ASKER
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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
Tom Cieslik

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