Categorie: sql

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