October 23, 2014 at 5:49 am
Hello all,
instead of reinventing the wheel i wanted to find out if someone is already doing something like below if so can you share your script with me?
We have our daily backup scheduled thru the agent, Sunday is full and rest of the days is incremental backup. In one of our environment new database is being created by app team almost every other day(during weekdays) and due to that our incremental backup fails as that newly created DB dose not have a FULL backup....
So i wanted to find out if anyone has a script/proc that can be scheduled to do something like below...
if a new database is created then go ahead and do a full backup the database to disk ... i tried searing here under the scripts location but was not able to find something similiar...
any help would be much appreciated it.
October 23, 2014 at 5:58 am
I think Ola Hallengren's procedures will automatically take a full backup if one doesn't exist.
John
October 23, 2014 at 6:05 am
hello,
could you please explain or point me in the right direction when you says "Ola Hallengren's procedure" ?
October 23, 2014 at 6:16 am
In the little window, type "Ola Hallengren".
His web page will appear. All of the instructions are explained clearly on his page.
And, it will automatically take a full backup if none exists.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 23, 2014 at 6:16 am
On the internet you can find many custom scripts and solutions for database backup (many incuding database maintenance). Ola Hallengren https://ola.hallengren.com/ has a highly recommendedd solution, but you can find many others.
October 23, 2014 at 6:36 am
sorry about that. my mind went blank for a min when i posted that....i found that script below...but we dont want to schedule that to do our daily backup...only do backup one a new one is created otherwise dont(our job will take care of that)
https://ola.hallengren.com/sql-server-backup.html
the values i see is
ValueDescription
SYSTEM_DATABASESAll system databases (master, msdb, and model)
USER_DATABASESAll user databases
ALL_DATABASESAll databases
Db1The database Db1
Db1, Db2The databases Db1 and Db2
USER_DATABASES, -Db1All user databases, except Db1
%Db%All databases that have “Db” in the name
%Db%, -Db1All databases that have “Db” in the name, except Db1
ALL_DATABASES, -%Db%All databases that do not have “Db” in the name
October 23, 2014 at 7:14 am
You can put the execution of your DIFF backup statement inside a TRY...CATCH block. Check in the CATCH block if the error message is the missing FULL backup. If so, start the FULL backup procedure and if not, display the error message.
October 23, 2014 at 7:39 am
You didn't read the instructions down far enough.
There is a parameter @ChangeBackupType. If this is set to 'Y', it will automatically change a diff or log to a full backup if there is not a current full backup.
The command, it will need to be changed to fit your environment
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBName -Q "EXECUTE [dbo].[DatabaseBackup] @databases = 'USER_DATABASES', @Directory = N'Z:\Backups', @BackupType = 'DIFF', @ChangeBackupType = 'Y', @verify = 'Y', @CleanupTime = 24, @checksum = 'Y', @LogToTable = 'Y'" -b
The simple solution to this is to do a full backup every night, or, test for the existence of a backup.
This code will list the DB's that have not had a full backup.
SELECT D.name
FROM sys.databases D
WHERE NOT EXISTS (SELECT B.database_name FROM msdb.dbo.backupset B WHERE D.name = B.database_name AND B.type = 'D')
A catch block seems a bit convoluted.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply