Get database schema changes history (by T-SQL query)

DECLARE @db_name TABLE

(

       db_name_ID int identity,

       DBName sysname

);

DECLARE @objects TABLE

(

       db_name sysname,

       ob_name sysname,

       type nvarchar(60),

       date datetime,

       s_name sysname

);

DECLARE @cnt int;

DECLARE @DBName sysname;

INSERT INTO @db_name

       (DBName)

       VALUES ('Your_Database_Name')

       -- OR SELECT name FROM sys.databases WHERE database_id <> 2;

SELECT @cnt = COUNT(*) FROM @db_name;

WHILE @cnt > 0

BEGIN

       SELECT @DBName = DBName FROM @db_name WHERE db_name_ID = @cnt;

      

       INSERT INTO @objects

              exec('use ['+@DBName+']; SELECT '''+@DBName+''' as db_name,o.name as object_name,o.type_desc, o.modify_date, s.name as schema_name from sys.all_objects o left outer join sys.schemas s

                             on (o.schema_id = s.schema_id) where modify_date > ( GETDATE() -3000);');

      

       SET @cnt = @cnt-1;

END

SELECT *

FROM @objects

       --where ob_name = 'Your_Object_Name'

ORDER BY date desc, db_name, s_name, ob_name;

"Automatic" extended events - system_health

After SQL Server installation, A default extended events session - system health – is created.

System health runs without any noticeable performance effects, and collect some important events:

• Deadlocks
• Sessions that have waited on locks, waits, etc.
• More….

See all in:

https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/use-the-system-health-session

I use it to catch deadlocks graph, and it was very helpful!

You can also filters events.

If you don’t remember the exact event name, you can type part of it:

After you get the results and see the event you need, you can specify the filter by it:

Export and Import / move a maintenance plan from one server to other

The maintenance plan stored in the sql server database engine menu under Management --> maintenance plan:

In order to move it to other server, we should do few steps:

1. Connect to the Integration Services of the server where the maintenance plan:

2. Right click on the maintenance plan --> Export Package:

3. Fill the package path (where the dtsx file will be saved) and press OK:

4. Connect to the Integration Services of the server where the maintenance plan will be imported to,

As before – with the same ordering – Right click on the maintenance plan --> Import Package

5. Add the package (press on the "…" button near the pacage path:

That it!

Connect to the database engine and make sure that you see the maintenance plan also there.

Note: if the maintanance plan run from a job – the job should be imported also (of course...).