A method to backup all databases from a sql server by using tsql and Python can be achieved as follows:
- install Ananconda
- create an anaconda environment for example with conda create –name <myenv> –clone base or conda create -p <pathtoenviron> –clone base
- activate new environment with conda activate <myenv>
- install microsoft mssql-scripter with pip install mssql-scripter
- place the following windows batch file somewhere on your server
- make a tsql stored procedure with can execute the batch file
- make a job with a jobstep with the tsql code loop, see all code below
Rem ***********************************************************************************
Rem escriptdb.bat
Rem ***********************************************************************************
echo %1
echo %2
rem in python environment eerst pip install mssql-scripter indien nodig
call c:\programData\Anaconda3\condabin\conda.bat activate myenv
e:
cd E:\Backup\DatabasesScriptOnlyBackup
rem script all schema to a file wothout data!
mssql-scripter -S localhost -d %1 -f %2
-- ======================================================
-- Author: Archimedes van Wou
-- Create date: 20230907
-- Description: backup scriptonly van alle databases SP
-- Version: 20230907 AvW
-- ======================================================
CREATE OR ALTER procedure [dbo].[ExportDbScript2File]
(
@db varchar(200),
@bestand varchar(200)
)
as
begin
declare @commando varchar(350);
set @commando=cast('cd.. &&"Q:\Projects\ExportDB\escriptdb.bat" '+@db +' '+@bestand as varchar(350))
EXEC xp_cmdshell @commando EXECUTE AS LOGIN='DOMAIN\SQLSERVER_USER';
end;
-- ======================================================
-- Author: Archimedes van Wou
-- Create date: 20230907
-- Description: backup scriptonly van alle databases
-- Version: 20230907 AvW
-- ======================================================
DECLARE @constBackupLocatie AS VARCHAR(200)='Q:\Backup\DatabasesScriptOnlyBackup\'
DECLARE @i AS INT=1;
DECLARE @Bestand AS VARCHAR(200);
DECLARE @db AS VARCHAR(30)
DROP TABLE IF exists #t_db
SELECT
Id=Row_number() over (order by name)
,DbName=Name
,Bestand=@constBackupLocatie+(FORMAT (getdate(), 'yyyyMMdd') )+'_'+name+'.sql'
,IsDone=0
INTO #t_db
FROM sys.databases
WHERE NAME NOT IN ('master','tempdb','model','msdb')
WHILE @i<=(SELECT MAX(id) FROM #t_db)
BEGIN
SELECT @Bestand=#t_db.Bestand,@db=#t_db.DbName FROM #t_db WHERE id=@i;
--select @Bestand,@db ;
EXEC DWH_DB.[dbo].[ExportDbScript2File] @db=@db,@bestand= @Bestand;
UPDATE #t_db SET IsDone=1;
SET @i=@i+1;
END;
--EOF
