1.
Migrate Aure SQL to MS SQL Servier (on--prem)
(Method 1) (Method 2) (Method 3)
Linked Server
Data mapping and create schema
in MySql. Migrate data through
Openquery
Challenges
➢ Data types mapping:
Source Type MySQL Type Comment
INT INT
TINYINT TINYINT UNSIGNED flag set in MySQL.
SMALLINT SMALLINT
BIGINT BIGINT
BIT TINYINT(1)
FLOAT FLOAT Precision value is used for
storage size in both.
REAL FLOAT
NUMERIC DECIMAL
DECIMAL DECIMAL
MONEY DECIMAL
SMALLMONEY DECIMAL
CHAR CHAR/LONGTEXT Depending on its length.
MySQL Server 5.6 and higher
can have CHAR columns with a
length up to 255 characters.
Anything larger is migrated as
LONGTEXT.
NCHAR CHAR/LONGTEXT Depending on its length.
MySQL Server 5.6 and higher
can have VARCHAR columns
with a length up to 65535
characters. Anything larger is
migrated to one of the TEXT
blob types. In MySQL, a
character set of strings
depends on the column
character set instead of the
data type.
VARCHAR VARCHAR/MEDIUMTEXT/LONG Depending on its length.
TEXT MySQL Server 5.6 and higher
can have VARCHAR columns
with a length up to 65535
characters. Anything larger is
migrated to one of the TEXT
blob types.
NVARCHAR VARCHAR/MEDIUMTEXT/LONG Depending on its length.
TEXT MySQL Server 5.6 and higher
can have VARCHAR columns
with a length up to 65535
characters. Anything larger is
migrated to one of the TEXT
blob types. In MySQL, a
character set of strings
depends on the column
character set instead of the
data type.
DATE DATE
DATETIME DATETIME
DATETIME2 DATETIME Date range in MySQL is '1000-
01-01 00:00:00.000000' to
'9999-12-31 23:59:59.999999'.
Note: fractional second values
are only stored as of MySQL
Server 5.6.4 and higher.
SMALLDATETIME DATETIME
DATETIMEOFFSET DATETIME
TIME TIME
TIMESTAMP TIMESTAMP
ROWVERSION TIMESTAMP
BINARY BINARY/MEDIUMBLOB/LONGB Depending on its length.
LOB
VARBINARY VARBINARY/MEDIUMBLOB/LO Depending on its length.
NGBLOB
TEXT VARCHAR/MEDIUMTEXT/LONG Depending on its length.
TEXT
NTEXT VARCHAR/MEDIUMTEXT/LONG Depending on its length.
TEXT
IMAGE TINYBLOB/MEDIUMBLOB/LON Depending on its length.
GBLOB
SQL_VARIANT not migrated There is not specific support
for this data type.
TABLE not migrated There is not specific support
for this data type.
HIERARCHYID not migrated There is not specific support
for this data type.
UNIQUEIDENTIFIER VARCHAR(64) A unique flag set in MySQL.
There is not specific support
for inserting unique identifier
values.
SYSNAME VARCHAR(160)
XML TEXT
➢ Code Migration:
Within the migration, we only convert tables and copy data, but it will not convert triggers, views, and
stored procedures. we must manually convert them to a MySQL database.
Duration
Depends on database size i.e., number of tables, triggers, views, and stored procedures. Please see the
below three sample code which I wrote for one of mine clients in Upwork
Example 1
To write below code I took 30 mins.
declare @date varchar(10),
@sending_shop_number varchar(4) = 482,
@receiving_shop_numbers varchar(50),
@product_no varchar(4) = 809,
@Ssql nvarchar(3000)
set @date= '20220202'
print @date
set @receiving_shop_numbers = ('''260'',''469''')
set @Ssql = N' select pd.*
from production_detail pd
inner join
production_header ph
on pd.shop_no = ph.shop_no and
pd.production_date = ph.production_date and
pd.production_no = ph.production_no
where
ph.shop_no in (' + @receiving_shop_numbers +') and
cast(ph.production_date as date) = '''+ @date +''' and
pd.product_no =' +''+ @product_no +''
----print @Ssql
----exec sp_executesql @Ssql
----select shop_no from shop_details_from_PretShopDB2
----where cluster_parent_shop_id in
---- (select shop_id
---- from shop_details_from_PretShopDB2
---- where shop_no = 482
--)
print @receiving_shop_numbers
SELECT
'Transfers' Source_table,
ROW_NUMBER()
OVER(PARTITION BY CAST(transfer_date AS
date),th.sending_shop_no,th.receiving_shop_no,td.product_no,default_product_transfer_sequ
ence_no
ORDER BY default_product_transfer_sequence_no) seq,
CAST(transfer_date AS date) transfer_date,
th.sending_shop_no,
th.receiving_shop_no,
td.product_no,
default_product_transfer_sequence_no,
receiving_shop_processed_date,
td.quantity
FROM
[dbo].transfer_header AS th WITH (NOLOCK)
JOIN [dbo].transfer_detail AS td WITH (NOLOCK) ON
td.sending_shop_no = th.sending_shop_no AND
td.receiving_shop_no = th.receiving_shop_no AND
td.transfer_no = th.transfer_no AND
td.product_no IS NOT NULL
WHERE
th.sending_shop_no = @sending_shop_number and
th.status_flag = 'A' AND
CAST(th.sending_shop_processed_date AS date) =@date and
td.product_no = @product_no
select pd.* from production_detail pd
inner join production_header ph
on pd.shop_no = ph.shop_no and
pd.production_date = ph.production_date and
pd.production_no = ph.production_no
where ph.shop_no = @sending_shop_number and
cast(ph.production_date as date) =@date and
pd.product_no = @product_no
EXEC Sp_executesql @Ssql
Example 2
To write below code I took 4 hours (used advance analytical functions)
--------- SQL SCRIPT TO David Coen 27/08/2021
----CREATE TABLE [dbo].[tblPortfolio](
---- [PortfolioID] [int] NOT NULL,
---- [Trade_date] [datetime] NOT NULL,
---- [SecurityID] [varchar](5) NOT NULL,
---- [Weight] [decimal](4, 2) NULL,
----PRIMARY KEY CLUSTERED
----(
---- [PortfolioID] ASC,
---- [Trade_date] ASC,
---- [SecurityID] ASC
----)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON
[PRIMARY]
----) ON [PRIMARY]
----GO
----/****** Object: Table [dbo].[tblSecurityPrices] Script Date: 27-08-2021 03:04:28 ******/
----SET ANSI_NULLS ON
----GO
----SET QUOTED_IDENTIFIER ON
----CREATE TABLE [dbo].[tblSecurityPrices](
---- [Trade_date] [date] NOT NULL,
---- [SecurityID] [varchar](5) NOT NULL,
---- [Price] [decimal](4, 2) NULL,
----PRIMARY KEY CLUSTERED
----(
---- [Trade_date] ASC,
---- [SecurityID] ASC
----)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON
[PRIMARY]
----) ON [PRIMARY]
----GO
----INSERT [dbo].[tblPortfolio] ([PortfolioID], [Trade_date], [SecurityID], [Weight]) VALUES (1,
CAST(N'2006-01-17T00:00:00.000' AS DateTime), N'M1', CAST(5.90 AS Decimal(4, 2)))
----GO
----INSERT [dbo].[tblPortfolio] ([PortfolioID], [Trade_date], [SecurityID], [Weight]) VALUES (1,
CAST(N'2006-01-17T00:00:00.000' AS DateTime), N'M2', CAST(94.10 AS Decimal(4, 2)))
----GO
----INSERT [dbo].[tblPortfolio] ([PortfolioID], [Trade_date], [SecurityID], [Weight]) VALUES (1,
CAST(N'2006-01-18T00:00:00.000' AS DateTime), N'M1', NULL)
----GO
----INSERT [dbo].[tblPortfolio] ([PortfolioID], [Trade_date], [SecurityID], [Weight]) VALUES (1,
CAST(N'2006-01-18T00:00:00.000' AS DateTime), N'M2', NULL)
----GO
----INSERT [dbo].[tblSecurityPrices] ([Trade_date], [SecurityID], [Price]) VALUES (CAST(N'2006-01-17' AS
Date), N'M1', CAST(11.71 AS Decimal(4, 2)))
----GO
----INSERT [dbo].[tblSecurityPrices] ([Trade_date], [SecurityID], [Price]) VALUES (CAST(N'2006-01-17' AS
Date), N'M2', CAST(13.09 AS Decimal(4, 2)))
----GO
----INSERT [dbo].[tblSecurityPrices] ([Trade_date], [SecurityID], [Price]) VALUES (CAST(N'2006-01-18' AS
Date), N'M1', CAST(12.62 AS Decimal(4, 2)))
----GO
----INSERT [dbo].[tblSecurityPrices] ([Trade_date], [SecurityID], [Price]) VALUES (CAST(N'2006-01-18' AS
Date), N'M2', CAST(13.27 AS Decimal(4, 2)))
----GO
---------------------QUERY USING T-SQL WINDOWS(ANALYTICAL) FUNCATIONS
-----------------QUERY 1
--------------------/* -----------IGNORE COMMENTED LINES BETWEEN THE CODE-----------*\--------------------
SELECT t2.PortfolioID,t2.Trade_date,
t2.X/LAG(X) OVER (ORDER BY t2.PortfolioID,t2.Trade_date ) [ReturnVal]
FROM
SELECT t1.PortfolioID,
---t1.SecurityID,
t1.Trade_date,
SUM(t1.X) X
--LAG(t1.Amount_invested) OVER (ORDER BY t1.PortfolioID,t1.Trade_date ) Y
FROM
SELECT a.PortfolioID,
a.Trade_date,
a.SecurityID,
a.Weight,
b.Price ,
ISNULL(LAG(a.Weight) OVER(PARTITION BY a.PortfolioID,a.SecurityID ORDER BY a.Trade_date),a.Weight)
LAG_Weight,
--(FIRST_VALUE(a.Weight) OVER(PARTITION BY a.SecurityID ORDER BY a.Trade_date)/100)*Price
Amount_invested
ISNULL(LAG(a.Weight) OVER(PARTITION BY a.PortfolioID,a.SecurityID ORDER BY
a.Trade_date),a.Weight)/100 * b.Price X
FROM tblPortfolio a
--LAG(a.Weight,1) OVER(ORDER BY a.Weight desc) prev_order_date from tblPortfolio a
INNER JOIN tblSecurityPrices b
ON --- a.PortfolioID = b.PortfolioID AND
a.SecurityID= b.SecurityID AND
a.Trade_date= b.Trade_date
)t1
GROUP BY t1.PortfolioID,
---t1.SecurityID,
t1.Trade_date
)t2
GROUP BY t2.PortfolioID,t2.Trade_date,t2.X
GO
-------------QUERY 2
SELECT t2.PortfolioID,t2.Trade_date,
LAG(X) OVER (ORDER BY t2.PortfolioID,t2.Trade_date ) as Y,
t2.X,
t2.X/LAG(X) OVER (ORDER BY t2.PortfolioID,t2.Trade_date ) [ReturnVal]
FROM (
SELECT t1.PortfolioID,
---t1.SecurityID,
t1.Trade_date,
SUM(t1.X) X
--LAG(t1.Amount_invested) OVER (ORDER BY t1.PortfolioID,t1.Trade_date ) Y
FROM
SELECT a.PortfolioID,
a.Trade_date,
a.SecurityID,
a.Weight,
b.Price ,
ISNULL(LAG(a.Weight) OVER(PARTITION BY a.PortfolioID,a.SecurityID ORDER BY a.Trade_date),a.Weight)
LAG_Weight,
--(FIRST_VALUE(a.Weight) OVER(PARTITION BY a.SecurityID ORDER BY a.Trade_date)/100)*Price
Amount_invested
ISNULL(LAG(a.Weight) OVER(PARTITION BY a.PortfolioID,a.SecurityID ORDER BY
a.Trade_date),a.Weight)/100 * b.Price X
FROM tblPortfolio a
--LAG(a.Weight,1) OVER(ORDER BY a.Weight desc) prev_order_date from tblPortfolio a
INNER JOIN tblSecurityPrices b
ON --- a.PortfolioID = b.PortfolioID AND
a.SecurityID= b.SecurityID AND
a.Trade_date= b.Trade_date
)t1
GROUP BY t1.PortfolioID,
---t1.SecurityID,
t1.Trade_date
)t2 GROUP BY t2.PortfolioID,t2.Trade_date,t2.X
Example 3
To write below code I took 8 hours
USE [master]
GO
/****** Object: StoredProcedure [dbo].[UspCloneDB] Script Date: 09-09-2021 10:12:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UspCloneDB]
@DBName nvarchar(100) ,
@DirPathDB nvarchar(100) ,
@DirPathClone nvarchar(100),
@NoofClones INT
AS
BEGIN
SET NOCOUNT ON
DECLARE
@FolderBackup nvarchar(100) ,
@FolderDatabase nvarchar(100),
@FolderCloneBackup nvarchar(100) ,
@FolderCloneDatabase nvarchar(100) ,
@BackupMaster nvarchar(200),
@BackupClone nvarchar(200),
@MinNoofDBClone tinyint =1,
@MaxNofDBClone tinyint,
@CloneDBName nvarchar(100),
@CloneDBSeqName nvarchar (100),
@SqlClearCloneDB nvarchar(max),
@SqlCreateCloneDB nvarchar(max),
@ValdbSQl nvarchar(1000),
@MasterCopyDBName varchar(100),
@CreateMasterDBCopySQl nvarchar(4000),
@SqlStateFgroup nvarchar(max),
@MasterCopyNdf nvarchar(200),
@MasterCopyNdfGroup nvarchar(200) ,
@SqlMasterBup nvarchar(500),
@CloneCopyNdfGroup nvarchar(500),
@CloneCopyNdf nvarchar(200)
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)
---1 Initialize variables
SET @FolderBackup = @DirPathDB+ 'Backup\'
SET @FolderDatabase = @DirPathDB+ 'Database\'
SET @FolderCloneDatabase = @DirPathClone+ 'Database\'
SET @FolderCloneBackup = @DirPathClone+ 'Backup\'
SET @BackupMaster= @FolderBackup + @DBName+'_'+ REPLACE(CONVERT(varchar,GETDATE(),27),':','-
') +'.BAK'
SET @BackupClone = @FolderCloneBackup+@DBName+'_'+
REPLACE(CONVERT(varchar,GETDATE(),27),':','-') +'.BAK'
SET @MaxNofDBClone = @NoofClones
SET @CloneDBName = @DBName
SET @MasterCopyDBName = 'ActiveM_' +@DBName
SET @MasterCopyDBName= REPLACE(@MasterCopyDBName,' ','')
SET @MasterCopyNdfGroup = @MasterCopyDBName +'_FILEGROUP'
SET @MasterCopyNdf = @FolderDatabase +@MasterCopyDBName +'_1.ndf'
-- 2 - @DataPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @DirPathDB
-- 3 - Create the @DataPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DirPathDB)
EXEC master.dbo.xp_create_subdir @DirPathDB
EXEC master.dbo.xp_create_subdir @DirPathClone
EXEC master.dbo.xp_create_subdir @FolderBackup
EXEC master.dbo.xp_create_subdir @FolderDatabase
EXEC master.dbo.xp_create_subdir @FolderCloneDatabase
EXEC master.dbo.xp_create_subdir @FolderCloneBackup
--4 - Take Backup of Given Database
SET @ValdbSQl = 'IF EXISTS(SELECT * FROM sys.databases WHERE name = '''+ @DBName +''' )
BEGIN
BACKUP DATABASE ' + @DBName +' TO DISK = '''+ @BackupMaster +'''WITH CHECKSUM;
END
'
---print @ValdbSQl
EXEC sp_Executesql @ValdbSQl
---5 - Create Database
SET @CreateMasterDBCopySQl = ' IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = '''
+ @MasterCopyDBName +''' ) BEGIN
EXEC UspCopyMaster '''+ @BackupMaster +''','''+@MasterCopyDBName +''','''+@FolderDatabase +'''
IF NOT EXISTS(
SELECT * FROM '+@MasterCopyDBName +'.sys.filegroups
WHERE name = '''+ @MasterCopyNdfGroup+'''
) BEGIN
ALTER DATABASE ' + @MasterCopyDBName +' ADD FILEGROUP [' + @MasterCopyNdfGroup +']
ALTER DATABASE ' + @MasterCopyDBName +' ADD FILE ( NAME = N''' + @MasterCopyDBName +''',
FILENAME = N'''+ @MasterCopyNdf +''' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [' +
@MasterCopyNdfGroup +']
END
END
EXEC master.dbo.xp_delete_file 0, ''' +@BackupMaster +'''
'
---print @CreateMasterDBCopySQl
EXEC sp_Executesql @CreateMasterDBCopySQl
---6 Take Copy of Master DB
SET @SqlMasterBup = 'BACKUP DATABASE ' + @MasterCopyDBName +' TO DISK = '''+ @BackupClone
+'''WITH CHECKSUM;'
EXEC (@SqlMasterBup)
----- 7 Loop 5 times to create database copies
WHILE @MinNoofDBClone <=@MaxNofDBClone
BEGIN
SET @CloneDBSeqName = @MasterCopyDBName+'Copy'+ CAST(@MinNoofDBClone as varchar)
SET @CloneCopyNdfGroup = @CloneDBSeqName +'_FILEGROUP'
SET @CloneCopyNdf = @FolderCloneDatabase +@CloneDBSeqName +'.ndf'
SET @SqlClearCloneDB =
'IF EXISTS(SELECT * FROM sys.databases WHERE name = '''+ @CloneDBSeqName + ''')
BEGIN
ALTER DATABASE ' + @CloneDBSeqName +' SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE '+ @CloneDBSeqName +'
END'
--- PRINT @CloneDBSeqName
EXEC sp_Executesql @SqlClearCloneDB
SET @SqlCreateCloneDB =
'
BEGIN EXEC [UspCopyClone] '''+ @BackupClone +''','''+@CloneDBSeqName
+''','''+@FolderCloneDatabase +'''
----ALTER DATABASE ' + @CloneDBSeqName +' SET MULTI_USER
END'
---- PRINT @SqlCreateCloneDB
EXEC sp_Executesql @SqlCreateCloneDB
SET @MinNoofDBClone =@MinNoofDBClone+1
END
END
GO
/****** Object: StoredProcedure [dbo].[UspCopyClone] Script Date: 09-09-2021 10:12:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UspCopyClone](
@pBackupFile NVARCHAR(550),
@pDatabaseName sysname,
@PDataPath NVARCHAR(550)
AS
BEGIN
BEGIN TRY
DECLARE @pDSQL NVARCHAR(MAX);
DECLARE @pDataFileLocation NVARCHAR(550);
DECLARE @pLogFileLocation NVARCHAR(550);
DECLARE @pLogicalDataFileName NVARCHAR(550);
DECLARE @pLogicalLogFileName NVARCHAR(550);
DECLARE @pLogicalNdfFileName NVARCHAR(550);
DECLARE @pDataFileName NVARCHAR(550);
DECLARE @pLogFileName NVARCHAR(550);
DECLARE @pNdfFileName NVARCHAR(550);
SELECT
@pDataFileName = @PDataPath + @pDatabaseName + '.mdf',
--@pDataFileName = 'H:\Shared Folders\Customers\CloneDatabase\Database\'+ @pDatabaseName
+ '.mdf' ,
@pLogFileName = @PDataPath+ @pDatabaseName + '.ldf',
@pNdfFileName = @PDataPath+ @pDatabaseName + '.ndf'
SELECT @pDataFileLocation = CONVERT(VARCHAR(550), SERVERPROPERTY('instancedefaultdatapath')) ,
@pLogFileLocation = CONVERT(VARCHAR(550), SERVERPROPERTY('instancedefaultlogpath'));
IF SERVERPROPERTY('productversion') >= '13.0.1400.361' -- SQL Server 2016 and Above Has
SnapshotUrl
BEGIN
DECLARE @FileLists TABLE
[LogicalName] NVARCHAR(128) ,
[PhysicalName] NVARCHAR(260) ,
[Type] NCHAR(1) ,
[FileGroupName] NVARCHAR(128) ,
[Size] BIGINT ,
[MaxSize] BIGINT ,
[FileId] BIGINT ,
[CreateLSN] DECIMAL(25, 0) ,
[DropLSN] DECIMAL(25, 0) ,
[UniqueId] UNIQUEIDENTIFIER ,
[ReadOnlyLSN] DECIMAL(25, 0) ,
[ReadWriteLSN] DECIMAL(25, 0) ,
[BackupSizeInBytes] BIGINT ,
[SourceBlockSize] INT ,
[FileGroupId] INT ,
[LogGroupGUID] UNIQUEIDENTIFIER ,
[DifferentialBaseLSN] DECIMAL(25, 0) ,
[DifferentialBaseGUID] UNIQUEIDENTIFIER ,
[IsReadOnly] BIT ,
[IsPresent] BIT ,
[TDEThumbprint] VARBINARY(20) ,
[SnapshotUrl] NVARCHAR(336)
);
END
ELSE
BEGIN
RAISERROR ('SnapshotUrl is Only Avialble for SQL Server 2016 and Above. Modify the Definition of
Table Variable and Remove SnapShotURL.', -- Message text.
16, -- Severity.
1 -- State.
);
END
SELECT @pDSQL = ' RESTORE FILELISTONLY FROM DISK = ''' + @pBackupFile + '''';
SELECT @pDSQL;
INSERT @FileLists
EXEC(@pDSQL);
SELECT @pLogicalDataFileName = LogicalName
FROM @FileLists
WHERE Type = 'D' AND [FileId] = 1;
SELECT @pLogicalLogFileName = LogicalName
FROM @FileLists
WHERE Type = 'L' AND [FileId] = 2;
SELECT @pLogicalNdfFileName = LogicalName
FROM @FileLists
WHERE Type = 'D' AND [FileId] = 3;
SELECT @pDataFileLocation ,
@pLogFileLocation ,
@pDatabaseName;
----SELECT @pDSQL = ' RESTORE DATABASE ' + @pDatabaseName + '
----FROM DISK = ''' + @pBackupFile + '''
----WITH MOVE ''' + @pLogicalDataFileName + ''' TO ''' + @pDataFileLocation
---- + @pDataFileName + ''', MOVE ''' + @pLogicalLogFileName + ''' TO '''
---- + @pLogFileLocation + @pLogFileName + '''';
SELECT @pDSQL = ' RESTORE DATABASE ' + @pDatabaseName + '
FROM DISK = ''' + @pBackupFile + '''
WITH MOVE ''' +
@pLogicalDataFileName + ''' TO ''' + @pDataFileName + ''', MOVE ''' +
@pLogicalLogFileName + ''' TO ''' + @pLogFileName + ''' , MOVE ''' +
@pLogicalNdfFileName + ''' TO ''' + @pNdfFileName + '''';
print @pDSQL;
exec SP_Executesql @pDSQL ;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage
END CATCH
END
GO
/****** Object: StoredProcedure [dbo].[UspCopyMaster] Script Date: 09-09-2021 10:12:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UspCopyMaster](
@pBackupFile NVARCHAR(550),
@pDatabaseName sysname,
@PDataPath NVARCHAR(550)
AS
BEGIN
BEGIN TRY
DECLARE @pDSQL NVARCHAR(MAX);
DECLARE @pDataFileLocation NVARCHAR(550);
DECLARE @pLogFileLocation NVARCHAR(550);
DECLARE @pLogicalDataFileName NVARCHAR(550);
DECLARE @pLogicalLogFileName NVARCHAR(550);
DECLARE @pDataFileName NVARCHAR(550);
DECLARE @pLogFileName NVARCHAR(550);
SELECT @pDataFileName = @PDataPath+ @pDatabaseName + '.mdf' ,
@pLogFileName = @PDataPath + @pDatabaseName + '.ldf';
SELECT @pDataFileLocation = CONVERT(VARCHAR(550), SERVERPROPERTY('instancedefaultdatapath')) ,
@pLogFileLocation = CONVERT(VARCHAR(550), SERVERPROPERTY('instancedefaultlogpath'));
IF SERVERPROPERTY('productversion') >= '13.0.1400.361' -- SQL Server 2016 and Above Has
SnapshotUrl
BEGIN
DECLARE @FileLists TABLE
[LogicalName] NVARCHAR(128) ,
[PhysicalName] NVARCHAR(260) ,
[Type] NCHAR(1) ,
[FileGroupName] NVARCHAR(128) ,
[Size] BIGINT ,
[MaxSize] BIGINT ,
[FileId] BIGINT ,
[CreateLSN] DECIMAL(25, 0) ,
[DropLSN] DECIMAL(25, 0) ,
[UniqueId] UNIQUEIDENTIFIER ,
[ReadOnlyLSN] DECIMAL(25, 0) ,
[ReadWriteLSN] DECIMAL(25, 0) ,
[BackupSizeInBytes] BIGINT ,
[SourceBlockSize] INT ,
[FileGroupId] INT ,
[LogGroupGUID] UNIQUEIDENTIFIER ,
[DifferentialBaseLSN] DECIMAL(25, 0) ,
[DifferentialBaseGUID] UNIQUEIDENTIFIER ,
[IsReadOnly] BIT ,
[IsPresent] BIT ,
[TDEThumbprint] VARBINARY(20) ,
[SnapshotUrl] NVARCHAR(336)
);
END
ELSE
BEGIN
RAISERROR ('SnapshotUrl is Only Avialble for SQL Server 2016 and Above. Modify the Definition of
Table Variable and Remove SnapShotURL.', -- Message text.
16, -- Severity.
1 -- State.
);
END
SELECT @pDSQL = ' RESTORE FILELISTONLY FROM DISK = ''' + @pBackupFile + '''';
SELECT @pDSQL;
INSERT @FileLists
EXEC(@pDSQL);
SELECT @pLogicalDataFileName = LogicalName
FROM @FileLists
WHERE Type = 'D';
SELECT @pLogicalLogFileName = LogicalName
FROM @FileLists
WHERE Type = 'L';
SELECT @pDataFileLocation ,
@pLogFileLocation ,
@pDatabaseName;
----SELECT @pDSQL = ' RESTORE DATABASE ' + @pDatabaseName + '
----FROM DISK = ''' + @pBackupFile + '''
----WITH MOVE ''' + @pLogicalDataFileName + ''' TO ''' + @pDataFileLocation
---- + @pDataFileName + ''', MOVE ''' + @pLogicalLogFileName + ''' TO '''
---- + @pLogFileLocation + @pLogFileName + '''';
SELECT @pDSQL = ' RESTORE DATABASE ' + @pDatabaseName + '
FROM DISK = ''' + @pBackupFile + '''
WITH MOVE ''' + @pLogicalDataFileName + ''' TO ''' + @pDataFileName + ''', MOVE ''' +
@pLogicalLogFileName + ''' TO '''
+ @pLogFileName + '''';
print @pDSQL;
exec SP_Executesql @pDSQL ;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage
END CATCH
END
GO
/****** Object: StoredProcedure [dbo].[UspValidateDBandCloneDB] Script Date: 09-09-2021
10:12:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UspValidateDBandCloneDB]
@DBName nvarchar(100) ,
@DirPathDB nvarchar(100) ,
@DirPathClone nvarchar(100),
@NoofClones CHAR(2)
AS
BEGIN
DECLARE @sSQL nvarchar(4000)
SET @sSQL = 'IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = '''+ @DBName +''' )
BEGIN
PRINT ''Database not exist...''
END
ELSE
BEGIN
EXEC [dbo].[UspCloneDB] '''+ @DBName +''', ''' + @DirPathDB+''', ''' + @DirPathClone+''', ''' +
@NoofClones +'''
END'
EXECUTE ( @sSQL)
END
GO
