tech - User contributions [en]

Recompile ffmpeg, is it worth?

2026-02-22T15:14:46Z

Luca:

= Recompile FFmpeg: is it worth? =

== Overview ==
This article documents a controlled benchmark study aimed at evaluating whether recompiling '''FFmpeg''' with different compilers and optimization flags provides measurable performance benefits on a '''first-generation AMD Ryzen CPU'''.

The focus is on CPU-bound, real-world video transcoding workloads, not synthetic microbenchmarks.
All tests were performed on the same machine, using identical inputs and methodology, varying only the compiler and compilation flags.

== Test System Configuration ==

=== Hardware ===
* '''CPU:''' AMD Ryzen 7 1700 (Zen 1, 8 cores / 16 threads)
* '''RAM:''' 16 GB
* '''Storage:''' SSD
* '''Architecture:''' x86_64

=== Software Environment ===
* '''Operating System:''' Linux Mint 22.3 (Cinnamon, 64-bit)
* '''Debian base:''' trixie/sid
* '''Kernel:''' 6.8.0-90-generic
* '''CPU governor:''' performance (fixed during benchmarks)

=== Toolchain ===
* '''FFmpeg (system):''' 6.1.1-3ubuntu5
* '''GCC:''' 13.3.0
* '''Clang/LLVM:''' 18.1.3
* '''Assembler:'''
** NASM 2.16.01
** YASM 1.3.0

=== External Libraries ===
* '''x264:''' built locally from source and linked dynamically
(system x264 package not used)

== Test Material ==

All input files are unmodified Blender Foundation open movies.
Files were not trimmed, re-encoded, or altered in any way.
SHA-256 checksums were recorded to ensure bitwise-identical inputs across all tests.

{| class="wikitable"
! File
! Resolution
! FPS
! Codec
|-
| Sintel.2010.1080p.mkv
| 1920×818
| 24
| H.264 High
|-
| Big Buck Bunny 60fps 4K - Official Blender Foundation Short Film.mp4
| 1920×1080
| 60
| H.264 High
|-
| Tears of Steel - Blender VFX Open Movie.mp4
| 1728×720
| 24
| H.264 High
|}

== Benchmark Methodology ==

=== Transcoding Scenario ===
All benchmarks used a realistic end-to-end transcoding workflow:

* '''Video encoder:''' libx264
* '''Preset:''' slow
* '''Rate control:''' CRF 18
* '''Audio:''' copied bit-exact (-c:a copy)
* '''Threads:''' 16 (matching logical CPU threads)

Example command (simplified):

<syntaxhighlight lang="bash">
ffmpeg -i input \
-c:v libx264 -preset slow -crf 18 \
-c:a copy -threads 16 output.mkv
</syntaxhighlight>

=== Measurement ===
* Tool: <code>/usr/bin/time -v</code>
* Primary metric: '''wall clock time'''
* Secondary metrics:
** user CPU time
** peak RSS memory
* '''Three runs per test''', arithmetic mean reported
* Locale forced to <code>LC_ALL=C</code> to ensure numeric consistency

== Tested Configurations ==

=== Baseline ===
* System FFmpeg package (distribution build)

=== GCC Builds ===
* GCC <code>-O2</code>
* GCC <code>-O2 -march=znver1 -mtune=znver1</code>
* GCC <code>-O3</code>
* GCC <code>-O3 -march=znver1 -mtune=znver1</code>

=== Clang Builds ===
* Clang <code>-O2</code>
* Clang <code>-O2 -march=znver1 -mtune=znver1</code>

All FFmpeg builds:
* were linked against the same locally built x264
* used identical configure options except for compiler flags

== Results Summary ==

=== Average Encoding Time (wall clock, seconds) ===

{| class="wikitable"
! Video
! System
! GCC O2
! GCC O2 znver1
! Clang O2
|-
| Sintel
| 554.00
| 550.68
| 548.06
| '''547.48'''
|-
| Big Buck Bunny
| 598.33
| 595.53
| 592.65
| '''589.88'''
|-
| Tears of Steel
| 308.67
| 306.59
| 306.04
| '''305.26'''
|}

(Standard deviation across runs was consistently below 1 second.)

== Analysis ==

=== GCC ===
* Recompiling FFmpeg with '''GCC -O2''' provides a small but consistent improvement (~0.5–0.7%).
* Adding '''Zen1-specific tuning''' (<code>-march=znver1</code>) yields an additional ~0.3–0.5%.
* <code>-O3</code> does '''not''' consistently improve performance and may slightly degrade results depending on workload.

Best GCC configuration:
<pre>
-O2 -march=znver1 -mtune=znver1
</pre>

=== Clang ===
* '''Clang -O2''' outperforms all GCC configurations tested.
* CPU-specific tuning (<code>znver1</code>) does not improve results with Clang and can be marginally negative.
* Memory usage and stability remain comparable to GCC builds.

Best overall configuration:
<pre>
Clang -O2
</pre>

== Final Conclusion ==

On a first-generation AMD Zen processor (Ryzen 7 1700):

* Recompiling FFmpeg provides measurable but modest gains.
* The best results in this study were achieved using '''Clang -O2''', with improvements of approximately '''1–1.4%''' over the distribution build.
* GCC benefits slightly from CPU-specific tuning; Clang does not.
* No configuration produced dramatic gains, as most performance-critical paths in x264 are already hand-optimized in assembly.

=== Practical Recommendation ===
Recompiling FFmpeg is worthwhile only if:
* encoding is CPU-bound
* workloads are frequent or long-running
* maintaining a custom build is acceptable

For general desktop usage or GPU-based encoding, the system FFmpeg package remains the most practical choice.

Recompile ffmpeg, is it worth?

2026-02-22T15:10:19Z

Luca:

== Overview ==

This article documents a controlled benchmark study aimed at evaluating whether recompiling FFmpeg with different compilers and optimization flags provides measurable performance benefits on a first-generation AMD Ryzen CPU.

The focus is on CPU-bound, real-world video transcoding workloads, not synthetic microbenchmarks.
All tests were performed on the same machine, using identical inputs and methodology, varying only the compiler and compilation flags.

== Test System Configuration ==
=== Hardware ===
* CPU: AMD Ryzen 7 1700 (Zen 1, 8 cores / 16 threads)
* RAM: 16 GB
* Storage: SSD
* Architecture: x86_64

=== Software Environment ===
* Operating System: Linux Mint 22.3 (Cinnamon, 64-bit)
* Debian base: trixie/sid
* Kernel: 6.8.0-90-generic
* CPU governor: performance (fixed during benchmarks)

