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


Testquery to compare 2 tables

The most important thing when developing in a datawarehouse environment is testing. If I want to compare 2 tables, ‘old’ versus ‘new’. I often use the code below.

/*Testquery difference between 2 tables*/
--select table old union table new with extra fields
;With ca as (
    select *,typ='old',chk=-1
    from old
    union all
   select *,typ='neww',chk=1
   from neww
)--end ca
,cb as (--make pivottable and compare the rows
    select *, dif=isnull(old,0)+isnull(neww,0)
    from (
        sum(chk) for typ in ([old],[neww])
    ) b
)

-query on the dif field for differences
select * from cb
where dif<>0

If you are working in an sql environment, using an index is important. When missing I use often a clustered columnstore index. No thinking, just use it, very easy.

create clustered columnstore index n001 on old;
create clustered columnstore index n001 on neww;

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.

CoronaChartApp

Last week I tried to publish my CoronaChartApp in google play. Unfortunately … the app was immediately suspended… I didn’t know that Google, Apple and Amazon banned everything about covid-19 and corona from their stores.

I looked for an alternative store. There are many alternatives… Aptoide is an alternative one and f-droid too. In order for Aptoide to work, an app must first be installed outside a store by the unknown sources method. The Aptoide app then serves as a marketplace where you can probably put your app. In my apps I normally put ads from google’s admob, I suspect this doesn’t work with non-google play repositories since you have to link to google play. I have looked at an alternative to admob for a while but decided to leave this alone for a while. Google uber alles…

An alternative is to make the app open source via f-droid. Unfortunately I think you have to install something again and I don’t like that at this moment…maybe tomorrow…. It is strange that you used to have much less problems with that, but now that you are used to installing everything from google play, everything is suspicious when installing your apk file direct on your phone. Maybe more of a psychological thing.

Maybe I should just slightly rebuild the CoronaChartApp … typhus might be allowed…. For people who are interested in corona apps outside of google play aptiode has them… but not my app at this moment…

…A note about the current covid-19 charts that I just refreshed, Brazil has a scary graph that is increasing and seems to get out of hand. Especially if you realize that these are the registered cases. In the graphs over the past 5 days, Canada has pushed my own Netherlands out of 10th place.

Tuberculosis and Corona

In 1900, 10,000 people out of a population of 5 million died in the Netherlands every year by Tuberculosis (TB). By comparison, until now Corona killed 4,500 people out of a population of 17.1 million. TB is primarily known as lung disease, and it spreads through the air through coughing (sounds familiar?) or contaminated food. Contamination by drinking milk from sick cattle could also occur before pasteurization. Usually illness occurs within a year or 2 after infection, but it can also last 60 years or more. Before World War II, tuberculosis was incurable. The only treatment that existed was to let people take the fresh air from forests and mountains.

Today, 80% of TB cases occur in 6 countries: China, India, South Africa, Nigeria, Indonesia and Pakistan, in 2015 10.4 million people fell ill, 1.8 million of whom died. In Europa is relative free from TB nowadays.

At the moment, Dutch hospitals Radboud UMC and UMC Utrecht are conducting tests to see whether the BCG vaccine, which is a known drug against TB, also has an effect against COVID-19. The vaccine contains the innocent brother of the tuberculosis bacteria. The test started on April 16 and works with a group of 1600 people older than 60. One half gets the drug, the other half does not. The aim of the research is to see whether the risk of infection with the coronavirus decreases, or whether it reduces the severity of the symptoms if an infection does occur.

Is sociale distancing like retirement?

Office workers are expected to work from home if possible. Well that works fine… of course I don’t have the best equipment here at home, but since I got an old television from the attic, I have a better screen than at work … until my laptop breaks, of course … I noticed that the Microsoft teams and skype apps just fit at my hard disk but had to clean up a lot to have enough disk space for all my other hobbies… ..

Communication with colleagues is going well because the people you work with know where to find you. However, that will not apply to everyone. With certain people and processes where contact was also difficult in the old pre corona situation, this has become more difficult. … .And of course… bump into someone by chance is not possible. … so expanding work contacts with new people is more difficult .. Fortunately, Corona is almost defeated, right? If not, more social moments must be built into working life, otherwise the social organization will fall apart.

Working at home takes some time to getting used to, a few weeks ago you only saw your wife full-time during the holidays and right now you see each other almost every day … we get used to it… In the beginning we sat at 1 table but that does not work, we each have another type of job. Anyway, after a while you will organize your environment in such a way that you can sit there for 8 hours a day. How boring it sometimes can be, even for a computer man like me…

