Database etiketine sahip kayıtlar gösteriliyor. Tüm kayıtları göster
Database etiketine sahip kayıtlar gösteriliyor. Tüm kayıtları göster

8 Şubat 2023

SQL Cloning Database

dbbackup.bat

sqlcmd -S SERVERNAME\SQLEXPRESS -E -i backupdb.sql


-------------------------------------------------------------------------------------------

backupdb.sql
 
declare     @backupDB nvarchar(128),
    @restoreDB nvarchar(128),
    @backupFolder nvarchar(512),
    @dataFolder nvarchar(512);

set @backupDB = 'SampleDB';
set @restoreDB = 'SampleDBX';
set @backupFolder = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Backup\';
set @dataFolder = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\';

declare @diskName nvarchar(512);
set @diskName = @backupDB + '-' +REPLACE(REPLACE(CONVERT(VARCHAR(128),GETDATE(),20),' ','-'),':','-') + '.bak';
set @diskName = @backupFolder + @diskName;

backup database @backupDB to disk = @diskName

declare @sql nvarchar(max)
set @sql = 'alter database ' + @restoreDB + ' set single_user with rollback immediate';
exec(@sql);

set @sql = 'restore database ' + @restoreDB + ' from disk = N'''+ @diskName +''''
+ ' with file = 1, '
+ ' move N'''+@backupDB+''' to N''' + @dataFolder + @restoreDB + '.mdf'', '
+ ' move N'''+@backupDB+'_log'' to N''' + @dataFolder + @restoreDB + '_log.ldf'', '
+ ' KEEP_REPLICATION,  NOUNLOAD,  REPLACE,  STATS = 5'
exec(@sql);

set @sql = 'alter database ' + @restoreDB + ' set MULTI_USER';
exec(@sql);

7 Kasım 2017

İki Veri Tabanının Tablolarını Karşılaştırmak

Karşılaştırma yapılacak her iki database için aşağıdaki SQL komutu ile sütunlar çağrılır.

Direk SQL Üzerinden Karşılaştırma Yapmak İçin

-- TÜM TABLOLAR İÇİN

SELECT * FROM [ASILDATABASE].INFORMATION_SCHEMA.COLUMNS G left outer join [KONTROLDATABASE].INFORMATION_SCHEMA.COLUMNS A on A.TABLE_NAME=G.TABLE_NAME and A.COLUMN_NAME=G.COLUMN_NAME where A.COLUMN_NAME is null

-- SADECE BİR TABLO İÇİN
declare @SourceTable varchar(100) declare @ControlTable varchar(100) set @SourceTable = 'GERCEKTABLO' set @ControlTable = 'KONTROLEDILENTABLO' SELECT 'ALTER TABLE '+@ControlTable+' ADD '+ G.COLUMN_NAME + ' '+DATA_TYPE + CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN '' ELSE '('+CONVERT(VARCHAR(10),CHARACTER_MAXIMUM_LENGTH)+')' END AS ALTERQUERY, * FROM [SOURCEDATABASE].INFORMATION_SCHEMA.COLUMNS G WHERE G.TABLE_NAME=@SourceTable AND NOT EXISTS ( SELECT * FROM [CONTROLDATABASE].INFORMATION_SCHEMA.COLUMNS A WHERE A.TABLE_NAME=@ControlTable AND A.COLUMN_NAME=G.COLUMN_NAME)


ya da program üzerinden.


SELECT TABLE_CATALOG,TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLUMN_DEFAULT, NUMERIC_PRECISION, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS ORDER BY TABLE_CATALOG,TABLE_NAME,COLUMN_NAME

Var olan sütunlar aşağıdaki metod ile karşılaştırılır.

public static List<string> HasErrors(this Model.Column thisColumn,
 List<Model.Column> otherColumns)
{
var errors = new List<string>();
var otherColumn = new Model.Column();
try
{
otherColumn = otherColumns.FirstOrDefault 
(q => q.TableName == thisColumn.TableName && 
 q.ColumnName == thisColumn.ColumnName);
if (otherColumn == null)
{
errors.Add(String.Format("{0}.{1}.{2} doesn't 
exist in the other database",  
thisColumn.TableCatalog, thisColumn.TableName,
 thisColumn.ColumnName));
}
}
catch (Exception ex)
{
errors.Add(ex.Message);
}

if (errors.Count == 0)
{


if (thisColumn.DataType != otherColumn.DataType)
errors.Add(String.Format("{0}.{1}.{2} DataType 
is not same as {3}.{4}.{5}",
 thisColumn.TableCatalog, thisColumn.TableName,
 thisColumn.ColumnName, otherColumn.TableCatalog,
 otherColumn.TableName, otherColumn.ColumnName));

if (thisColumn.CharacterMaximumLength != otherColumn 
.CharacterMaximumLength)
errors.Add(String.Format("{0}.{1}.{2} 
CharacterMaximumLength is not same as {3}.{4}.{5}",
 thisColumn.TableCatalog, thisColumn.TableName,  
thisColumn.ColumnName, otherColumn.TableCatalog,  
otherColumn.TableName, otherColumn.ColumnName));

if (thisColumn.IsNullable != otherColumn.IsNullable)
errors.Add(String.Format("{0}.{1}.{2} IsNullable 
is not same as {3}.{4}.{5}", thisColumn.TableCatalog,  
thisColumn.TableName, thisColumn.ColumnName, 
 otherColumn.TableCatalog, otherColumn.TableName,  
otherColumn.ColumnName));

if (thisColumn.ColumnDefault != otherColumn.ColumnDefault)
errors.Add(String.Format("{0}.{1}.{2} ColumnDefault 
is not same as {3}.{4}.{5}", thisColumn.TableCatalog,
 thisColumn.TableName, thisColumn.ColumnName, 
 otherColumn.TableCatalog, otherColumn.TableName,  
otherColumn.ColumnName));

if (thisColumn.NumericPrecision != otherColumn.NumericPrecision)
errors.Add(String.Format("{0}.{1}.{2} NumericPrecision 
is not same as {3}.{4}.{5}", thisColumn.TableCatalog,  
thisColumn.TableName, thisColumn.ColumnName, 
 otherColumn.TableCatalog, otherColumn.TableName,
 otherColumn.ColumnName));

if (thisColumn.CollationName != otherColumn.CollationName)
errors.Add(String.Format("{0}.{1}.{2} CollationName is not
 same as {3}.{4}.{5}", thisColumn.TableCatalog,  
thisColumn.TableName, thisColumn.ColumnName,  
otherColumn.TableCatalog, otherColumn.TableName,  
otherColumn.ColumnName));

}
if (errors.Count == 0)
otherColumns.Remove(otherColumn);
return errors;
}




Kodlar aşağıdaki linktedir

https://github.com/AmagiTech/DatabaseComparer