• Search for:

    Yearly Archives: 2021

    1. Home  - 
    2. 2021
      3. ( Page4 )
    11 Jul, 2021
    T-SQL script to get last restored date of SQL Server Database 0 (0)
    MSSQL , ,

    Below query can be used to find out database last restored date.

    SELECT [bs].[database_name] as [source_database_name]
,[rh].[destination_database_name],
[rh].[Restore_date],
[bmf].[physical_device_name] as [Backup_file_used],
[rh].[user_name] AS [RestoredBy],
datediff(minute,bs.backup_start_date,bs.backup_finish_date) as RestoreDurationMin,
CAST(bs.backup_size/1024.0/1024 AS DECIMAL(10, 2)) AS BackupSizeMB
FROM msdb..restorehistory rh
INNER JOIN msdb..backupset bs
    … Read the rest
    3 Jul, 2021
    T-SQL Script to change compatibility level of all databases in SQL Server 0 (0)
    MSSQL , ,

    declare @CompatibilityLevelUpdate varchar(255)
    set @CompatibilityLevelUpdate = "
    select @CompatibilityLevelUpdate =@CompatibilityLevelUpdate +
    'alter database [' + name + '] set compatibility_level =150;' + char(15)
    from sys.databases
    where database_id4 — exluding system databases
    exec (@CompatibilityLevelUpdate)

    — Verify compatibility level

    select name … Read the rest

    1 Jul, 2021
    T-SQL Script to restore multiple databases using backup files in SQL Server 1 (1)
    MSSQL , , ,

    Below query can be used to restore multiple SQL Server databases using backup files.

    SET NOCOUNT ON
DECLARE
@foldername Varchar (255) = 'D:\MSSQL\MSSQLSERVER', --path where .bak files are placed
@DataFileLocation Varchar(255) = 'E:\MSSQL' , -- Optional, Specifies the folder for
    … Read the rest
    19 Jun, 2021
    How to check when procedure was last executed in SQL Server 0 (0)
    MSSQL , ,

    select
    db_name() [database name]
    ,[schema name] = SCHEMA_NAME([schema_id])
    ,o.name
    ,create_date [create date]
    ,modify_date [last modify date]
    ,ps.last_execution_time
    from sys.dm_exec_procedure_stats ps
    inner join
    sys.objects o
    ON ps.object_id = o.object_id
    where o.type = 'P'
    –and o.schema_id = schema_name(schema_id)
    and o.name = 'MonTlogUsage'    … Read the rest