Creating, backing up, and restoring a database are critical databases administration tasks that we must perform regardless of Operating System platform. Good news! If you had done these steps in Windows, the same steps apply in Linux. You just need to be aware of the file path structure.

 Creating a Database and Verifying Its Data and Log Files

To create a database and verify its data and log files, perform the following steps:

1. Connect to your Ubuntu Server from your PuTTY remote terminal if not already connected.

2. Connect to your SQL Server:

sqlcmd -S localhost -U USERNAME -P ‘PASSWORD’

3. Create the database, sqldbanow:

create database DVSQLRocks
go

4. Specify the database to use:

use sqldbanow
go

you will see the message “Changed database context to ‘DVSQLRocks'”.

5. Create a table, major_conference, and insert two rows of data:

create table major_conference (id int, name nvarchar(50), start_date datetime)
go
insert into major_conference values (1, ‘SQL Saturday LA’, ‘2019-06-15’)
insert into major_conference values (2, ‘SQL PASS Summit’, ‘2019-11-05’)
insert into major_conference values (3, ‘AWS Re:Invent’, ‘2019-12-02’)
go

Figure 1: Create a database and table and insert data into a table


6. Find the location of the data and log files for the newly created databases:


select DB_NAME(database_id), physical_name from sys.master_files
where DB_NAME(database_id) = ‘sqldbanow’
go

Figure 2: Query the sys.master_files to find the location of the data and log files

Backing up a Database:

To backup a database, perform the following steps:

1. Connect to your SQL Server if not already connected:

sqlcmd -S localhost -U USERNAME -P ‘PASSWORD’

2. Backup our sample database, sqldbanow:

backup database [sqldbanow] to disk = N’/var/opt/mssql/data/sqldbanow_20210127.bak’
go

Figure 3: Backup a database

If executed successfully, you will see the confirmation message as in Figure 3.

Restoring up a Database

To restore a database, perform the following steps:

1. Find the location of the backup file. In our example, it is 

/var/opt/mssql/data/sqldabanow_20210127.bak.

2. Connect to your SQL Server if not already connected:
sqlcmd -S localhost -U USERNAME -P ‘PASSWORD’

3. Change the database to master:

use master
go

Just as in SQL on Windows, you can’t restore a database if it is in use.

4. Restore the database. In this example, we will restore and replace the existing sqldbanow databases:
use master
go
restore database [sqldbanow] from disk = N’/var/opt/mssql/data/sqldbanow_20210127.bak’ with norecovery, replace





Figure 4: Restore a database





How many IP Addresses we require for setting up Active\Active SQL Server cluster with Analysis services?

•  2 Windows nodes – Public
•  2 Private IP Addresses – Private
• 1 Windows Virtual Cluster Name
• 1 MSDTC (Optional)
• 1 SQL Server Virtual Network Name
• 1 SQL Server Analysis Services

How many IP Addresses we require for setting up Active\Passive SQL Server cluster?

• 2 Windows nodes – Public
• 2 Private IP Addresses – Private
• 1 Windows Virtual Cluster Name
• 1 MSDTC (optional)
• 1 SQL Server Virtual Network Name

SQL Server DBA: Database in Recovery Pending state

 I have recently encountered an issue where one of the databases suddenly moved to Recovery Pending status after SQL Server fail-over.

I checked the database and it is in Recovery Pending status. Recovery Pending means that recovery cannot be started. Until the cause is fixed, recovery cannot run and the database cannot come online. I then checked the drives in the server and all drives are accessible.Then

Just i took database offline and bring back to online and  database is automatically recovered.

OR

if multiple database are in recovery pending after SQL Fail over or  SQL Server Restart..

I had to restart the SQL Server service manually and the database is automatically recovered.

SQL Server script to get a list of all jobs that access a database in SQL server?

 This will work for Agent jobs that have T-SQL job steps pointing to a database.

Databases Last Accessed

 Running the script below will show you the last access date and time of all databases since the day sql server was rebooted

-- Get Last Restart time

SELECT

crdate as 'Last Rebooted On'

FROM

sysdatabases

WHERE name = 'tempdb'

go

 

-- Get last database access time (Null - no access since last reboot)

SELECT name, last_access =(SELECT X1= max(LA.xx)

FROM ( SELECT xx =

MAX(last_user_seek)

WHERE MAX(last_user_seek)is not null

UNION ALL

SELECT xx = max(last_user_scan)

where max(last_user_scan)is not null

UNION ALL

SELECT xx = max(last_user_lookup)

WHERE MAX(last_user_lookup) is not null

UNION ALL

SELECT xx =max(last_user_update)

WHERE MAX(last_user_update) is not null) LA)

FROM master.dbo.sysdatabases sd

LEFT OUTER JOIN sys.dm_db_index_usage_stats s

on sd.dbid= s.database_id

GROUP BY sd.name

ORDER BY name

Should I Keep Autoshrink On?

 It is not a good practice to keep Autoshrink on because it will cause indexes to become fragmented. If you want to reclaim free space then a better approach is to manually shrink the files and then rebuild the indexes.

By default AutoShrink feature is turned off in SQL Server

Configure Alerts for disk I/O errors using t-sql

 Use this script to create alerts for disk I/O errors.

Before running the script replace the <OperatorName> parameter with the actual operator that you would like to alert.

/*

PARAMETERS:

<OperatorName,sysname,Alerts> - Name of the Operator/Alias to alert.

@notification_method 1 - Bitmap of notification types/options: 1 = email,

2 = pager, 4 = netsend

*/

 

USE msdb

GO

 

EXEC msdb. dbo.sp_add_alert @name = N'823 - Read/Write Failure',

    @message_id = 823,

    @severity = 0,

    @enabled = 1,

    @delay_between_responses = 0,

    @include_event_description_in = 1

GO

EXEC msdb .dbo. sp_add_notification

        @alert_name=N'823 - Read/Write Failure' ,

        @operator_name =N'<OperatorName>' ,

        @notification_method = 1;   -- 1 for email

 

EXEC msdb. dbo.sp_add_alert @name = N'824 - Page Error',

    @message_id = 824,

    @severity = 0,

    @enabled = 1,

    @delay_between_responses = 0,

    @include_event_description_in = 1

GO

EXEC msdb .dbo. sp_add_notification

        @alert_name=N'824 - Page Error' ,

        @operator_name =N'<OperatorName>' ,

        @notification_method = 1;   -- 1 for email

 

EXEC msdb. dbo.sp_add_alert @name = N'825 - Read-Retry Required',

    @message_id = 825,

    @severity = 0,

    @enabled = 1,

    @delay_between_responses = 0,

    @include_event_description_in = 1

GO

EXEC msdb .dbo. sp_add_notification

        @alert_name=N'825 - Read-Retry Required' ,

        @operator_name =N'<OperatorName>' ,

        @notification_method = 1;   -- 1 for email