Easy db backup and restore

declare @dbname nvarchar(max), @includeAllVersions bit, @includeOverwritten bit, @includeNonDisk bit;

-- Filter parameters
set @dbname = ''
set @includeAllVersions = 1;
set @includeOverwritten = 1;
set @includeNonDisk = 1;


/*
    [database_creation_version] is 0 for the current database guid (or most recent, if the db does not currently exist).
    If you have backups from a previous database with the same name, the version will be negative.
    If you have backups from a new database with the same name, the version will be positive.
    Any backup with version 0 can be restored without REPLACE; any other backups will require it.

    [backup_overwritten] indicates that the same path was used for a later backup, so that backup is no longer available.

    [restore_script] contains the script SQL Server Management Studio would use to perform the restore.
    If you want to restore to that point, copy and paste into a query window to execute it.
*/


with family_order_lookup as (
    select family_guid, database_name, row_number() over (partition by database_name order by min(database_creation_date)) as [version]
    from (
        select family_guid, database_name, database_creation_date as database_creation_date from msdb..backupset
        union all -- in case the current database family does not have a backup
        select family_guid, name, create_date from sys.databases join sys.database_recovery_status on database_recovery_status.database_id = databases.database_id
    ) family_dates
    group by family_guid, database_name
)
select
    *,
    case when backup_overwritten = 0 then
        case when backup_device_type = 'disk' then
            'use master;' + char(13)+char(10) +
            'alter database ' + quotename(database_name) + ' set single_user with rollback immediate;' + char(13)+char(10) +
            'restore database ' + quotename(database_name) + ' from disk = ' + quotename(backup_physical_device_name, '''') + case when database_creation_version = 0 then ';' else ' with replace;' end + char(12)+char(10) +
            'alter database ' + quotename(database_name) + ' set multi_user;'
        end
    end as restore_script
from (
    select
        backupset.database_name,
        (select [version] from family_order_lookup where database_name = backupset.database_name and family_guid = backupset.family_guid) -
        (select [version] from family_order_lookup where database_name = backupset.database_name and family_guid = database_recovery_status.family_guid) as database_creation_version,
        cast(backupset.backup_start_date as datetime2(0)) as backup_start_date,
        case backupset.[type]
            when 'D' then 'database'
            when 'I' then 'differential database'
            when 'L' then 'log'
            when 'F' then 'file or filegroup'
            when 'G' then 'differential file'
            when 'P' then 'partial'
            when 'Q' then 'differential partial'
        end as backup_type,
        backupmediafamily.physical_device_name as backup_physical_device_name,
        case backupmediafamily.device_type
            when 2 then 'disk'
            when 5 then 'tape'
            when 7 then 'virtual device'
            when 105 then 'a permanent backup device'
        end as backup_device_type,
        cast(case when row_number() over (partition by physical_device_name order by backupset.backup_start_date desc) = 1 then 0 else 1 end as bit) as backup_overwritten
    from msdb..backupmediafamily
    join msdb..backupset on backupset.media_set_id = backupmediafamily.media_set_id
    join sys.databases on databases.name = backupset.database_name
    join sys.database_recovery_status on database_recovery_status.database_id = databases.database_id
) backups
where (coalesce(@dbname, '') = '' or database_name = @dbname)
  and (coalesce(@includeAllVersions, 1) = 1 or database_creation_version = 0)
  and (coalesce(@includeOverwritten, 1) = 1 or backup_overwritten = 0)
  and (coalesce(@includeNonDisk, 1) = 1 or backup_device_type = 'disk')
order by database_name, backup_start_date;