=== Toolchain ===
* FFmpeg (system): 6.1.1-3ubuntu5
* GCC: 13.3.0 (Ubuntu 13.3.0-6ubuntu2~24.04)
* Clang/LLVM: 18.1.3 (Ubuntu build)
* Assembler:
** NASM 2.16.01
** YASM 1.3.0
* External Libraries
** x264: built locally from source and linked dynamically (system x264 package not used)

=== Test Material ===

All input files are unmodified Blender Foundation open movies.
Files were not trimmed, re-encoded, or altered in any way. SHA-256 checksums were recorded to ensure bitwise-identical inputs across all tests.

Recompile ffmpeg, is it worth?

2026-02-22T15:03:04Z

Luca: Created page with "todo"

todo

Main Page

2024-06-18T08:04:41Z

Luca:

Added few articles:
* [[Create foreign data wrapper (Postgresql)]]
* [[Automating Wiki Page Creation from a PostgreSQL Table]]
* [[Control-M job stuck]]
* [[Backup and restore huge partitioned database - SQL Server]]
* [[Install a certificate for a website under Apache on Linux]]

Create foreign data wrapper (Postgresql)

2024-06-18T08:04:16Z

Luca: Created page with "Category:postgresql In this guide I'm going to show how to read a remote Postgresql database, from Postgresql. We will work on database '''A''' that will access data of database '''B'''. On database '''A''' we need to connect with a user with admin privileges, and install the extension: <syntaxhighlight lang=postgresql>CREATE EXTENSION postgres_fdw;</syntaxhighlight> Then we create a ''server'' wich is the link to read remote data. Suppose our database '''B'''..."

[[Category:postgresql]]
In this guide I'm going to show how to read a remote Postgresql database, from Postgresql.

We will work on database '''A''' that will access data of database '''B'''.

On database '''A''' we need to connect with a user with admin privileges, and install the extension:
<syntaxhighlight lang=postgresql>CREATE EXTENSION postgres_fdw;</syntaxhighlight>

Then we create a ''server'' wich is the link to read remote data.
 Suppose our database '''B''' is on a server at address '''192.168.1.22''' on port '''5432''' and the database is called '''muninn'''.
<syntaxhighlight lang=postgresql>CREATE SERVER muninn_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.1.22', dbname 'muninn', port '5432');</syntaxhighlight>

We have also defined the name '''muninn_server''' wich is the link to our remote server '''B'''.

Now the security. We need to give permission on user on database '''A''' to read data from database '''B'''. But we need to specify credentials to read from database '''B'''.
 Suppose in database '''A''' we whant user '''odin''' to read data on database '''B'''. On database '''B''' we need to specify credentials. Suppose our user on database '''B''' is '''saturn''' and the password is '''aPPlep13''':
<syntaxhighlight lang=postgresql>CREATE USER MAPPING FOR odin
SERVER muninn_server
OPTIONS (user 'saturn', password 'aPPlep13');</syntaxhighlight>

Now we need to grant to user '''odin''' usage of foreign server '''muninn_server''':
<syntaxhighlight lang=postgresql>GRANT USAGE ON FOREIGN SERVER muninn_server TO odin;</syntaxhighlight>

Now we can connect as user '''odin''' to database '''A'''.

I strongly suggest to create an appropriate schema where make remote objects visible:
<syntaxhighlight lang=postgresql>CREATE SCHEMA rem_muninn;</syntaxhighlight>

And now we can grant visibility of remote objects of a specific schema in our new created schema:
<syntaxhighlight lang=postgresql>IMPORT FOREIGN SCHEMA remote_schema
FROM SERVER muninn_server INTO rem_muninn;</syntaxhighlight>

Now in schema rem_muninn we can query every object in remote schema.

Category:Postgresql

2024-06-18T07:43:28Z

Luca: Created page with "Pages related to Postgresql."

Pages related to Postgresql.

Automating Wiki Page Creation from a PostgreSQL Table

2024-06-18T07:43:13Z

Luca:

[[Category:mediawiki]]
[[Category:postgresql]]
Managing large datasets can be daunting. A neat solution is to use MediaWiki to transform data from a PostgreSQL table into organized wiki pages. This guide walks you through an automated process to achieve this, saving time and ensuring accurate, user-friendly data representation. Ideal for database administrators and developers, this tutorial bridges the gap between raw data and accessible wiki pages, making information management a breeze.

== Initial Data ==
* In my case, the table is called: public.cities
* The only field of interest to me is friendly_name
* The mediawiki template in my case is called city_t and has the city name as a parameter. The parameter is called city_name.

== Requirements ==
* The name of the page should not just be the city name, but should be composed similarly to: cityname_(city).
* The parameter passed to the template should instead be just the city name.
== Execution ==
Here is the query executed on PostgreSQL:
<syntaxhighlight lang=postgresql>
SELECT xmlelement(name mediawiki,
xmlagg(
xmlelement(name page,
xmlelement(name title, friendly_name || '_(city)'),
xmlelement(name revision,
xmlelement(name text, '{{city_t|city_name=' || friendly_name || '}}')
)
)
)
)::text
FROM public.cities
where active;</syntaxhighlight>

Note: the XML is converted to text because it was more convenient for me with the client I was using (DBeaver) to then copy and paste into a text file.

Having saved the text file with an .xml extension, it was enough to go to the wiki at: "SpecialPages -> Page tools -> Import pages" and upload the XML from there.

Done, hundreds of pages created quickly!

Automating Wiki Page Creation from a PostgreSQL Table

2023-11-07T14:57:33Z

Luca:

[[Category:mediawiki]]
Managing large datasets can be daunting. A neat solution is to use MediaWiki to transform data from a PostgreSQL table into organized wiki pages. This guide walks you through an automated process to achieve this, saving time and ensuring accurate, user-friendly data representation. Ideal for database administrators and developers, this tutorial bridges the gap between raw data and accessible wiki pages, making information management a breeze.

== Initial Data ==
* In my case, the table is called: public.cities
* The only field of interest to me is friendly_name
* The mediawiki template in my case is called city_t and has the city name as a parameter. The parameter is called city_name.

== Requirements ==
* The name of the page should not just be the city name, but should be composed similarly to: cityname_(city).
* The parameter passed to the template should instead be just the city name.
== Execution ==
Here is the query executed on PostgreSQL:
<syntaxhighlight lang=postgresql>
SELECT xmlelement(name mediawiki,
xmlagg(
xmlelement(name page,
xmlelement(name title, friendly_name || '_(city)'),
xmlelement(name revision,
xmlelement(name text, '{{city_t|city_name=' || friendly_name || '}}')
)
)
)
)::text
FROM public.cities
where active;</syntaxhighlight>

