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.
LVL 51
Dale FyeOwner, Developing Solutions LLCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Tom CieslikIT EngineerCommented:
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
0
Dale FyeOwner, Developing Solutions LLCAuthor 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?
0
Tom CieslikIT EngineerCommented:
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
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Tom CieslikIT EngineerCommented:
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%',
0

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
Dale FyeOwner, Developing Solutions LLCAuthor 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.
0
Dale FyeOwner, Developing Solutions LLCAuthor 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
0
Tom CieslikIT EngineerCommented:
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
0
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
Thanks, for your help Tom.
0
Tom CieslikIT EngineerCommented:
You are very welcome.
0
Dale FyeOwner, Developing Solutions LLCAuthor 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.
0
Tom CieslikIT EngineerCommented:
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 :)
0
Dale FyeOwner, Developing Solutions LLCAuthor 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
0
Tom CieslikIT EngineerCommented:
What is this D for ?
0
Tom CieslikIT EngineerCommented:
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',
0
Dale FyeOwner, Developing Solutions LLCAuthor 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
0
Dale FyeOwner, Developing Solutions LLCAuthor 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?
0
Tom CieslikIT EngineerCommented:
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
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.