However, you feel less involved in society, no hobbies possible, rules regarding the distance when you walk outside … no longer going live tot your parents … although I will try this next week anyway.

Working from home offers the solution to the environmental problems. If we want to maintain this in society after Corona, it must be ensured that productivity remains the same in any case. And the question is also what should happen to those empty office buildings? All our pension money is in those buildings … Should they be filled with economic refugees as soon as the Corona virus strikes in the developing world? Or is this still a typical disease of (semi) western consumer countries? I don’t see a major pandemic in Africa yet.

However, dependence on large American companies is increasing. Do we want that? As technology becomes more and more important for the private sphere, these companies that fall under the American Patriot act become more powerful because a lot of economic information also leaks out, which provides a competitive advantage for the organisation or country who has all the data.

But maybe it is just the way it is.

The economy is also a patient

Social isolation does not work. The remedy is worse than the disease. At the moment we have had social isolation for a month in the Netherlands. Major events have been canceled. Bars and restaurants are closed, everyone works at home behind the computer if possible. People should keep 1.5 meters away from strangers. Groups of more than 3 people together are prohibited. The number of deaths and the number of new patients in intensive care units is currently decreasing. Bottleneck is the small number of ic beds per capita in the Netherlands. It will take a few more weeks before we are back to “normal” capacity utilization. It is expected that as soon as measures to prevent spread are lifted, the number of new cases will increase. Looking purely at prevention, this situation should last until there is a pill against this disease. And that can take another six months. And then hope that the disease does not mutate to a new variant, just like with the seasonal flu.

However, the economy has also become a patient due to these measures. The economic downturn is unprecedented. … Only not to measure according to the usual methods. New official figures for economic growth will not be available until May. Yet there is already an urgent need to know what is to come economically. The current situation is so unique that there are no economic models for it. According to the NOS (Dutch public broadcaster), economists are trying to get a grip by experimenting with so called ‘nowcasting’ instead of forecasting, such as counting the number of times a barrier at the port of Rotterdam opens in real time.
Perhaps the coming downturn of the economy can be compared to that of the Second World War. Demolishing something is easy but rebuilding it again is much more difficult when it takes too long. Simply put, do we choose the end of civilization in these regions or will we take a different approach?

The truth is that it is still the case that the average age of the people who die is 70 years old. So it is still an old people’s disease or deadly to the physically weak in this society. …..A bit of bad luck if it is your own grandpa or grandma. Or father and mother…. Last week, the blood bank reported that 3 percent of donors had Corona. A much higher percentage is required for group immunity. An intermediate variant between the current measures mentioned above and opening everything up is to ensure that the current measures only apply to people over 50 and those who are weak. So that the rest just get sick and group immunity arises. Additional measures such as supporting loneliness, having groceries delivered, etc. must then be taken. And maybe just knowingly and exclusively voluntarily infecting non-risk groups. I don’t see the latter as politically possible of course … But it would be nice if we had everything behind us within 1 month.

A learning moment is that air pollution has never been so low here. The daily traffic jam problem has also been solved. It would be nice for humanity if there would also be a balance between the ecological footprint of consumption in western and semi-western economies and what the earth can handle.

This is also a chance!

The prediction of Corona

A number of models can be used to predict Corona. The one that was easiest to use for me is the SIR model. SIR is based on parts of the population that go through certain phases. Infectious, Infected and Recovered or deceased. (Susceptible Infected Recovered, SIR). Conversion rates apply between these phases. e.g. with the Corona sickness, 1 person infects 2.3 other people. They also call this the R0. Using the literature on the internet and source code from Kaggle I made a number of new graphs that compare the theoretical contamination with the contamination realized. These charts are here. It looks like Italy is going in the right direction. I am working at better predictions.

This is Corona

OVID-19 is the disease transmitted by SARS_CoV2 virus. SARS_COV2 is the official name, when it did not yet have an official name it was often called nCoV-2019 or in China Wuhan-Hu-1 virus. The virus was first reported on December 30, 2019 in Wuhan, the capital of China’s Hubei province. At the outbreak in 2019 and early 2020, the first patients were mainly visitors and employees of the Huahan fish market in Wuhan, with disease transmission between coughing and sneezing.

SARS_CoV2 virus source: NIAID under this licence

Recent research from the WHO evaluated the survival of the COVID-19 virus on different surfaces and reported that the virus can remain viable for up to 72 hours on plastic and stainless steel, up to four hours on copper, and up to 24 hours on cardboard.

An infected person infects an average of 2.2 other people . The disease is characterized by flu-like symptoms, including fever, cough, shortness of breath, muscle pain, fatigue. See also my Corona statistics page for the symptoms.

Novel Coronavirus SARS-CoV-2