Sql-server – Adding new databases to existing maintenance backup plan

sql server

I am trying to automate our backup plans to add newly created backups automatically on SQL server 2008. Is there a way to do this?
Since it wont let me comment on this to clarify. I am trying to add new databases that we have created to an existing Management Maintenance Plan on SQL Server 2008. I am trying to make it a script that we can run that will automatically add the database when created. Is this possible?

Best Answer

Yes, you can use the well used ola hallengren.scripts.

Make use of the SQL Server Backup scripts available on the above.

Eg:

Back up all user databases, using checksums and compression; verify the backup; and delete old backup files

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24

Then run the above script on SQL server agent job as per the backup plan strategy to execute this.

Also, just to mention you need to first create the scripts CommandExecute.sql and DatabaseBackup.sql in order to use the backup plan.

Related Solutions

Sql-server – SQL Server Maintenance Plan TSQL query to log the backup databases

You can get the backup date for each database backed up after the plan started from

--INSERT INTO YourBackupLogTable
--     (DatabaseName, LastBackupStartDate, LastBakupFinishDate, LastBackupDurationSecs)
SELECT db.name AS DatabaseName
    , MAX(lb.backup_start_date) AS LastBackupStartDate
    , MAX(lb.backup_finish_date) AS LastBakupFinishDate
    , DATEDIFF(S, MAX(lb.backup_start_date), MAX(lb.backup_finish_date) ) AS LastBackupDurationSecs
FROM sys.databases AS db
JOIN msdb.dbo.backupset lb ON lb.database_name = db.name
WHERE lb.backup_start_date >= (SELECT MAX(l.start_time) AS Start 
                                 FROM msdb.dbo.sysmaintplan_plans p
                           INNER JOIN msdb.dbo.sysmaintplan_log l ON p.id = l.plan_id
                                WHERE name = 'Your backup plan')
GROUP BY db.name

Sql-server – backup maintenance plan fails with error 0x80131534

Grant the access to the computer account since the service is running under NT Service\InstanceName: domain_name>\$

Example: Domain1\MyServerName$

MyServerName must be the name of the server, not the name of the SQL instance. Get the server name via hostname in a cmd window. By adding the dollar sign to the end, it's the computer account.

Best Answer

Related Solutions

Sql-server – SQL Server Maintenance Plan TSQL query to log the backup databases

Sql-server – backup maintenance plan fails with error 0x80131534

Related Question