2

I recently implemented Ola Hallengren Maintenance solution on all of my production servers(SQL server 2005,2008,2008R2 and 2012). It works great.
My problem is how to implement Ola Hallengren backup plan on the server for some databases nightly full backups and for other weekly full backup only. To be specific I have 20 databases on the server. It is required 15 databases have nightly full backup plan. The remaining five databases should have weekly database full backup.

I scheduled the backup job for 15 databases nightly full backup. It is working great. But I don’t know how to take care the remaining five databases weekly full backup. (SQL Server 2008R2) On native maintenance plan I used to create two plans 1. Nightly full backup for the 15 databases and 2. Weekly full backups for the remaining five databases I don’t know how to do that on ola. Do I need to install ola Backup solution again for the five databases? Or is there another way to do it?

I copy and past the script from ola User database job substitute my databases on the place of user databases and create backup job as follows

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d MASTER -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'DB1,DB2,DB3,DB3,DB4,Db5', @Directory = N'C:\Backup2', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 72, @CheckSum = 'Y', @LogToTable = 'Y'" -b

I run a test backup . It worked but I am not sure that is the right way doing it or not? could you please somebody tell me whether it is ok or not. Thank you your help in advance.

@KASQLDBA I have attached the error screen shot Error Screen shot

Improve this question

1 Answer 1

Reset to default
2

No you don't need to install the Ola's solution again since the initially installed commandexecute.sql and databasebackup.sql would have been saved on master DB as SP.

You just need to create a new job , as one created early but this time for 5 left over DB;s and schedule it weekly per you're timings.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',-- Name of those 5 DB's would come here
@Directory = 'C:\Backup',-- You're backup path
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y'

enter image description here You can use above script while creating the T-SQL job and schedule it accordingly.

More details on how to do above refer to schedule job

Also, just to make sure both, initial backup and this newly created backup job not to run on same time you can put a condition or per the analysis when you're daily backup job completes, you can schedule this new weekly/nightly backup job for remaining 5 DB's

Improve this answer
7
  • Thank you for responding KASQLDBA. can you tell me how to create the job? If you can . I am new . for the others I didn't create a jobs . it was created after I run ola script. I only scheduled the jobs.
    – SQL_DBA_Bini
    Commented Mar 17, 2015 at 14:59
  • @DBABiniam i just edited the answer.. You can refer there on how to create and schedule.
    – KASQLDBA
    Commented Mar 17, 2015 at 15:08
  • Thanks KASQLDBA. I run the script you wrote in while I was creating the job. Unfortunately when I run the job. it gave me this Error:[136] Job New_Backup_job reported: The process could not be created for step 1 of job 0x7D5BAD177B0229408439B782A4B44946(REASON: the system cannot find the file specified)
    – SQL_DBA_Bini
    Commented Mar 17, 2015 at 16:47
  • please attach the screenshot of the error or the link so that i can see it
    – KASQLDBA
    Commented Mar 17, 2015 at 16:51
  • I attached the screenhot
    – SQL_DBA_Bini
    Commented Mar 17, 2015 at 19:10

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.