Link to home
Start Free TrialLog in
Avatar of Dale Fye
Dale FyeFlag 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.
Avatar of Tom Cieslik
Tom Cieslik
Flag of United States of America image

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

User generated image
Avatar of 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?
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
ASKER CERTIFIED SOLUTION
Avatar of Tom Cieslik
Tom Cieslik
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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
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

User generated image
Thanks, for your help Tom.
You are very welcome.
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 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 :)
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.
User generated image
What is this D for ?
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',
The "D" is a subfolder on \\whr-sql\

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

\\whr-sql\D\MSSQL\Backups
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?
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