DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'mydb'
-- Get Backup History
SELECT TOP (30) s.database_name, s.name, s.description
,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
ORDER BY backup_start_date DESC
,backup_finish_date
Month: May 2022
Log shipping with Standby/ReadOnly
Run the below on the Primary
BACKUP DATABASE LogRestoreDB
TO DISK = '\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_full1.bak'
WITH INIT, FORMAT,COMPRESSION
INSERT INTO t1(Id)
VALUES (1)
BACKUP LOG LogRestoreDB
TO DISK = '\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log1.trn'
WITH INIT, COMPRESSION
INSERT INTO t1(Id)
VALUES (2)
BACKUP DATABASE LogRestoreDB
TO DISK = '\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log2.trn'
WITH INIT, COMPRESSION
INSERT INTO t1(Id)
VALUES (3)
BACKUP DATABASE LogRestoreDB
TO DISK = '\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log3.trn'
WITH INIT, COMPRESSION
Run the below on the Secondary to restore the database in standby/read-only mode
ALTER DATABASE LogRestoreDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master];
RESTORE DATABASE [LogRestoreDB]
FROM DISK = N'\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_Full.bak'
WITH STANDBY = '\\SQLSERVER01\SQLBackup\Standby\LogRestoreDB_Standby.tuf';
GO
--Check the table t1
-- SELECT * from t1;
USE [master];
RESTORE DATABASE [LogRestoreDB]
FROM DISK = N'\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log1.trn'
WITH STANDBY = '\\SQLSERVER01\SQLBackup\Standby\LogRestoreDB_Standby.tuf';
GO
--Check the table t1
-- SELECT * from t1;
USE [master];
RESTORE DATABASE [LogRestoreDB]
FROM DISK = N'\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log2.trn'
WITH STANDBY = '\\SQLSERVER01\SQLBackup\Standby\LogRestoreDB_Standby.tuf';
GO
--Check the table t1
-- SELECT * from t1;
USE [master];
RESTORE DATABASE [LogRestoreDB]
FROM DISK = N'\\SQLSERVER01\SQLBackup\Temp\LogRestoreDB_log3.trn'
WITH STANDBY = '\\SQLSERVER01\SQLBackup\Standby\LogRestoreDB_Standby.tuf';
GO
--Check the table t1
-- SELECT * from t1;
Docker Commands to get started
Docker pull: Download an image from the docker repository
Docker run: Create a container from the image
Docker start: Start a container that has been stopped
Docker stop: Stop a container that is running
Docker help: Show help for Docker commands
Docker Commands to Manage Containers
Docker ps: Show running containers
Docker ps -a: Show all container
Docker rm: Remove stopped container
Docker Exec: Execute a command in a running container
Docker cp: Copy files to and from a container
Docker Commands to Manage Images
Docker images: Show and work with images
Docker rmi: Remove image
Docker commit: Create an image from a container
Docker Commands to do things
docker run -p 1433:1433 -e SA_PASSWORD=passw0rd -e ACCEPT_EULA=Y mcr.microsoft.com/mssql/server:2019-latest
docker exec -it sql_preprod mkdir /var/opt/mssql/backup
docker cp c:\temp\db1.bak sql_preprod:/var/opt/mssql/backup
docker exec -it sql_preprod /bin/bash
Database Blog 