72 lines
1.9 KiB
Transact-SQL
72 lines
1.9 KiB
Transact-SQL
USE [dba]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[set_superset_source] Script Date: 15.04.2025 10:22:33 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
/*
|
|
set_superset_source
|
|
|
|
This procedure is set to be called from the on-prem HCI database server.
|
|
We need to sync product_superset from on-prem to cloud, but db snapshots cannot be replicated to cloud.
|
|
To get around this, a snapshot replication has been setup for both product_superset_00 and product_superset_01 and a call to this stored procedure will
|
|
create a backup of the superset that is in use on the on-prem database and restore it as [product_superset].
|
|
|
|
It triples the amount of data, but ensure that the sl2007 db uses the correct data.
|
|
|
|
--Changelog (dd.mm.yyyy)
|
|
12.02.2025 TSC Creation
|
|
*/
|
|
CREATE OR ALTER procedure [dbo].[set_superset_source]
|
|
@src_db_name varchar(100)
|
|
as
|
|
begin
|
|
set nocount on
|
|
set xact_abort on
|
|
|
|
declare @q nvarchar(max)='';
|
|
declare @url varchar(max) = 'https://stbagspezlisteprdsql.blob.core.windows.net/sqlbakfiles/superset.bak';
|
|
DECLARE @tplBkp nvarchar(max) = '
|
|
BACKUP DATABASE [@src_db_name@]
|
|
TO URL = ''@url@''
|
|
WITH COPY_ONLY, COMPRESSION, FORMAT;
|
|
';
|
|
|
|
DECLARE @tplRest nvarchar(max)='
|
|
RESTORE DATABASE [Product_Superset]
|
|
FROM URL = ''@url@'';
|
|
';
|
|
--check that the given source db exists
|
|
IF NOT EXISTS(
|
|
SELECT 1
|
|
FROM sys.databases d
|
|
WHERE d.name = @src_db_name
|
|
)
|
|
begin
|
|
RAISERROR('The database specified as a source for the superset is not found on this server: %s', 16, 10, @src_db_name)
|
|
return
|
|
end
|
|
else
|
|
begin
|
|
|
|
exec [dbo].[drop_db_product_superset]
|
|
|
|
--backup the correct superset
|
|
set @q = REPLACE(REPLACE(@tplBkp,'@src_db_name@',@src_db_name),'@url@',@url);
|
|
exec sys.sp_executesql @q, N'';
|
|
print 'Backup of source '+@src_db_name+' done';
|
|
|
|
--restore to product_superset
|
|
set @q = REPLACE(@tplRest,'@url@',@url);
|
|
exec sys.sp_executesql @q, N'';
|
|
print 'restore of product_superset done';
|
|
end
|
|
|
|
end
|
|
GO
|
|
|