Category: SQL Server

Database Testing Checklist

Database Testing

  • Synchronization between the database and the values displayed in our client/web.
  • Query results, views, stored procedures, indexers Etc.
  • Data manipulation (Update, Delete, insert Etc.).
  • Database performance.
  • Data maintenance.
  • Table’s structure.
  • Data recovery.
  • Data integrity.
  • Others

Clean database testing

  • Verify clean database testing.
  • Input 1st data

Database system-level tests

  • Validate the DB behavior of any case of service failures (recovery, error handling Etc.).
  • Validate that all indexes are created when it can increase the system performance.
  • Validate that appropriate events are created ad sent to the EventVwr/trace log.
  • Validate that DB tables are created with informative and reasonable names.
  • Try to work when the storage is ‘0’ and the e database is in running state.
  • Perform your tests on different versions (SQL 2005, 2008, 2012 etc.).
  • Validate the software security model (User roles, permissions etc.).
  • Validate the connection strings against SQL/Win authentications.
  • Validate data migrations (Different Database, Cluster, etc.).
  • Validate the behavior of the system against SQL injections.
  • Validate date to DB when the server is loaded.
  • Try to work when the database server is down.
  • Try to work with difference instance.
  • Validate restore and backup plans.

Database Integration Testing

  • Check that all columns are set with the relevant data type (Bigint, int, string Etc.)
  • Check that all data is logically organized in the relevant DB tables.
  • Check that each data item is located under the relevant column.
  • Is there any irrelevant data in the software dedicated tables?
  • Check that each table contains the relevant data.
  • Try to insert invalid database values.
  • Verify the data encryption (if any).

Data field tests

  • Validate that “Allow Null” condition is not allowed in a place that result a software failure.
  • Validate that all tables are created with logical structure (Primary, foreign keys.)
  • Validate that “Allow Null” condition is set when you need to allow it.
  • Validate that mandatory fields are created, this issue is very important when you work with multiple tables that depends on each other.

Procedure tests

  • Validate that the data the affected by the procedure is changed as expected.
  • Validate that all procedures are triggered when they supposed to run.
  • Validate that all the conditions receive an appropriate date inputs.
  • Validate that all procedures are created with the relevant code.
  • Is there an appropriate error handling for a failed procedure?
  • Validate that all the loops receive an appropriate date inputs.
  • Validate the procedure’s parameters (types, names, etc.).
  • Test the SP while executing the code manually.
  • Validate important code with SQL profiler.
  • Validate that all procedures names
  • Run tests with missing parameters.

Database and software integration (Client, web Etc.)

  • Validate that the user data is saved when the user “Apply” or “Submit” the changes.
  • Try to insert “NULL” values on fields that doesn’t supposed to receive it.
  • Validate that the user receives the current result when pulling data.
  • Validate that transaction the data type boundaries (Minima Etc.)
  • Validate that empty spaces are not committed to the database.
  • Validate that the values displayed based on the database data.
  • Try to insert UNICODE on Unicode character strings.
  • Try to insert values that exceed the field boundaries.
  • Validate that transactions the negative data values.
  • Insert invalid date format on Date and time fields.
  • Validate that the data integrity is not affected when the “Apply” or “Submit transactions are failing during the process.
  • Validate that the “Roll Back” option is available when the DB transaction is failed in the middle.

Data checking

  • Create Data from frontend and check by Query
  • Delete Data from frontend and check by Query

When search value in all tables under a database

If someone wants to search by specific value in database, it’s difficult to search a value whole database. Below code easy way to find out

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = 'test@test.com.au'
 
CREATE TABLE #Output (TableLocation nvarchar(370), Details_Information nvarchar(3630))
 
SET NOCOUNT ON
 
DECLARE @SearchTableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @SearchTableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
WHILE @SearchTableName IS NOT NULL
 
BEGIN
    SET @ColumnName = ''
    SET @SearchTableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @SearchTableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )
 
    WHILE (@SearchTableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
         
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@SearchTableName, 2)
                AND    TABLE_NAME    = PARSENAME(@SearchTableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )
 
        IF @ColumnName IS NOT NULL
         
        BEGIN
            INSERT INTO #Output
            EXEC
            (
                'SELECT ''' + @SearchTableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @SearchTableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END   
END
 
SELECT TableLocation, Details_Information FROM #Output
 
DROP TABLE #Output

how to get restore history in sql server

SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

How to check how many users are connected to a SQL Server

You can retrieve information about the number of connected users to a SQL Server. It is also possible to check which users are connected to a particular database.

Few examples in below:

Example 1: To check all users and process connection


select @@servername as server, count(distinct usr) as users, count(*) as processes from

( select sp.loginame as usr, sd.name as db

 from sysprocesses sp join sysdatabases sd on sp.dbid = sd.dbid ) as db_usage

Example 2: To check individual users and process connection

select Users, count(distinct db) as Databases, count(*) as processes from

( select sp.loginame as Users, sd.name as db

 from sysprocesses sp join sysdatabases sd on sp.dbid = sd.dbid ) as db_usage

group by Users

order by Users

Example 3: To check database, user and process connections

select db, users, count(*) as processes from

( select sp.loginame as users, sd.name as db

 from sysprocesses sp join sysdatabases sd on sp.dbid = sd.dbid ) as db_usage

where db like('%')

group by db, users

order by db, users

How to enable FILESTREAM in SQL Server 2008 (After Install)

If you forgot to enable FILESTREAM when install SQL Server 2008.

Don’t worry about it. You can enable FILESTREAM later.

To enable filestream settings:

1.  Go to Start menu > All Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager.

2.  If asking for user account control then click yes

3. In the list of services, right-click SQL Server Services, and then click Open

4. Now go to Filestream tab and ticked ‘Enable FILESTREAM for Transact-SQL access’.

5. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

6. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data

7.  Click Apply

 8. Go to SQL Server Management Studio, click New Query to display the Query Editor

9. In Query Editor, enter the following Transact-SQL code:

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

 

10. Click Execute or Press ‘F5’ button

11. Make File-stream access level full

12. Restart the SQL Server service