Sql'de Backup alma ve Restore Etme

Backup Alır, Restore Eder

EXECUTE [dbo].[sp_AlpataDB_Restore]

@DBName = 'AlpataBBS2018',

@DBNameEski = 'AlpataBBS_Istanbul',

@BackupYol = 'C:\Yedek\Alpata2018.bak',

@DataFileYol = 'C:\Yedek\AlpataBBS2018_Data.mdf',

@LogFileYol = 'C:\Yedek\AlpataBBS2018_Log.ldf'

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

USE [master]

GO

/****** Object:  StoredProcedure [dbo].[sp_AlpataDB_Restore]    Script Date: 27.10.2017 15:54:07 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF OBJECT_ID('dbo.sp_AlpataDB_Restore') IS NULL

  EXEC ('CREATE PROCEDURE dbo.sp_AlpataDB_Restore AS RETURN 0;');

GO

ALTER PROCEDURE [dbo].[sp_AlpataDB_Restore]

@DBName varchar(60),

@DBNameEski varchar(60),

@BackupYol varchar(120),

--@DataAd varchar(60),

@DataFileYol varchar(120),

--@LogAd varchar(60),

@LogFileYol varchar(120)

  AS

  BEGIN

       SET NOCOUNT ON;

       DECLARE @Message nvarchar(max)

       DECLARE  @sql nvarchar(MAX),

                    @MDF_Ad nvarchar(4000),

                    @LDF_Ad nvarchar(4000)

       IF DB_ID(@DBName) IS NOT NULL

       BEGIN

          set @sql = N'ALTER DATABASE ' + @DBName + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE '

       execute sp_executesql @sql

          set @sql = N'DROP DATABASE ' + @DBName + ' '

       execute sp_executesql @sql

       END

       SELECT top(1) @MDF_Ad= [name]

       FROM sys.[master_files]

       WHERE [database_id] IN (DB_ID(@DBNameEski)) and [type_desc] ='ROWS'

       ORDER BY [type], DB_NAME([database_id]);

       SELECT top(1) @LDF_Ad= [name]

       FROM sys.[master_files]

       WHERE [database_id] IN (DB_ID(@DBNameEski)) and [type_desc] ='LOG'

       ORDER BY [type], DB_NAME([database_id]);

       BACKUP DATABASE @DBNameEski TO DISK = @BackupYol

       WITH CHECKSUM, COPY_ONLY, FORMAT, INIT, STATS = 10;

       RESTORE DATABASE @DBName FROM DISK = @BackupYol

       WITH CHECKSUM,

       MOVE @MDF_Ad TO @DataFileYol,

       MOVE @LDF_Ad TO @LogFileYol,

       RECOVERY, REPLACE, STATS = 10;

SET @Message = 'Tarih: ' + CONVERT(nvarchar,GETDATE(),120)

  SET @Message = 'Sencer Belge - Alpata Yazılım'

  SET @Message = REPLACE(@Message,'%','%%')

  RAISERROR(@Message,10,1) WITH NOWAIT

END