This is an old revision of the document!
Docker mssql Mssql docker
sudo docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<YourStrong@Passw0rd>" \
-p 1433:1433 --name sql1 -h sql1 \
-d mcr.microsoft.com/mssql/server:2019-latest
Пример работы с подключением из командной строки и выполнения восстановления
sudo docker exec -it sql3 "bash"
/opt/mssql-tools/bin/sqlcmd -S 172.31.28.155,1434 -U SA -P "passw0rd_123"
--- MSSQL. Sql to Get info data of base , space used, files
-- Get data for mssql
USE ISOJ
GO
-- main info about db
sp_helpdb ISOJ
GO
-- space used for DB
sp_spaceused
GO
-- all databases
SELECT a.name, a.is_temporal_history_retention_enabled
FROM sys.databases a;
--- MSSQL. Backup Restore
-- # Backup Restore/Mssql
-- delete database
USE [master];
--ALTER DATABASE [WAdmIsk] SET SINGLE_USER ;
--DROP DATABASE [WAdmIsk];
DECLARE @path NVARCHAR(255) = N'\\backup_share\log\testdb_'
+ CONVERT(CHAR(8), GETDATE(), 112) + '_'
+ REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
+ '.trn';
BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;
# backup FULL
/*
USE WAdmIsk;
BACKUP DATABASE WAdmIsk TO DISK = 'F:\Backups\temp\WAdmIskFull.bak' WITH FORMAT, MEDIANAME = 'SQLServerBackups', NAME = 'Full Backup of WAdmIsk';
BACKUP DATABASE [WAdmIsk] To DISK='F:\Backups\temp\WAdmdiff20210901_1709.bak' WITH DIFFERENTIAL, MEDIANAME = 'SQLServerBackups', NAME = 'Diff-Backup of WAdmIsk';
GO
# Restore
USE [master]
RESTORE DATABASE [WAdmIsk] FROM DISK = N'/mnt/backup/WAdmIsk_backup_2021_09_02_081951_4217315.bak' WITH FILE = 1, MOVE N'WAdmIsk' TO N'/var/opt/mssql/data/WAdmIsk.mdf', MOVE N'WAdmIsk_log' TO N'/var/opt/mssql/data/WAdmIsk_1.ldf', NORECOVERY, NOUNLOAD, STATS = 5
RESTORE DATABASE [WAdmIsk] FROM DISK = N'/mnt/backup/WAdmIsk_backup_2021_09_02_120927_8075561.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [WAdmIsk] FROM DISK = N'/mnt/backup/WAdmIsk_backup_2021_09_02_121428_1305931.trn' WITH FILE = 1, NOUNLOAD, STATS = 5
GO
-- проверка прогресса backup
SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE');
-- проверить корректность
RESTORE VERIFYONLY FROM DISK = 'C:\tmp\Server2008\isoj_2020.bak'
go
-- посмотреть информацию
RESTORE HEADERONLY
FROM DISK = N'C:\tmp\Server2008\isoj_2020.bak' ;
GO
-- восстановить с переносом файлов - с возвратом сообщения раз в 5 %
USE [master]
RESTORE DATABASE [WAdmIsk] FROM DISK = N'C:\tmp\Server2008\isoj_2020.bak' WITH FILE = 1,
MOVE N'isoj' TO N'd:\isoj\isoj.mdf', MOVE N'isoj_log' TO N'd:\isoj\isoj._1.ldf', NOUNLOAD, STATS = 5
bcp [ISOJ].[dbo].[lNLogData] OUT i:\lNLogData.txt -T -c -b 10000
https://stackoverflow.com/questions/56628/how-do-you-clear-the-sql-server-transaction-log
USE ISOJ
GO
DECLARE @FileName sysname = N'ISOJ';
DECLARE @TargetSize INT = (SELECT 1 + SIZE*8./1024 FROM sys.database_files WHERE name = @FileName);
DECLARE @Factor FLOAT = .995;
DECLARE @Inc INT = 0
WHILE @Inc < 10
BEGIN
SET @Inc += 1
SET @TargetSize *= @Factor;
DBCC SHRINKFILE(@FileName, @TargetSize);
DECLARE @msg VARCHAR(200) = 'Shrink file completed. Target Size: '+
CAST(@TargetSize AS VARCHAR )+ ' MB. Timestamp: ';
RAISERROR(@msg, 1, 1) WITH NOWAIT;
WAITFOR DELAY '00:00:01';
END;
-- reclail logs
USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
DBCC SHRINKFILE(yourdb_log, 200); -- unit is set in MBs
GO
Rebuild indexes
--https://stackoverflow.com/questions/32505775/rebuild-all-indexes-in-a-database
DECLARE @TableName VARCHAR(255)
DECLARE @SQL NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))+'.' + QUOTENAME(name) AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@SQL)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO