SQL Server Automated Backup

Question

I use this Stored Procedure to get information about backups:

SELECT
        MAX(msdb.dbo.backupset.backup_finish_date) AS LastBackupDate
    FROM
        msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
    WHERE
            msdb..backupset.type = 'D'
        AND
            msdb.dbo.backupset.database_name = @DBName;

Based on that, SQL Server appears to automatically back up a database whenever a change is made to the schema or to the data itself. I want to change this behavior but I cannot find where to find backup parameters.

How can I amend backup policy?

Answer 1

SQL Server appears to automatically back up a database whenever a change is made to the schema or to the data itself.

Definitely not, SQL Server (on-premise) never performs a backup on it's own.

Why do you think? How does the backup history look like?

Answer 2

Backup schedule is something you set up locally. As Olaf says, SQL Server does not back up the database on its own initiative. (Unless you are using Azure SQL Database or Azure SQL Managed Instance, since backups are part of the service.)

Answer 3

Hi @RogerSchlueter-7899 ,

Welcome to Microsoft Q&A!

SQL Server appears to automatically back up a database whenever a change is made to the schema or to the data itself.

Please follow my screenshots to check if you have a backup maintain plan.

Then right click Modify,

You can get the details about the backup plan.

Lastly, if you want to get the backup file lacation, execute this script:

Use master
go
SELECT
database_name,
backup_finish_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
physical_device_name,
device_type
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
--WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1)
ORDER BY database_name,backup_finish_date

Maybe you can see value 7 in the column "device_type" which means "Virtual device". These rows are actually very useful for monitoring, for example it help to know that the Virtual Machine backups are running full database backups on a SQL Server instance. There's no trace of these backup files since they are above the scope of the virtual machine (above the level of your control) - these are triggered by the host,meaning azure in this case (Hyper-V or VMware for example triggers these).
For more information, please see https://ariely.info/Blog/tabid/83/EntryId/224/SQL-Server-physical_device_name-in-the-Backup-log-shows-GUID.aspx

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hello @RogerSchlueter-7899

In the article below you can information how to configure backups, it will help you to identify the menus and options where to find SQL Backup configurations, and you can modify them.

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server?view=sql-server-ver15

Hope this helps with your query,

--------------

--If the reply is helpful, please Upvote and Accept as answer--