Note: the XML is converted to text because it was more convenient for me with the client I was using (DBeaver) to then copy and paste into a text file.

Having saved the text file with an .xml extension, it was enough to go to the wiki at: "SpecialPages -> Page tools -> Import pages" and upload the XML from there.

Done, hundreds of pages created quickly!

Automating Wiki Page Creation from a PostgreSQL Table

2023-11-03T15:54:19Z

Luca:

[[Category:mediawiki]]
Managing large datasets can be daunting. A neat solution is to use MediaWiki to transform data from a PostgreSQL table into organized wiki pages. This guide walks you through an automated process to achieve this, saving time and ensuring accurate, user-friendly data representation. Ideal for database administrators and developers, this tutorial bridges the gap between raw data and accessible wiki pages, making information management a breeze.

== Initial Data ==
* In my case, the table is called: public.cities
* The only field of interest to me is friendly_name
* The mediawiki template in my case is called city_t and has the city name as a parameter. The parameter is called city_name.

== Requirements ==
* The name of the page should not just be the city name, but should be composed similarly to: cityname_(city).
* The parameter passed to the template should instead be just the city name.
== Execution ==
Here is the query executed on PostgreSQL:
<syntaxhighlight lang=postgresql>
SELECT xmlelement(name mediawiki,
xmlagg(
xmlelement(name page,
xmlelement(name title, friendly_name || '_(city)',
xmlelement(name revision,
xmlelement(name text, '{{city_t|city_name=' || friendly_name || '}}')
)
)
)
)::text
FROM public.cities
where active;</syntaxhighlight>

Note: the XML is converted to text because it was more convenient for me with the client I was using (DBeaver) to then copy and paste into a text file.

Having saved the text file with an .xml extension, it was enough to go to the wiki at: "SpecialPages -> Page tools -> Import pages" and upload the XML from there.

Done, hundreds of pages created quickly!

Main Page

2023-11-03T15:51:46Z

Luca:

Added few articles:
* [[Automating Wiki Page Creation from a PostgreSQL Table]]
* [[Control-M job stuck]]
* [[Backup and restore huge partitioned database - SQL Server]]
* [[Install a certificate for a website under Apache on Linux]]

Automating Wiki Page Creation from a PostgreSQL Table

2023-11-03T15:51:27Z

Luca: Luca moved page Create numerous wiki pages from a Postgresql table to Automating Wiki Page Creation from a PostgreSQL Table without leaving a redirect: Titolo migliore

[[Category:mediawiki]]
This is a rather specific requirement.
In my case, I found myself having a Postgresql table from which I needed to create a page on an internal mediawiki for each row.
For each row, I also had to insert a call to a template in the body of the page.

== Initial Data ==
* In my case, the table is called: public.cities
* The only field of interest to me is friendly_name
* The mediawiki template in my case is called city_t and has the city name as a parameter. The parameter is called city_name.

== Requirements ==
* The name of the page should not just be the city name, but should be composed similarly to: cityname_(city).
* The parameter passed to the template should instead be just the city name.
== Execution ==
Here is the query executed on PostgreSQL:
<syntaxhighlight lang=postgresql>
SELECT xmlelement(name mediawiki,
xmlagg(
xmlelement(name page,
xmlelement(name title, friendly_name || '_(city)',
xmlelement(name revision,
xmlelement(name text, '{{city_t|city_name=' || friendly_name || '}}')
)
)
)
)::text
FROM public.cities
where active;</syntaxhighlight>

Note: the XML is converted to text because it was more convenient for me with the client I was using (DBeaver) to then copy and paste into a text file.

Having saved the text file with an .xml extension, it was enough to go to the wiki at: "SpecialPages -> Page tools -> Import pages" and upload the XML from there.

Done, hundreds of pages created quickly!

Category:Mediawiki

2023-11-03T15:47:24Z

Luca: Created page with "Articles relative at mediawiki itself."

Articles relative at mediawiki itself.

Automating Wiki Page Creation from a PostgreSQL Table

2023-11-03T15:47:09Z

Luca: Created page with "Category:mediawiki This is a rather specific requirement. In my case, I found myself having a Postgresql table from which I needed to create a page on an internal mediawiki for each row. For each row, I also had to insert a call to a template in the body of the page. == Initial Data == * In my case, the table is called: public.cities * The only field of interest to me is friendly_name * The mediawiki template in my case is called city_t and has the city name as a pa..."

Test

2023-10-18T14:36:13Z

Luca:

This is just a test page for testing.
== Autosection ==
this section is updated by a bot

Test

2023-10-18T14:35:49Z

Luca: Created page with "This is just a test page for testing."

This is just a test page for testing.

Backup and restore huge partitioned database - SQL Server

2023-02-27T16:31:21Z

Luca:

[[Category:SQL Server]]
This example fits the case when you have a big database in SQL Server where:
* one ore more table are partitioned
* one or more filegroup are in readonly, for example filegroups with old data

Suggestion: when you have a big database with historical data, it is a good idea to design it similar to this example.

In this case you can backup and restore filegroups separately. If you are doing full backups of this kind of database you are wasting time, space and power. You are wasting money.

We are going to create a simple database:
<syntaxhighlight lang="tsql">
CREATE DATABASE [HistoryDemo]
ON PRIMARY
(NAME = N'HistoryDemo', FILENAME = N'S:\Data\HistoryDemo.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON
(NAME = N'HistoryDemo_log', FILENAME = N'L:\Log\HistoryDemo_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
</syntaxhighlight>

Now we add filegroups where partitioned data will relay:

<syntaxhighlight lang="tsql">
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FGVeryOldStuff;
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FG_2020_Stuff;
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FG_2021_Stuff;
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FG_2022_Stuff;
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FG_2023_Stuff;
</syntaxhighlight>

This filegroups are still empty! We need to add datafiles. At least one for each filegroup. Let's do it:
<syntaxhighlight lang="tsql">
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'OldStuff', FILENAME = N'S:\Data\OldStuff.ndf', SIZE = 512KB) TO FILEGROUP FGVeryOldStuff;
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'2020_Stuff', FILENAME = N'S:\Data\2020_Stuff.ndf', SIZE = 512KB) TO FILEGROUP FG_2020_Stuff;
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'2021_Stuff', FILENAME = N'S:\Data\2021_Stuff.ndf', SIZE = 512KB) TO FILEGROUP FG_2021_Stuff;
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'2022_Stuff', FILENAME = N'S:\Data\2022_Stuff.ndf', SIZE = 512KB) TO FILEGROUP FG_2022_Stuff;
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'2023_Stuff', FILENAME = N'S:\Data\2023_Stuff.ndf', SIZE = 512KB) TO FILEGROUP FG_2023_Stuff;
</syntaxhighlight>

