How to move ms access database to SQL server

MOVE ACCESS TO SQL

A)  How to migrate access database to SQL server.

Steps:

1)      Copy all the access files from below path and keep it on another folder

2)      Run access migration tool and browse new access file path

3)      Set the target SQL version and click next.

 

4)      Click on Add databases and add access files and click next.

5) Select target SQL server and provide database name( new database will create). Click Next

6) Click on Link Tables and Click Next

7)      Click on Convert, Load and Migrate.

8)      Once migration done, please save the project and close.

9)      Now access database will act as front-end where SQL server will act as backend.

How to find out recent differential backup history in SQL Server

-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() and s.type<>'L' and s.type='I'-- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

How to fix orphaned SQL database users

To find out Orphaned users

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user then run below command:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by below command

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

update stats modified date in SQL server

SELECT  sp.stats_id ,
        OBJECT_NAME(s.object_id) AS tablename ,
        s.name ,
sp.last_updated,
        sp.rows ,
        sp.rows_sampled ,
        CAST(sp.rows_sampled AS NUMERIC) / CAST(sp.rows AS NUMERIC) * 100 AS Percentage_sampled ,
        CASE WHEN sp.modification_counter = 0 THEN 0
             ELSE CAST(sp.modification_counter AS NUMERIC)
                  / CAST(sp.rows AS NUMERIC) * 100
        END AS Percent_changed ,
        CASE WHEN sp.modification_counter = 0 THEN 'No Modifications'
ELSE
CAST(sp.modification_counter AS VARCHAR(20))
END AS Modification_counter
FROM    sys.stats AS s
        CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id)
        AS sp
WHERE   sp.last_updated <= DATEADD(DAY, -1, GETDATE())
        AND sp.rows > 10000

How to check fragmentation status in SQL server

IF OBJECT_ID('tempDB..#bbc') IS NOT NULL
    DROP TABLE #bbc

CREATE TABLE #bbc
    (
      DatabaseName VARCHAR(100) ,
      ObjectName VARCHAR(100) ,
      Index_id INT ,
      indexName VARCHAR(100) ,
      avg_fragmentation_percent FLOAT ,
      Page_Count INT ,
      IndexType VARCHAR(100) ,
      Action_Required VARCHAR(100) DEFAULT 'NA'
    )
go
INSERT  INTO #bbc
        ( DatabaseName ,
          ObjectName ,
          Index_id ,
          indexName ,
          avg_fragmentation_percent ,
 Page_Count,
          IndexType
        )
        EXEC master.sys.sp_MSforeachdb ' USE [?]

SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as ObjectName,

a.index_id, b.name as IndexName,

avg_fragmentation_in_percent,page_count, index_type_desc

-- , record_count, avg_page_space_used_in_percent --(null in limited)

FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a

JOIN sys.indexes AS b

ON a.object_id = b.object_id AND a.index_id = b.index_id

WHERE b.index_id <> 0 and avg_fragmentation_in_percent <>0'
go

UPDATE  #bbc
SET     Action_Required = 'Rebuild'
WHERE   avg_fragmentation_percent > 30
go

UPDATE  #bbc
SET     Action_Required = 'Reorganize'
WHERE   avg_fragmentation_percent BETWEEN 5 AND 30
go

DELETE #bbc
WHERE Page_Count < 25

go


SELECT  *
FROM    #bbc
WHERE   DatabaseName NOT IN ( 'master', 'model', 'msdb', 'tempdb' ) AND Page_Count > 10000
ORDER BY avg_fragmentation_percent

Find Index Name and columns

SELECT  S.NAME SCHEMA_NAME ,
        T.NAME TABLE_NAME ,
        I.NAME INDEX_NAME ,
        C.NAME COLUMN_NAME
FROM    SYS.TABLES T
        INNER JOIN SYS.SCHEMAS S ON T.SCHEMA_ID = S.SCHEMA_ID
        INNER JOIN SYS.INDEXES I ON I.OBJECT_ID = T.OBJECT_ID
        INNER JOIN SYS.INDEX_COLUMNS IC ON IC.OBJECT_ID = T.OBJECT_ID
        INNER JOIN SYS.COLUMNS C ON C.OBJECT_ID = T.OBJECT_ID
                                    AND IC.INDEX_ID = I.INDEX_ID
                                    AND IC.COLUMN_ID = C.COLUMN_ID
