List of recent database backups

Usually we need to find list of recent database backups. When the backup was taken and where it was saved. Below script will get a 5 recent backups

DECLARE @DBNAME VARCHAR(128)

SET @DBNAME = 'DB_Name'

SELECT TOP 5 
 A.database_name
 ,B.physical_device_name
 ,A.media_set_id
 ,A.backup_size
 ,A.backup_start_date
 ,A.backup_finish_date
 
FROM msdb.dbo.backupset A
 INNER JOIN msdb.dbo.backupmediafamily B
ON 
 A.media_set_id = B.media_set_id
WHERE 
A.Database_Name= @DBNAME

ORDER BY A.backup_finish_date DESC

Detach/Attach VS ALTER DATABASE MODIFY FILE

It's not a news that Detach/Attach method for moving database affects different aspects. When a read-only database is detached and then reattached, the backup information about the current differential base is lost. Service Broker gets disabled and it requires exclusive access to re-enable it back. TRUSTWORTHY also get disabled during detach/attach operation.
So in order not to get trapped it's better to use ALTER DATABASE MODIFY FILE command. Below script generated ALTER DATABASE commands, you just need to enter your database name and new locations for data and log files:

DECLARE @NewDataFolder nvarchar(1000);
DECLARE @NewLogFolder nvarchar(1000);
DECLARE @FileTable table (lkey int identity (1,1) primary key, [name]nvarchar(100), physical_name nvarchar(1000), [type] int );
DECLARE @CmdTable table (command nvarchar(1000));
DECLARE @sql nvarchar(4000);
DECLARE @count int;
DECLARE @RowNum int;
DECLARE @DbName nvarchar(100);
DECLARE @OldPath nvarchar(1000);
DECLARE @Type int;
DECLARE @LogicalName nvarchar(100);
DECLARE @ParmDefinition nvarchar(1000);
DECLARE @FileName nvarchar(100);
DECLARE @NewPath nvarchar(1000);

--Make sure that @NewDataFolder and @NewLogFolder has '\' at the end
SET @DbName = 'YourDBName'
SET @NewDataFolder = 'C:\SQLData\'
SET @NewLogFolder = 'C:\SQLLog\'

SET @sql = 'select name, physical_name, type from  ' + @DbName + '.sys.database_files'
INSERT INTO @FileTable
EXEC sp_executesql @sql 

WHILE @@rowcount > 0    
 BEGIN
  
  SET @OldPath = (SELECT TOP 1 physical_name  FROM @FileTable)
  SET @Type = (SELECT TOP 1 [type]  FROM @FileTable)
  SET @LogicalName = (SELECT TOP 1 [name]  FROM @FileTable)
  SET @FileName = (SELECT REVERSE(SUBSTRING(REVERSE(@OldPath), 0, CHARINDEX('\', REVERSE(@OldPath), 1))))
  IF @type = 0
  BEGIN
   SET @NewPath = @NewDataFolder + @FileName
  END
  ELSE
  BEGIN
   SET @NewPath = @NewLogFolder + @FileName
  END
  SET @sql = 'ALTER DATABASE ' + @DbName + ' MODIFY FILE (NAME = ' + @LogicalName + ', FILENAME = ''' + @NewPath   + ''')'
  INSERT INTO @CmdTable SELECT @sql 
  DELETE FROM @FileTable WHERE [name] = @LogicalName 
END 
SELECT * FROM @CmdTable