We need to put data in these files. So we need to prepare a partition function. See [https://learn.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql?view=sql-server-ver16 Official documentation].
<syntaxhighlight lang="tsql">
USE [HistoryDemo];
CREATE PARTITION FUNCTION PFStuff (datetime)
AS RANGE RIGHT FOR VALUES ('20200101', '20210101', '20220101', '20230101');
</syntaxhighlight>

Now we need to map this function to a partition scheme. Also see [https://learn.microsoft.com/en-us/sql/t-sql/statements/create-partition-scheme-transact-sql?view=sql-server-ver16 official documentation].
<syntaxhighlight lang="tsql">
USE [HistoryDemo];
CREATE PARTITION SCHEME PSStuff
AS PARTITION PFStuff TO (FGVeryOldStuff, FG_2020_Stuff, FG_2021_Stuff, FG_2022_Stuff, FG_2023_Stuff);
</syntaxhighlight>

You can see scheme and function in SSMS:
[[image:Partition_scheme_and_function_sql_server_ssms.png|none]]

Now we create a simple table:
<syntaxhighlight lang="tsql">
USE [HistoryDemo];

CREATE TABLE dbo.big_one
(
id int NOT NULL IDENTITY (1, 1),
event_time datetime NOT NULL,
some_text nchar(2000) NULL
)
ON PSStuff(event_time);
ALTER TABLE dbo.big_one
ADD CONSTRAINT PK_big_one PRIMARY KEY CLUSTERED (id, event_time);
</syntaxhighlight>

This is not nice, loot at primary key. Not elegant, but not a problem for a demo. It is important to notice this line:
<syntaxhighlight lang="tsql">ON PSStuff(event_time);</syntaxhighlight>
It means that table will relay on partion scheme '''PSStuff'''.

Populate table '''big_one''' with some data, in order to have few data in every partition. This is not necessary, we just want to see our data restored!
<syntaxhighlight lang="tsql">
INSERT INTO big_one (event_time, some_text) VALUES ('20180501 12:30', 'Good lunch with Emma');
INSERT INTO big_one (event_time, some_text) VALUES ('20180501 20:30', 'Good dinner with Emma');
INSERT INTO big_one (event_time, some_text) VALUES ('20200401 06:00', 'Epic prank to Dimitri');
INSERT INTO big_one (event_time, some_text) VALUES ('20200402 20:30', 'Dinner offered to Dimitri to apologize');
INSERT INTO big_one (event_time, some_text) VALUES ('20211231 23:50', 'This year was boring');
INSERT INTO big_one (event_time, some_text) VALUES ('20220915 16:00', 'Had a tea with boss');
INSERT INTO big_one (event_time, some_text) VALUES ('20230101 13:00', 'Lunch with family. Really???');
INSERT INTO big_one (event_time, some_text) VALUES ('20230701 00:11', 'Reading future');
</syntaxhighlight>

We alse create a table on PRIMARY filegroup, just to have some data on in:
<syntaxhighlight lang="tsql">
CREATE TABLE dbo.primary_table
(
id int PRIMARY KEY IDENTITY (1, 1),
some_text nvarchar(50) NULL
)
ON [PRIMARY];
</syntaxhighlight>

And we populate it:
<syntaxhighlight lang="tsql">
INSERT INTO primary_table (some_text) VALUES ('Some random text');
INSERT INTO primary_table (some_text) VALUES ('Another random text');
INSERT INTO primary_table (some_text) VALUES ('Klaatu barada nikto');
</syntaxhighlight>

Now suppose we are in year 2023, which is true at the moment I wrote this page, and we know that data before 2023 will never be modified.
 We can freeze that data by setting in READ_ONLY the correct filegroups:
<syntaxhighlight lang="tsql">
USE master;
ALTER DATABASE HistoryDemo MODIFY FILEGROUP FGVeryOldStuff READ_ONLY;
ALTER DATABASE HistoryDemo MODIFY FILEGROUP FG_2020_Stuff READ_ONLY;
ALTER DATABASE HistoryDemo MODIFY FILEGROUP FG_2021_Stuff READ_ONLY;
ALTER DATABASE HistoryDemo MODIFY FILEGROUP FG_2022_Stuff READ_ONLY;
</syntaxhighlight>

If an application tries to write on a frozen filegroup, like this query:
<syntaxhighlight lang="tsql">
USE HistoryDemo;
INSERT INTO big_one (event_time, some_text) VALUES ('20180201 12:30', 'Lunch with coworker');
</syntaxhighlight>
It gets an error:
<pre>
Msg 652, Level 16, State 1, Line 2
The index "PK_big_one" for table "dbo.big_one" (RowsetId 72057594041597952) resides on a read-only filegroup ("FGVeryOldStuff"), which cannot be modified.
</pre>
And this is good, because we want to keep our data consistent.
 But anyone can still write on fresh data. Like this:
<syntaxhighlight lang="tsql">
USE HistoryDemo;
INSERT INTO big_one (event_time, some_text) VALUES ('20230201 12:30', 'Lunch at "La quercia" reastaurant');
</syntaxhighlight>
And we get no problem: (1 row affected).

Now the backup.
First we make a backup for the readonly filegroups:
<syntaxhighlight lang="tsql">
BACKUP DATABASE [HistoryDemo] FILEGROUP = 'FGVeryOldStuff' TO DISK = 'B:\BACKUP\HistoryDemo_FGVeryOldStuff.bck';
BACKUP DATABASE [HistoryDemo] FILEGROUP = 'FG_2020_Stuff' TO DISK = 'B:\BACKUP\HistoryDemo_FG_2020_Stuff.bck';
BACKUP DATABASE [HistoryDemo] FILEGROUP = 'FG_2021_Stuff' TO DISK = 'B:\BACKUP\HistoryDemo_FG_2021_Stuff.bck';
BACKUP DATABASE [HistoryDemo] FILEGROUP = 'FG_2022_Stuff' TO DISK = 'B:\BACKUP\HistoryDemo_FG_2022_Stuff.bck';
</syntaxhighlight>
OK, now a full backup.
 But do we need it really full? No, because we already have a backup of frozen filegroups!
 The key, in the following script is '''READ_WRITE_FILEGROUPS'''.
<syntaxhighlight lang="tsql">
USE master;
BACKUP DATABASE [HistoryDemo] READ_WRITE_FILEGROUPS
TO DISK = 'B:\BACKUP\HistoryDemo_partial.bck';
</syntaxhighlight>

Look at the output:
<pre>
Processed 432 pages for database 'HistoryDemo', file 'HistoryDemo' on file 1.
Processed 24 pages for database 'HistoryDemo', file '2023_Stuff' on file 1.
Processed 2 pages for database 'HistoryDemo', file 'HistoryDemo_log' on file 1.
BACKUP DATABASE...FILE=<name> successfully processed 458 pages in 0.031 seconds (115.218 MB/sec).
</pre>
It skipped all the readonly filegroups!

Now the restore. For this example we suppose, and tested, to go on a different server, just to simulate the loss of our source server. So we have different paths.
 First we restore backup of writable filegroups:
<syntaxhighlight lang="tsql">
RESTORE DATABASE [HistoryDemo]
FROM DISK = N'B:\BACKUP\HistoryDemo_partial.bck' WITH PARTIAL, RECOVERY,
MOVE N'HistoryDemo' TO N'K:\Data\HistoryDemo.mdf',
MOVE N'HistoryDemo_log' TO N'L:\Log\HistoryDemo.ldf'
</syntaxhighlight>

Then we restore the frozen filegroups:
<syntaxhighlight lang="tsql">
RESTORE DATABASE [HistoryDemo] FILE = N'OldStuff' FROM DISK = N'B:\BACKUP\HistoryDemo_FGVeryOldStuff.bck'
WITH FILE = 1, MOVE N'OldStuff' TO N'K:\Data\OldStuff.ndf'
RESTORE DATABASE [HistoryDemo] FILE = N'2020_Stuff' FROM DISK = N'B:\BACKUP\HistoryDemo_FG_2020_Stuff.bck'
WITH FILE = 1, MOVE N'2020_Stuff' TO N'K:\Data\2020_Stuff.ndf'
RESTORE DATABASE [HistoryDemo] FILE = N'2021_Stuff' FROM DISK = N'B:\BACKUP\HistoryDemo_FG_2021_Stuff.bck'
WITH FILE = 1, MOVE N'2021_Stuff' TO N'K:\Data\2021_Stuff.ndf'
RESTORE DATABASE [HistoryDemo] FILE = N'2022_Stuff' FROM DISK = N'B:\BACKUP\HistoryDemo_FG_2022_Stuff.bck'
WITH FILE = 1, MOVE N'2022_Stuff' TO N'K:\Data\2022_Stuff.ndf'
</syntaxhighlight>

And then we restore the 2023 stuff:
<syntaxhighlight lang="tsql">
RESTORE DATABASE [HistoryDemo]
FILEGROUP = 'FG_2023_Stuff'
FROM DISK = N'B:\BACKUP\HistoryDemo_partial.bck'
WITH RECOVERY,
MOVE N'2023_Stuff' TO N'K:\Data\2023_Stuff.ndf'
</syntaxhighlight>

Done! It works!

Main Page

2023-02-27T16:16:04Z

Luca:

Added a couple of artilces:
* [[Control-M job stuck]]
* [[Backup and restore huge partitioned database - SQL Server]]

Backup and restore huge partitioned database - SQL Server

2023-02-27T16:13:43Z

Luca:

Backup and restore huge partitioned database - SQL Server

2023-02-27T16:07:39Z

Luca:

[[Category:SQL Server]]
This example fits the case when you have a big database in SQL Server where:
* one ore more table are partitioned
* one or more filegroup are in readonly, for example filegroups with old data

Suggestion: when you have a big database with historical data, it is a good idea to design it similar to this example.

In this case you can backup and restore filegroups separately. If you are doing full backups of this kind of database you are wasting time, space and power. You are wasting money.

We are going to create a simple database:
<syntaxhighlight lang="tsql">
CREATE DATABASE [HistoryDemo]
ON PRIMARY
(NAME = N'HistoryDemo', FILENAME = N'S:\Data\HistoryDemo.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON
(NAME = N'HistoryDemo_log', FILENAME = N'L:\Log\HistoryDemo_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
</syntaxhighlight>

Now we add filegroups where partitioned data will relay:

<syntaxhighlight lang="tsql">
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FGVeryOldStuff;
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FG_2020_Stuff;
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FG_2021_Stuff;
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FG_2022_Stuff;
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FG_2023_Stuff;
</syntaxhighlight>

This filegroups are still empty! We need to add datafiles. At least one for each filegroup. Let's do it:
<syntaxhighlight lang="tsql">
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'OldStuff', FILENAME = N'S:\Data\OldStuff.ndf', SIZE = 512KB) TO FILEGROUP FGVeryOldStuff;
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'2020_Stuff', FILENAME = N'S:\Data\2020_Stuff.ndf', SIZE = 512KB) TO FILEGROUP FG_2020_Stuff;
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'2021_Stuff', FILENAME = N'S:\Data\2021_Stuff.ndf', SIZE = 512KB) TO FILEGROUP FG_2021_Stuff;
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'2022_Stuff', FILENAME = N'S:\Data\2022_Stuff.ndf', SIZE = 512KB) TO FILEGROUP FG_2022_Stuff;
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'2023_Stuff', FILENAME = N'S:\Data\2023_Stuff.ndf', SIZE = 512KB) TO FILEGROUP FG_2023_Stuff;
</syntaxhighlight>

