• compressed file
• contains ddl and dml data
• doesnt support encrypted objects
• cant have any stored procs with string that contain $( character sequence reserved sequence
  • break up sequence ie 'test$(test' become 'test$' + '(test'
• https://learn.microsoft.com/en-us/azure/azure-sql/database/database-import
• sqlpackage
  • https://download.microsoft.com/download/7/c/8/7c877288-a0d9-4d22-b1dc-2086e262d82f/x64/DacFramework.msi
  • https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download
  • C:\Program Files\Microsoft SQL Server\160\DAC\bin
• turn off real time scan (anti virus)

1. run extract with sql-script-db.ps1 with validate set to true and raw objects set to true, only need tbl-triggers, functions and sp
   1. reason for this is to identify any procedure/triggers/functions that dont compile
2. once this is done examine results and fix stored procedures on database so that they can compile, ie add missing columns or modify code to not require them
   1. repeat step 1 and 2 until you get zero objects that can't compile
3. check sql code for any reserved varables ie... and fix on DB
4. run dacpac export
   1. make sure nothing is running on source DB
   2. upgrade source DB to high level for quicker export and set back to what it was once done
   3. cmd
5. run dacpac publish to managed instance
6.  

• https://github.com/microsoft/mssql-scripter

  • C:\Users\alliancetest\.mssqlscripter
  • mssql-scripter -S "kula.database.windows.net" -d Everest_Test -U ebsadmin -P xxx -r -f E:\temp\khula_test.sql --display-progress --enable-toolsservice-logging

 -- export
select export = concat('bcp ', DB_NAME(), '.',     
   SCHEMA_NAME(schema_id), '.', name,
   ' out "$path\data\', SCHEMA_NAME(schema_id), '_', name, '.bcp" ',
' -e "$path\export_log\', SCHEMA_NAME(schema_id), '_', name, '.log" ',
   ' -S "', '$server','"',
   ' -N -U "$username" -P "$password"',
' | Out-Null')
from sys.tables
where type = 'U'
and is_ms_shipped = 0
--and temporal_type in (0,2);


-- import
select import = concat('bcp "', '$target_db', '.',
   SCHEMA_NAME(schema_id), '.', name,
   '" in "$path\data\', SCHEMA_NAME(schema_id), '_', name, '.bcp" ',
' -e "$path\import_log\', SCHEMA_NAME(schema_id), '_', name, '.log" ',
   ' -S "', '$target_server','"',
   ' -N -U "$target_username" -P "$target_password"',
' -b 1000 -E -k -N -q',
' | Out-Null')
from sys.tables
where type = 'U'
and is_ms_shipped = 0
--and temporal_type in (0,2);

• disable anti-virus real time scan

placeholder

Exporting

• no encrypted objects, they need to be decrypted or removed prior to exporting.

SQL 2012

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

EXEC XP_CMDSHELL 'net use O: \\zagpebslab.file.core.windows.net\sqlshare  /user:"localhost\zagpebslab" "access_key"'

EXEC XP_CMDSHELL 'Dir O:' 

BACKUP DATABASE Everest_TEST
TO DISK = N'O:\ENS_Everest_Test_20230721_1806.bak'
WITH  COMPRESSION,
STATS = 10;

GO

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') 

GO

Importing

Modifying and Troubleshooting

• Get-FileHash 'C:\temp\lfs\LifeSense_Everest_Test2-2023-5-17-7-37\model.xml'
  • place in Origin.xml
    • <Checksum Uri="/model.xml">DEADD2734B789135ED627061A385FC2C1096718A7D3398658DF9097196C1F571</Checksum>

Recalculate Signature

• incubator/ebsphere.crm/ebs-devops-devops-sql/calc-signature-bacpac.ps1
• modify Origin.xml
  • Checksum attribute for model.xml