Backup all SQL server databases without data ddl scriptonly

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


Crypto bitvavo api per 10 seconds

I have saved the price of Bitcoin and Ethereum every 10 seconds on a Raspberry pi for several months. After a few months I couldn’t log in with ssh anymore and I decided it was time to harvest the data… I took the minisd card out of the PI, put it in a sdcard cartridge. And this one again in my laptop. First I looked in the terminal at the used size of the raspberry pi mini sdcard. For this I used df -m. Plenty of room still.

The data ran from September 9, 2022 to January 20, 2023. I copied it to my laptop. the ticker24.txt file contained the last actively filled data, after a boundary the data is stored in an archive text file. So I had 135 of those, see below.

ticker24.txt
ticker24.txt_2023012000011674172800
ticker24.txt_2023011900011674086400

de code to save these files per 10 seconds stands below, a service with a bash file used in a linux environment:

goud.service

[Unit]
Description=ververs data
After=graphical.target
Wants=goud.timer

[Service]
ExecStart=/bin/sh /home/usert/goud/goud.sh
Type=oneshot
User=usert
WorkingDirectory=/home/usert/goud/
Restart=always

[Install]
WantedBy=multi-user.target

goud.sh

cd /home/usert/goud
python3 ./kryp003kleinding.py

goud.timer

[Unit]
Description iedere 10 seconde

Requires=goud.service

[Timer]
Unit=goud.service
OnCalendar=*-*-* *:*:00,10,20,30,40,50

[Install]
WantedBy=timers.target

kryp003kleinding.py

kryp003kleinding.py
"""
@author: usert
@author: usert
https://github.com/bitvavo/python-bitvavo-api
https://ethereum.org/en/developers/tutorials/
werkt met service en timer (10 seconde)

"""
from python_bitvavo_api.bitvavo import Bitvavo
from datetime import date
import os
import json

d = {}
RijZichtbaar=5
with open('./hidden/credentials.txt') as f:
    for line in f:
        (key, val) = line.split("|")
        d[key] = val.replace("\n","")
        if d[key]=='False' :
            d[key]=False
        if d[key]=='True':
            d[key]=True
        if key=="ACCESSWINDOW" :
            d[key]=int(d[key])

bitvavo = Bitvavo(d)
limit = bitvavo.getRemainingLimit()

def ticker24h_vullen(bbParam={"market" : "ETH-EUR"},fileName="./data/ticker24.txt"):

response = bitvavo.ticker24h(bbParam)

with open(fileName,"a") as f:
    f.write(json.dumps(response))
    f.write('\n')
    if os.stat(fileName).st_size>1000000:
        newFileName=fileName+"_" +date.today().strftime("%Y%m%d%H%m%s")
        os.rename(fileName,newFileName)

if __name__=='__main__':
    if limit>10:
        ticker24h_vullen()
        ticker24h_vullen(bbParam={"market" : "BTC-EUR"})
    else:
        with open("./data/log.txt","a") as f:
        f.write("limiet is te laag")
        f.write(str(limit))
        f.write('\n')
#EOF

to harvest the files on serverside I used enabled everything with the linux bash commands below:

-Using systemd, create a .service file in /etc/systemd/system directory, containing the service’s information and commands.
-Put the .timer file in /etc/systemd/system directory, containing the timer’s information and schedule.
-Use the following commands to enable and start the service and timer:
sudo systemctl enable –now myservice.service
sudo systemctl enable –now mytimer.timer

Below the commands I used for testing the service:
-journalctl -u [service_name]
-or watch the output of man journalctl

To find out what I used at the time to achieve the above, I also used chat gpt, see https://chat.openai.com/chat. This is an AI that gives a pretty good answer when I compare it with my memory.
To see this in chat gpt, ask for:
-how can i install a service with timer on linux?
-how to show logging from a system3 service?
-how do I install a ssh server on a Raspberry PI.
-how to secure a raspberry pi installation

To be able to use my old Raspberry PI at all, I had to reinstall it fresh with a more recent operating system:
-first install ubuntu imager on the laptop
-then put the mini sdcard of the raspberry pi in the laptop and install a new operating system
-then disable default password and account! enable firewall if present. systemctl ufw enable.
-Update your system regularly: Use the command sudo apt-get update && sudo apt-get upgrade to update your system.
-Limit remote access: If you need to access your Raspberry Pi remotely, it’s recommended to use a secure protocol such as SSH. Also, you should limit remote access to only specific users or IP addresses.
-Use encrypted storage
-Use intrusion detection system: you can use a software like OSSEC to monitor your system for intrusion attempts, malware and other suspicious activity.

Installing SSH with keys on a Raspberry Pi is a more secure way of accessing your device remotely compared to using a password. Here are the steps to install and enable SSH with keys on your Raspberry Pi:
-Make sure your Raspberry Pi is connected to the internet and powered on.
-Open the terminal on your Raspberry Pi or connect to it via SSH from another computer.
-Update the package list:
sudo apt-get update
-Install the SSH server:
sudo apt-get install ssh
-Start the SSH service:
sudo service ssh start
-Enable SSH on boot:
bash
sudo systemctl enable ssh
-Check the status of the SSH service:
sudo service ssh status
Generate the SSH keys:
ssh-keygen -t rsa

Copy the public key to the Raspberry Pi:
ssh-copy-id pi@[IP address of Raspberry Pi]
You can check the authorized_keys file in the /home/pi/.ssh/ directory to ensure that the key has been added. Now you can access the Raspberry Pi via SSH using the keys, instead of a password.
-You can use ifconfig command to check your Raspberry Pi IP address

Please note that, SSH default port is 22, but you can change it to a custom value to increase security.

How to download films from youtube

A time ago I was looking for a Python library to download a film for a joke in an offline presentation. This is the code which achieved the download part.

installation in python (anaconda) run in the terminal conda install pytube3

In the Python editor paste the code below and run it.

from pytube import YouTube
yt = YouTube(“linkToFilm”)
yt.streams.first().download()