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);

SQL Express Otomatik Yedek Alma

SQL Express otomatik yedek almak için Görev Zamanlayıcıdan aşağıda ki batch dosyası ile otomatik yedek alınacaktır.

Aşağıda ki backup.bat dosyası ve sqlyedek.sql dosyaları aynı klasörde olacak şekilde yazalım.

backup.bat

d:
cd "D:\YEDEKKLASOR"
sqlcmd -S SQLSERVERADI -U SQLKULLANICIADI -P SQLSIFRE -i

sqlyedek.sql

USE YEDEKALINACAKDATABASEADI
GO
DECLARE @backupPath NVARCHAR(500)
SET @backupPath = 'D:\YEDEKKLASOR\YEDEKDOSYAADI' /* YEDEK ALINAN DATABASENIN YOLU !UZANTISIZ OLACAK! */
SET @backupPath = @backupPath +  FORMAT( GETDATE(),'yyyyMMddHHmmss') +'.bak' /* !!!! BU SATIRI DEĞİŞTİRME !!!! */
BACKUP DATABASE YEDEKALINACAKDATABASEADI
TO DISK = @backupPath
GO

Dosyaları İndir

SQL ile Linked Serverdan Tablo Verileri Kopyalama

exec sp_MSforeachtable 'SET IDENTITY_INSERT ? ON '
exec sp_MSforeachtable 'INSERT INTO ? SELECT * FROM OPENQUERY([LinkedServer], ''select * from ?'')  '
exec sp_MSforeachtable 'SET IDENTITY_INSERT ? OFF '

C# SqlBulkCopy ile büyük miktarda veri ekleme

Örnek olarak ekleyeceğimiz Customer tablosu

 CREATE TABLE [dbo].[Customer](
  Id bigint IDENTITY(1,1) NOT NULL,
  Name [nvarchar](60) NOT NULL,
  BirthDate [datetime] NOT NULL,
     Height INT NULL
     CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
     (
    [ID] ASC
        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
     ) ON [PRIMARY]

C# İle Kullanımı

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace SqlBulkCopyDemo
{
    public class Customer
    {
        public long Id { get; set; }
        public string Name { get; set; }
        public DateTime BirthDate { get; set; }
        public int Height { get; set; }

        public static IEnumerable<Customer> GenerateExampleData(int count)
        {
            int i = 0;
            while (i++ < count)
            {
                yield return new Customer()
                {
                    Name = Guid.NewGuid().ToString(),
                    BirthDate = DateTime.Today.AddDays(-1 * (i % (365 * 50))),
                    Height = new Random(i).Next(150, 190)
                };
            }

        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var customerCount = 5000000;
            var stopWatch = new Stopwatch();
            Console.WriteLine("Generating example data");
            stopWatch.Start();

            var dt = new DataTable();

            //Write Destination Column Names
            dt.TableName = "Customer";
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("BirthDate", typeof(DateTime));
            dt.Columns.Add("Height", typeof(int));

            foreach (var customer in Customer.GenerateExampleData(customerCount))
            {
                dt.Rows.Add(customer.Name, customer.BirthDate, customer.Height);
            }
            Console.WriteLine($"Data generated {stopWatch.Elapsed.ToString()}");

            Console.WriteLine($"SqlBulkCopy starting ");
            stopWatch.Restart();
            using (var con = new SqlConnection("Server=xxx;Database=xxx;User Id=xxx;Password=xxx;"))
            {

                using (var bulkCopy = new SqlBulkCopy(con, SqlBulkCopyOptions.TableLock, null))
                {
                    bulkCopy.DestinationTableName = "Customer";
                    bulkCopy.ColumnMappings.Add("Name", "Name");
                    bulkCopy.ColumnMappings.Add("BirthDate", "BirthDate");
                    bulkCopy.ColumnMappings.Add("Height", "Height");

                    bulkCopy.EnableStreaming = true;
                    bulkCopy.BatchSize = 10000;
                    bulkCopy.NotifyAfter = 1000;
                    bulkCopy.SqlRowsCopied += (sender, e) => Console.WriteLine("RowsCopied: " + e.RowsCopied);

                    var rows = new DataRow[dt.Rows.Count];
                    dt.Rows.CopyTo(rows, 0);

                    con.Open();
                    bulkCopy.WriteToServer(rows);
                }

            }
            stopWatch.Stop();
            Console.WriteLine($"Written to Database: {stopWatch.Elapsed.ToString()}");
            
            Console.ReadKey();
        }
    }
}

SQL ile Web Servis Kullanmak

http://secure.smartbearsoftware.com/samples/testcomplete12/webservices/Service.asmx adresi üzerindeki demo web servisten HelloWorld metodunu çağıracağız. Örnek kod aşağıdadır.

DECLARE @Object INT;
DECLARE @Status INT;
DECLARE @Response NVARCHAR(1000);
 
 
DECLARE @requestBody NVARCHAR(MAX) = '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <HelloWorld xmlns="http://smartbear.com" />
  </soap:Body>
</soap:Envelope>'
 
 
EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @Object OUT;
EXEC sp_OAMethod @Object, 'Open', NULL, 'POST',  'http://secure.smartbearsoftware.com/samples/testcomplete12/webservices/Service.asmx', 'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Host', 'secure.smartbearsoftware.com'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'text/xml'
DECLARE @len INT = len(@requestBody) 
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Length', @len
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'SOAPAction', "http://smartbear.com/HelloWorld"
EXEC sp_OAMethod @Object, 'send', null, @requestBody
 
EXEC sp_OAGetProperty @Object, 'Status', @Status OUT
EXEC sp_OAGetProperty @Object, 'ResponseText', @Response OUTPUT
EXEC sp_OADestroy @Object
 
PRINT @Status
PRINT @Response

Sql Foreach

Bu Örnekte Farklı databaselerde aynı sorguyu kullanmak için foreach Döngüsü Örneği Yapılmıştır.

DECLARE @i int
DECLARE @dbAdi nvarchar(100)
DECLARE @tblDB TABLE (idx smallint Primary Key IDENTITY(1,1),dbAdi nvarchar(100))

INSERT @tblDB SELECT DB_Adi FROM [DatabaselerinIcindeTanimlandigiDatabase].[dbo].[DatabaseListesi]

SET @i = 1
DECLARE @numrows int
SET @numrows = (SELECT COUNT(*) FROM @tblDB)
IF  @numrows > 0
    WHILE (@i <= (SELECT MAX(idx) FROM @tblDB))
    BEGIN
        SET @dbAdi = (SELECT dbAdi FROM @tblDB WHERE idx = @i)
DECLARE @sql_query NVARCHAR(500)
SET @sql_query=N'SELECT * FROM ['+@dbADi+'].[dbo].[OrnekTablo]'
EXEC sp_executesql  @sql_query
SET @i=@i+1
    END

SQL Üzerinde Bağlantı Bilgilerini Görmek

Tüm Bilgileri için

SELECT  *

FROM    sys.dm_exec_connections

WHERE   session_id = @@SPID




Kullandığınız bilgisayarın IP adresi için

SELECT  client_net_address

FROM    sys.dm_exec_connections

WHERE   session_id = @@SPID