db:mssql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision		 Previous revision
db:mssql [2021/09/16 20:59] – [MSSQL. Docker] admindb:mssql [2021/11/03 09:53] (current) – admin
Line 2: Line 2:
 ===== MSSQL. Info ===== ===== MSSQL. Info =====
  
 +  * https://habr.com/ru/post/316064/ - информация по mssql  
 +  * mssql video traning http://www.sqlservervideos.com/video/sql2528-log-files/ 
 +  * https://sqlserver-kit.org/ru - каталог инструменты,
   * https://my.visualstudio.com/Downloads/Featured?mkt=ru-ru - скачивание дистрибутивов   * https://my.visualstudio.com/Downloads/Featured?mkt=ru-ru - скачивание дистрибутивов
-  * https://habr.com/ru/post/316064/ - информация по mssql+  * https://docs.microsoft.com/ru-ru/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15 - SSMS - studio
   * https://sqlserverbuilds.blogspot.com/ - site with version / update    * https://sqlserverbuilds.blogspot.com/ - site with version / update 
   * [[https://www.microsoft.com/en-us/download/details.aspx?id=44278|MSSQL 2008 SP4]]   * [[https://www.microsoft.com/en-us/download/details.aspx?id=44278|MSSQL 2008 SP4]]
   * mssql update pack https://support.microsoft.com/en-us/topic/kb968382-how-to-obtain-the-latest-service-pack-for-sql-server-2008-8b8113f9-dc3e-4f79-fecc-e1b8d96dd6fe   * mssql update pack https://support.microsoft.com/en-us/topic/kb968382-how-to-obtain-the-latest-service-pack-for-sql-server-2008-8b8113f9-dc3e-4f79-fecc-e1b8d96dd6fe
-  * mssql video traning http://www.sqlservervideos.com/video/sql2528-log-files/ 
   * sp_command help https://irfanworld.wordpress.com/tag/sp_helpdb/   * sp_command help https://irfanworld.wordpress.com/tag/sp_helpdb/
  
Line 16: Line 17:
 sudo docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=passw0rd_123" \ sudo docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=passw0rd_123" \
  -p 1434:1433 --name sql1 -h sql1 -v /mnt:/mnt/volume \  -p 1434:1433 --name sql1 -h sql1 -v /mnt:/mnt/volume \
 + -v /opt/docker/mssql/isoj:/var/opt/mssql/data
  -d mcr.microsoft.com/mssql/server:2019-latest  -d mcr.microsoft.com/mssql/server:2019-latest
 </code> </code>
Line 59: Line 61:
 <code SQL> <code SQL>
 -- # Backup Restore/Mssql -- # Backup Restore/Mssql
 +
 +-- get backup set
 +SELECT TOP 100
 +  name,
 +  type, *
 +FROM
 +  msdb..backupset
 +WHERE 1=1 
 + -- and database_name like '%adm%'
 +ORDER BY
 +backup_set_id DESC
  
 -- delete database -- delete database
Line 73: Line 86:
  
 # backup full # backup full
-/*+/* */
 USE WAdmIsk; 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\WAdmIskFull.bak' WITH FORMAT, MEDIANAME = 'SQLServerBackups', NAME = 'Full Backup of WAdmIsk';
Line 109: Line 122:
 </code> </code>
 ++++ ++++
 +
 +
 +++++ --- MSSQL. turn on agent|
 +<code SQL>
 +-- Get data for mssql
 +sp_configure 'show advanced options', 1;
 +GO
 +RECONFIGURE;
 +GO
 +sp_configure 'Agent XPs', 1;
 +GO
 +RECONFIGURE
 +GO
 +</code>
 +++++
 +
 +
 +++++ --- SCRIPT - for archive all files except ZIP|
 +<code BASH>
 +Function Zip
 +{
 + Param
 + (
 + [string[]]$toBeZipped
 + )
 +    if ($null -eq $toBeZipped) {return}
 + $CurDir = Get-Location
 + Set-Location "C:\program files\7-zip\"
 + foreach ( $f in $toBeZipped)
 +        {
 +        echo "$(Get-Date -Format o) zip file $f "
 +    .\7z.exe A -tzip "$f.zip" $f | Out-Null
 +        }
 + Set-Location $CurDir
 +}
 +
 +
 +Function ZipFiles
 +{
 + Param
 + (
 + [string]$zipFile
 + ,
 + [string[]]$toBeZipped
 + )
 + $CurDir = Get-Location
 + Set-Location "C:\program files\7-zip\"
 + .\7z.exe A -tzip $zipFile $toBeZipped | Out-Null
 + Set-Location $CurDir
 +}
 +
 +
 +
 +# Code 
 +# get-childitem $path -recurse -exclude *.cs,*.tt,*.xaml,*.csproj,*.sln,*.xml,*.cmd,*.txt
 +$bckpdir=$args[0]
 +if ($null -eq $bckpdir){ 
 + echo "$(Get-Date -Format o) no directory in param in start"
 + exit 1
 +}
 +
 +$CheckFile = Test-Path -Path $bckpdir
 +
 +
 +if (!($CheckFile))  {
 + echo "$(Get-Date -Format o) $bckpdir  directory not exist "
 + exit 1 
 +} 
 +echo "$(Get-Date -Format o) --- start compress $bckpdir"
 +
 +
 +
 +$files = Get-ChildItem $bckpdir\*.* -exclude *.zip | Where-Object {$_.CreationTime -lt ((Get-date).adddays(+1))}
 +Zip $files
 +foreach ( $f in $files)
 +{
 + If(Test-Path "$f.zip")
 +   {
 +     Remove-Item $f
 +     echo "compress ok Delete $f"
 +   }
 +}
 +
 +echo  "$(Get-Date -Format o) === stop"
 +</code>
 +++++
 +
 +
 +++++ --- SCRIPT - for archive ZIP all files except last one for sort in time|
 +<code BASH>
 +Function Zip
 +{
 +  Param
 +  (
 +    [string[]]$toBeZipped
 +  )
 +    if ($null -eq $toBeZipped) {return}
 +  $CurDir = Get-Location
 +  Set-Location "C:\program files\7-zip\"
 +  foreach ( $f in $toBeZipped)
 +        {
 +        echo "$(Get-Date -Format o) zip file $f "
 +     .\7z.exe A -tzip "$f.zip" $f | Out-Null
 +        }
 +  Set-Location $CurDir
 +}
 +
 +
 +Function ZipFiles
 +{
 +  Param
 +  (
 +    [string]$zipFile
 +    ,
 +    [string[]]$toBeZipped
 +  )
 +  $CurDir = Get-Location
 +  Set-Location "C:\program files\7-zip\"
 +  .\7z.exe A -tzip $zipFile $toBeZipped | Out-Null
 +  Set-Location $CurDir
 +}
 +
 +
 +
 +# Code 
 +# get-childitem $path -recurse -exclude *.cs,*.tt,*.xaml,*.csproj,*.sln,*.xml,*.cmd,*.txt
 +$bckpdir=$args[0]
 +if ($null -eq $bckpdir){ 
 + echo "$(Get-Date -Format o) no directory in param in start"
 + exit 1
 +}
 +
 +$CheckFile = Test-Path -Path $bckpdir
 +
 +
 +if (!($CheckFile))  {
 + echo "$(Get-Date -Format o) $bckpdir  directory not exist "
 + exit 1 
 +} 
 +echo "$(Get-Date -Format o) --- start compress $bckpdir"
 +
 +
 +
 +$files = Get-ChildItem $bckpdir\*.* -exclude *.zip | sort -Property CreationTime
 +
 +if ($files -is [array])
 +{
 +$files = $files[0..($files.Count - 2)]
 +Zip $files
 +foreach ( $f in $files)
 +{
 + If(Test-Path "$f.zip")
 +   {
 +     Remove-Item $f
 +     echo "compress ok Delete $f"
 +   }
 +}
 +}
 +else
 +{
 +   echo  "$(Get-Date -Format o) return is not array - only one file $files"
 +}
 +echo  "$(Get-Date -Format o) === stop"
 +</code>
 +++++
 +
 +
 +++++ --- SCRIPT - clear age files in directory |
 +<code BASH>
 +$bckpdir=$args[0]
 +if ($null -eq $bckpdir){ 
 + echo "$(Get-Date -Format o) no directory in param in start"
 + exit 1
 +}
 +
 +$numdays=$args[1]
 +if ($null -eq $numdays -or -not $numdays -match '^[0-9]+$' -or $numdays -lt 3 ){ 
 + echo "$(Get-Date -Format o) no days in integer or less than 3 in param "
 + exit 1
 +}
 +
 +
 +$CheckFile = Test-Path -Path $bckpdir
 +
 +
 +if (!($CheckFile))  {
 + echo "$(Get-Date -Format o) $bckpdir  directory not exist "
 + exit 1 
 +} 
 +echo "$(Get-Date -Format o) --- start clear $bckpdir keep $numdays days"
 +
 +$files = Get-ChildItem –Path $bckpdir | Where-Object {($_.CreationTime -lt (Get-Date).AddDays(-$numdays))} 
 +if ($null -eq $files){ 
 + echo "$(Get-Date -Format o) === stop no files to delete"
 + exit 0
 +}
 +echo $($files| Format-Table  -HideTableHeaders)
 +$files | Remove-Item
 +</code>
 +++++
 +
 +
  
 ===== MSSQL.bulk copy to file ===== ===== MSSQL.bulk copy to file =====
Line 203: Line 418:
 https://www.sqlservercentral.com/blogs/setting-up-a-server-side-trace https://www.sqlservercentral.com/blogs/setting-up-a-server-side-trace
  
 +===== MSSQL. syncronize tables =====
 +
 +++++Check syncrhonize|
 +<code SQL>
 +
 +-- Sector  
 +select 'cappeal', 
 +year(case when c.date is null then co.date else c.date end) as  year,
 +count(*) as cnt,
 +sum(case when co.id is null then 1 else 0 end) as cntForLoad
 +from ISOJ2018.dbo.cAppeal c
 +full outer join isoj.dbo.cAppeal co on co.id=c.id
 +-- where co.id is null
 +group by rollup(year(case when c.date is null then co.date else c.date end)
 +);
 +</code>
 +++++
 +
 +++++ Syncronize tables|
 +<code SQL>
 +USE ISOJ;
 +  ---  synchronize tables
 + 
 +    DECLARE @DATABASE NVARCHAR(255)   
 +    DECLARE @TABLE NVARCHAR(255)  
 +    DECLARE @cmd NVARCHAR(1000)  
 + 
 +
 + -- '[' + table_catalog + '].[' + table_schema + '].[' +  table_name + ']' as tableName 
 + DECLARE DatabaseCursor CURSOR READ_ONLY FOR  
 +   SELECT top 2000 table_name  as tableName 
 +    FROM [ISOJ].INFORMATION_SCHEMA.TABLES 
 +    WHERE table_type = 'BASE TABLE'
 + 
 +    OPEN DatabaseCursor  
 + 
 +    FETCH NEXT FROM DatabaseCursor INTO @TABLE  
 +    WHILE @@FETCH_STATUS = 0  
 +    BEGIN  
 + FETCH NEXT FROM DatabaseCursor INTO @TABLE 
 +
 +  --set @TABLE = 'cAppealSource'
 + 
 +       SET @cmd = '
 +INSERT INTO [isoj].[dbo].['+@TABLE+']
 +select top 1000000 *
 +from ISOJ2018.[dbo].['+@TABLE+'] c
 +where c.id in (
 + select c.id
 + from ISOJ2018.[dbo].['+@TABLE+'] c
 + left join isoj.[dbo].['+@TABLE+'] co on co.id=c.id
 + where co.id is null
 +)
 +order by c.DateModified asc;
 +    '   
 + 
 +          BEGIN TRY   
 +           
 +             PRINT @TABLE -- uncomment if you want to see commands
 +             EXEC (@cmd) 
 +          END TRY
 +          BEGIN CATCH
 +             PRINT '---'
 +             PRINT @cmd
 +             PRINT ERROR_MESSAGE() 
 +             PRINT '---'
 +          END CATCH
 +    
 + END  
 +    CLOSE DatabaseCursor   
 +    DEALLOCATE DatabaseCursor
 +
 +GO
 +</code>
 +++++
 ===== TSQL example ===== ===== TSQL example =====
 ++++ TSQL example| ++++ TSQL example|
  • db/mssql.1631825943.txt.gz
  • Last modified: 2021/09/16 20:59
  • by admin