We need to put data in these files. So we need to prepare a partition function. See [https://learn.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql?view=sql-server-ver16 Official documentation].
<syntaxhighlight lang="tsql">
USE [HistoryDemo];
CREATE PARTITION FUNCTION PFStuff (datetime)
AS RANGE RIGHT FOR VALUES ('20200101', '20210101', '20220101', '20230101');
</syntaxhighlight>

Now we need to map this function to a partition scheme. Also see [https://learn.microsoft.com/en-us/sql/t-sql/statements/create-partition-scheme-transact-sql?view=sql-server-ver16 official documentation].
<syntaxhighlight lang="tsql">
USE [HistoryDemo];
CREATE PARTITION SCHEME PSStuff
AS PARTITION PFStuff TO (FGVeryOldStuff, FG_2020_Stuff, FG_2021_Stuff, FG_2022_Stuff, FG_2023_Stuff);
</syntaxhighlight>

You can see scheme and function in SSMS:
[[image:Partition_scheme_and_function_sql_server_ssms.png|none]]

Now we create a simple table:
<syntaxhighlight lang="tsql">
USE [HistoryDemo];

CREATE TABLE dbo.big_one
(
id int NOT NULL IDENTITY (1, 1),
event_time datetime NOT NULL,
some_text nchar(2000) NULL
)
ON PSStuff(event_time);
ALTER TABLE dbo.big_one
ADD CONSTRAINT PK_big_one PRIMARY KEY CLUSTERED (id, event_time);
</syntaxhighlight>

This is not nice, loot at primary key. Not elegant, but not a problem for a demo. It is important to notice this line:
<syntaxhighlight lang="tsql">ON PSStuff(event_time);</syntaxhighlight>
It means that table will relay on partion scheme '''PSStuff'''.

Populate table '''big_one''' with some data, in order to have few data in every partition. This is not necessary, we just want to see our data restored!
<syntaxhighlight lang="tsql">
INSERT INTO big_one (event_time, some_text) VALUES ('20180501 12:30', 'Good lunch with Emma');
INSERT INTO big_one (event_time, some_text) VALUES ('20180501 20:30', 'Good dinner with Emma');
INSERT INTO big_one (event_time, some_text) VALUES ('20200401 06:00', 'Epic prank to Dimitri');
INSERT INTO big_one (event_time, some_text) VALUES ('20200402 20:30', 'Dinner offered to Dimitri to apologize');
INSERT INTO big_one (event_time, some_text) VALUES ('20211231 23:50', 'This year was boring');
INSERT INTO big_one (event_time, some_text) VALUES ('20220915 16:00', 'Had a tea with boss');
INSERT INTO big_one (event_time, some_text) VALUES ('20230101 13:00', 'Lunch with family. Really???');
INSERT INTO big_one (event_time, some_text) VALUES ('20230701 00:11', 'Reading future');
</syntaxhighlight>

We alse create a table on PRIMARY filegroup, just to have some data on in:
<syntaxhighlight lang="tsql">
CREATE TABLE dbo.primary_table
(
id int PRIMARY KEY IDENTITY (1, 1),
some_text nvarchar(50) NULL
)
ON [PRIMARY];
</syntaxhighlight>

And we populate it:
<syntaxhighlight lang="tsql">
INSERT INTO primary_table (some_text) VALUES ('Some random text');
INSERT INTO primary_table (some_text) VALUES ('Another random text');
INSERT INTO primary_table (some_text) VALUES ('Klaatu barada nikto');
</syntaxhighlight>

Now suppose we are in year 2023, which is true at the moment I wrote this page, and we know that data before 2023 will never be modified.
 We can freeze that data by setting in READ_ONLY the correct filegroups:
<syntaxhighlight lang="tsql">
USE master;
ALTER DATABASE HistoryDemo MODIFY FILEGROUP FGVeryOldStuff READ_ONLY;
ALTER DATABASE HistoryDemo MODIFY FILEGROUP FG_2020_Stuff READ_ONLY;
ALTER DATABASE HistoryDemo MODIFY FILEGROUP FG_2021_Stuff READ_ONLY;
ALTER DATABASE HistoryDemo MODIFY FILEGROUP FG_2022_Stuff READ_ONLY;
</syntaxhighlight>

If an application tries to write on a frozen filegroup, like this query:
<syntaxhighlight lang="tsql">
USE HistoryDemo;
INSERT INTO big_one (event_time, some_text) VALUES ('20180201 12:30', 'Lunch with coworker');
</syntaxhighlight>
It gets an error:
<pre>
Msg 652, Level 16, State 1, Line 2
The index "PK_big_one" for table "dbo.big_one" (RowsetId 72057594041597952) resides on a read-only filegroup ("FGVeryOldStuff"), which cannot be modified.
</pre>
And this is good, because we want to keep our data consistent.
 But anyone can still write on fresh data. Like this:
<syntaxhighlight lang="tsql">
USE HistoryDemo;
INSERT INTO big_one (event_time, some_text) VALUES ('20230201 12:30', 'Lunch at "La quercia" reastaurant');
</syntaxhighlight>
And we get no problem: (1 row affected).

Now the backup.
First we make a backup for the readonly filegroups:
<syntaxhighlight lang="tsql">
BACKUP DATABASE [HistoryDemo] FILEGROUP = 'FGVeryOldStuff' TO DISK = 'B:\BACKUP\HistoryDemo_FGVeryOldStuff.bck';
BACKUP DATABASE [HistoryDemo] FILEGROUP = 'FG_2020_Stuff' TO DISK = 'B:\BACKUP\HistoryDemo_FG_2020_Stuff.bck';
BACKUP DATABASE [HistoryDemo] FILEGROUP = 'FG_2021_Stuff' TO DISK = 'B:\BACKUP\HistoryDemo_FG_2021_Stuff.bck';
BACKUP DATABASE [HistoryDemo] FILEGROUP = 'FG_2022_Stuff' TO DISK = 'B:\BACKUP\HistoryDemo_FG_2022_Stuff.bck';
</syntaxhighlight>
OK, now a full backup.
 But do we need it really full? No, because we already have a backup of frozen filegroups!
 The key, in the following script is '''READ_WRITE_FILEGROUPS'''.
<syntaxhighlight lang="tsql">
USE master;
BACKUP DATABASE [HistoryDemo] READ_WRITE_FILEGROUPS
TO DISK = 'B:\BACKUP\HistoryDemo_partial.bck';
</syntaxhighlight>

Look at the output:
<pre>
Processed 432 pages for database 'HistoryDemo', file 'HistoryDemo' on file 1.
Processed 24 pages for database 'HistoryDemo', file '2023_Stuff' on file 1.
Processed 2 pages for database 'HistoryDemo', file 'HistoryDemo_log' on file 1.
BACKUP DATABASE...FILE=<name> successfully processed 458 pages in 0.031 seconds (115.218 MB/sec).
</pre>
It skipped all the readonly filegroups!

Now the restore. For this example we suppose, and tested, to go on a different server, just to simulate the loss of our source server. So we have different paths.
 First we restore backup of writable filegroups:
<syntaxhighlight lang="tsql">
RESTORE DATABASE [HistoryDemo]
FROM DISK = N'B:\BACKUP\HistoryDemo_partial.bck' WITH PARTIAL, RECOVERY,
MOVE N'HistoryDemo' TO N'K:\Data\HistoryDemo.mdf',
MOVE N'2023_Stuff' TO N'K:\Data\2023_Stuff.ndf',
MOVE N'HistoryDemo_log' TO N'L:\Log\HistoryDemo.ldf'
</syntaxhighlight>

Then we restore the frozen filegroups:
<syntaxhighlight lang="tsql">
RESTORE DATABASE [HistoryDemo] FILE = N'OldStuff' FROM DISK = N'B:\BACKUP\HistoryDemo_FGVeryOldStuff.bck'
WITH FILE = 1, MOVE N'OldStuff' TO N'K:\Data\OldStuff.ndf'
RESTORE DATABASE [HistoryDemo] FILE = N'2020_Stuff' FROM DISK = N'B:\BACKUP\HistoryDemo_FG_2020_Stuff.bck'
WITH FILE = 1, MOVE N'2020_Stuff' TO N'K:\Data\2020_Stuff.ndf'
RESTORE DATABASE [HistoryDemo] FILE = N'2021_Stuff' FROM DISK = N'B:\BACKUP\HistoryDemo_FG_2021_Stuff.bck'
WITH FILE = 1, MOVE N'2021_Stuff' TO N'K:\Data\2021_Stuff.ndf'
RESTORE DATABASE [HistoryDemo] FILE = N'2022_Stuff' FROM DISK = N'B:\BACKUP\HistoryDemo_FG_2022_Stuff.bck'
WITH FILE = 1, MOVE N'2022_Stuff' TO N'K:\Data\2022_Stuff.ndf'
</syntaxhighlight>

And then we restore the 2023 stuff:
<syntaxhighlight lang="tsql">
RESTORE DATABASE [HistoryDemo]
FILEGROUP = 'FG_2023_Stuff'
FROM DISK = N'B:\BACKUP\HistoryDemo_partial.bck'
WITH RECOVERY
GO
</syntaxhighlight>

Backup and restore huge partitioned database - SQL Server

2023-02-27T15:13:25Z

Luca:

Backup and restore huge partitioned database - SQL Server

2023-02-27T15:09:28Z

Luca:

[[Category:SQL Server]]
This example fits the case when you have a big database in SQL Server where:
* one ore more table are partitioned
* one or more filegroup are in readonly, for example filegroups with old data

Suggestion: when you have a big database with historical data, it is a good idea to design it similar to this example.

In this case you can backup and restore filegroups separately. If you are doing full backups of this kind of database you are wasting time, space and power. You are wasting money.

== Demo database creation ==
We are going to create a simple database:

<syntaxhighlight lang="tsql">
CREATE DATABASE [HistoryDemo]
ON PRIMARY
(NAME = N'HistoryDemo', FILENAME = N'S:\Data\HistoryDemo.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON
(NAME = N'HistoryDemo_log', FILENAME = N'L:\Log\HistoryDemo_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
</syntaxhighlight>

Now we add filegroups where partitioned data will relay:

<syntaxhighlight lang="tsql">
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FGVeryOldStuff;
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FG_2020_Stuff;
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FG_2021_Stuff;
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FG_2022_Stuff;
ALTER DATABASE [HistoryDemo] ADD FILEGROUP FG_2023_Stuff;
</syntaxhighlight>

This filegroups are still empty! We need to add datafiles. At least one for each filegroup. Let's do it:
<syntaxhighlight lang="tsql">
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'OldStuff', FILENAME = N'S:\Data\OldStuff.ndf', SIZE = 512KB) TO FILEGROUP FGVeryOldStuff;
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'2020_Stuff', FILENAME = N'S:\Data\2020_Stuff.ndf', SIZE = 512KB) TO FILEGROUP FG_2020_Stuff;
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'2021_Stuff', FILENAME = N'S:\Data\2021_Stuff.ndf', SIZE = 512KB) TO FILEGROUP FG_2021_Stuff;
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'2022_Stuff', FILENAME = N'S:\Data\2022_Stuff.ndf', SIZE = 512KB) TO FILEGROUP FG_2022_Stuff;
ALTER DATABASE [HistoryDemo]
ADD FILE (NAME = N'2023_Stuff', FILENAME = N'S:\Data\2023_Stuff.ndf', SIZE = 512KB) TO FILEGROUP FG_2023_Stuff;
</syntaxhighlight>