WHERE t.name = 'name'
ORDER BY I.NAME ,
        I.INDEX_ID ,
        IC.KEY_ORDINAL

GO

SELECT '['+Sch.name+'].['+ Tab.[name]+']' AS TableName,
Ind.[name] AS IndexName,
SUBSTRING(( SELECT ', ' + AC.name
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I
ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC
ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 0
ORDER BY IC.key_ordinal
FOR
XML PATH('')
), 2, 8000) AS KeyCols,
SUBSTRING(( SELECT ', ' + AC.name
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I
ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC
ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 1
ORDER BY IC.key_ordinal
FOR
XML PATH('')
), 2, 8000) AS IncludeCols
FROM sys.[indexes] Ind
INNER JOIN sys.[tables] AS Tab
ON Tab.[object_id] = Ind.[object_id]
INNER JOIN sys.[schemas] AS Sch
ON Sch.[schema_id] = Tab.[schema_id]
WHERE ind.name like 'idx_name_id_first_middle_last%'
ORDER BY TableName

How to find duplicate and overlapping indices in SQL server

;WITH CTE_INDEX_DATA AS (
       SELECT
              SCHEMA_DATA.name AS schema_name,
              TABLE_DATA.name AS table_name,
              INDEX_DATA.name AS index_name,
              STUFF((SELECT  ', ' + COLUMN_DATA_KEY_COLS.name + ' ' + CASE WHEN INDEX_COLUMN_DATA_KEY_COLS.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END -- Include column order (ASC / DESC)

                                  FROM    sys.tables AS T
                                                INNER JOIN sys.indexes INDEX_DATA_KEY_COLS
                                                ON T.object_id = INDEX_DATA_KEY_COLS.object_id
                                                INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS
                                                ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id
                                                AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id
                                                INNER JOIN sys.columns COLUMN_DATA_KEY_COLS
                                                ON T.object_id = COLUMN_DATA_KEY_COLS.object_id
                                                AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id
                                  WHERE   INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id
                                                AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id
                                                AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0
                                  ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal
                                  FOR XML PATH('')), 1, 2, '') AS key_column_list ,
          STUFF(( SELECT  ', ' + COLUMN_DATA_INC_COLS.name
                                  FROM    sys.tables AS T
                                                INNER JOIN sys.indexes INDEX_DATA_INC_COLS
                                                ON T.object_id = INDEX_DATA_INC_COLS.object_id
                                                INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS
                                                ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id
                                                AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id
                                                INNER JOIN sys.columns COLUMN_DATA_INC_COLS
                                                ON T.object_id = COLUMN_DATA_INC_COLS.object_id
                                                AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id
                                  WHERE   INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id
                                                AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id
                                                AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1
                                  ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal
                                  FOR XML PATH('')), 1, 2, '') AS include_column_list,
       INDEX_DATA.is_disabled -- Check if index is disabled before determining which dupe to drop (if applicable)
       FROM sys.indexes INDEX_DATA
       INNER JOIN sys.tables TABLE_DATA
       ON TABLE_DATA.object_id = INDEX_DATA.object_id
       INNER JOIN sys.schemas SCHEMA_DATA
       ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id
       WHERE TABLE_DATA.is_ms_shipped = 0
       AND INDEX_DATA.type_desc IN ('NONCLUSTERED', 'CLUSTERED')
)
SELECT
       *
FROM CTE_INDEX_DATA DUPE1
WHERE EXISTS
(SELECT * FROM CTE_INDEX_DATA DUPE2
 WHERE DUPE1.schema_name = DUPE2.schema_name
 AND DUPE1.table_name = DUPE2.table_name
 AND (DUPE1.key_column_list LIKE LEFT(DUPE2.key_column_list, LEN(DUPE1.key_column_list)) OR DUPE2.key_column_list LIKE LEFT(DUPE1.key_column_list, LEN(DUPE2.key_column_list)))
 AND DUPE1.index_name <> DUPE2.index_name)