We need to put data in these files. So we need to prepare a partition function. See [https://learn.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql?view=sql-server-ver16 Official documentation].
<syntaxhighlight lang="tsql">
USE [HistoryDemo];
CREATE PARTITION FUNCTION PFStuff (datetime)
AS RANGE RIGHT FOR VALUES ('20200101', '20210101', '20220101', '20230101');
</syntaxhighlight>

Now we need to map this function to a partition scheme. Also see [https://learn.microsoft.com/en-us/sql/t-sql/statements/create-partition-scheme-transact-sql?view=sql-server-ver16 official documentation].
<syntaxhighlight lang="tsql">
USE [HistoryDemo];
CREATE PARTITION SCHEME PSStuff
AS PARTITION PFStuff TO (FGVeryOldStuff, FG_2020_Stuff, FG_2021_Stuff, FG_2022_Stuff, FG_2023_Stuff);
</syntaxhighlight>

You can see scheme and function in SSMS:
[[image:Partition_scheme_and_function_sql_server_ssms.png|none]]

Now we create a simple table:
<syntaxhighlight lang="tsql">
USE [HistoryDemo];

CREATE TABLE dbo.big_one
(
id int NOT NULL IDENTITY (1, 1),
event_time datetime NOT NULL,
some_text nchar(2000) NULL
)
ON PSStuff(event_time);
ALTER TABLE dbo.big_one
ADD CONSTRAINT PK_big_one PRIMARY KEY CLUSTERED (id, event_time);
</syntaxhighlight>

This is not nice, loot at primary key. Not elegant, but not a problem for a demo. It is important to notice this line:
<syntaxhighlight lang="tsql">ON PSStuff(event_time);</syntaxhighlight>
It means that table will relay on partion scheme '''PSStuff'''.

Populate table '''big_one''' with some data, in order to have few data in every partition. This is not necessary, we just want to see our data restored!
<syntaxhighlight lang="tsql">
INSERT INTO big_one (event_time, some_text) VALUES ('20180501 12:30', 'Good lunch with Emma');
INSERT INTO big_one (event_time, some_text) VALUES ('20180501 20:30', 'Good dinner with Emma');
INSERT INTO big_one (event_time, some_text) VALUES ('20200401 06:00', 'Epic prank to Dimitri');
INSERT INTO big_one (event_time, some_text) VALUES ('20200402 20:30', 'Dinner offered to Dimitri to apologize');
INSERT INTO big_one (event_time, some_text) VALUES ('20211231 23:50', 'This year was boring');
INSERT INTO big_one (event_time, some_text) VALUES ('20220915 16:00', 'Had a tea with boss');
INSERT INTO big_one (event_time, some_text) VALUES ('20230101 13:00', 'Lunch with family. Really???');
INSERT INTO big_one (event_time, some_text) VALUES ('20230701 00:11', 'Reading future');
</syntaxhighlight>

We alse create a table on PRIMARY filegroup, just to have some data on in:
<syntaxhighlight lang="tsql">
CREATE TABLE dbo.primary_table
(
id int PRIMARY KEY IDENTITY (1, 1),
some_text nvarchar(50) NULL
)
ON [PRIMARY];
</syntaxhighlight>

And we populate it:
<syntaxhighlight lang="tsql">
INSERT INTO primary_table (some_text) VALUES ('Some random text');
INSERT INTO primary_table (some_text) VALUES ('Another random text');
INSERT INTO primary_table (some_text) VALUES ('Klaatu barada nikto');
</syntaxhighlight>

Now suppose we are in year 2023, which is true at the moment I wrote this page, and we know that data before 2023 will never be modified.
 We can freeze that data by setting in READ_ONLY the correct filegroups:
<syntaxhighlight lang="tsql">
USE master;
ALTER DATABASE HistoryDemo MODIFY FILEGROUP FGVeryOldStuff READ_ONLY;
ALTER DATABASE HistoryDemo MODIFY FILEGROUP FG_2020_Stuff READ_ONLY;
ALTER DATABASE HistoryDemo MODIFY FILEGROUP FG_2021_Stuff READ_ONLY;
ALTER DATABASE HistoryDemo MODIFY FILEGROUP FG_2022_Stuff READ_ONLY;
</syntaxhighlight>

If an application tries to write on a frozen filegroup, like this query:
<syntaxhighlight lang="tsql">
USE HistoryDemo;
INSERT INTO big_one (event_time, some_text) VALUES ('20180201 12:30', 'Lunch with coworker');
</syntaxhighlight>
It gets an error:
<pre>
Msg 652, Level 16, State 1, Line 2
The index "PK_big_one" for table "dbo.big_one" (RowsetId 72057594041597952) resides on a read-only filegroup ("FGVeryOldStuff"), which cannot be modified.
</pre>
And this is good, because we want to keep our data consistent.
 But anyone can still write on fresh data. Like this:
<syntaxhighlight lang="tsql">
USE HistoryDemo;
INSERT INTO big_one (event_time, some_text) VALUES ('20230201 12:30', 'Lunch at "La quercia" reastaurant');
</syntaxhighlight>
And we get no problem: (1 row affected).

Now the backup.
First we make a backup for the readonly filegroups:
<syntaxhighlight lang="tsql">
BACKUP DATABASE [HistoryDemo] FILEGROUP = 'FGVeryOldStuff' TO DISK = 'B:\BACKUP\HistoryDemo_FGVeryOldStuff.bck';
BACKUP DATABASE [HistoryDemo] FILEGROUP = 'FG_2020_Stuff' TO DISK = 'B:\BACKUP\HistoryDemo_FG_2020_Stuff.bck';
BACKUP DATABASE [HistoryDemo] FILEGROUP = 'FG_2021_Stuff' TO DISK = 'B:\BACKUP\HistoryDemo_FG_2021_Stuff.bck';
BACKUP DATABASE [HistoryDemo] FILEGROUP = 'FG_2022_Stuff' TO DISK = 'B:\BACKUP\HistoryDemo_FG_2022_Stuff.bck';
</syntaxhighlight>

Backup and restore huge partitioned database - SQL Server

2023-02-27T14:58:49Z

Luca:

Backup and restore huge partitioned database - SQL Server

2023-02-27T14:57:25Z

Luca:

File:Partition scheme and function sql server ssms.png

2023-02-27T14:52:22Z

Luca: Category:SQL Server

== Summary ==
[[Category:SQL Server]]

Category:SQL Server

2023-02-27T14:22:14Z

Luca: Created page with "Articles about Microsoft SQL Server."

Articles about Microsoft SQL Server.

Backup and restore huge partitioned database - SQL Server

2023-02-27T14:21:58Z

Luca:

Backup and restore huge partitioned database - SQL Server

2023-02-27T14:20:08Z

Luca: Created page with "Category:SQL Server This example fits the case when you have a big database in SQL Server where: * one ore more table are partitioned * one or more filegroup are in readonly, for example filegroups with old data Suggestion: when you have a big database with historical data, it is a good idea to design it similar to this example. In this case you can backup and restore filegroups separately. If you are doing full backups of this kind of database you are wasting time..."

Main Page

2023-02-01T16:47:12Z

Luca: Replaced content with "Added first article about Control-M: * Control-M job stuck"

Added first article about Control-M:
* [[Control-M job stuck]]

Category:Control-M

2023-02-01T16:46:40Z

Luca: Created page with "Pages about Control-M."

Pages about Control-M.

Control-M job stuck

2023-02-01T16:46:24Z

Luca:

[[Category:Control-M]]
I wrote this article following my experience with Control-M. I'm not really an expert, and this article is relative to Control-M version 9.0.19.
== What do I mean as job stuck? ==
Stuck jobs refer to Control-M jobs that remain in the schedule for days without a underlying process. There are several cases, they will be documented one by one as they arise.
== Job in hold in Wait Condition ==
This is the seemingly simplest case. A job on hold can be deleted, and the problem is solved.
== Job in running ==
If the job has remained in running, a '''kill''' probably will not work. There is therefore a script to end a job of this type on the server side.
 To do this, you need to log in to the active Control-M server and run the appropriate script.
 By selecting the job in question, the Order ID must be identified, which is the unique identifier to act on the job that we find in monitoring. This information can be found in the 'Run Information' section. For example, in the following case:
 [[Image:Control-m-orderid.png|none]]
The Order ID is '''14nfw''', and this notation works well for our purposes.
 To run the interested script, open a command prompt on the server as '''administrator'''. In this specific case, you need to adjust the following command to your path:
<syntaxhighlight lang="winbatch">"C:\Program Files\BMC Software\Control-M Server\bmcperl\perl" "C:\Program Files\BMC Software\Control-M Server\ctm_server\scripts\force_end_job.pl" 14nfw</syntaxhighlight>

Interactively, confirmation will be requested, or an error will be returned.
 Attention: ending the job in this way is equivalent to a kill, so a Control-M alert will be generated to be managed.

== Job in running in hold ==
End the job as in the previous case. Additionally, the job must be freed (free).

== Cyclic job running in hold ==
It's the most annoying case, because in many cases if we were to simply do a free, the next execution would start. In this case, before proceeding with the steps from the previous case, the job schedule needs to be modified by limiting the execution interval to a time range that does not include the period during which we are operating.

File:Control-m-orderid.png

2023-02-01T16:32:11Z

Luca: Category:Control-M

== Summary ==
[[Category:Control-M]]

Control-M job stuck

2023-02-01T16:22:24Z

Luca: gestiscoIdati8

[[Category:Control-M]
I wrote this article following my experience with Control-M. I'm not really an expert, and this article is relative to Control-M; version lucazeo