DEFINITIONS

Definitions More Info.
Definition ID967
TitleSQL
CategoryNOTES
DefinitionBackup restore auto script
Definition DescriptionUSE master;
SET NOCOUNT ON;
SET DEADLOCK_PRIORITY HIGH;
GO

DECLARE @backupFilesPath VARCHAR(256)
DECLARE @dataFilesPath VARCHAR(256)
DECLARE @logFilesPath VARCHAR(256)
DECLARE @replaceDB BIT
DECLARE @recoveryDB BIT
DECLARE @subFolder BIT
DECLARE @exec BIT

DECLARE @DBList TABLE (rowid INT NOT NULL IDENTITY(1, 1), NAME VARCHAR(1000))
DECLARE @cmd VARCHAR(max)
DECLARE @Ncmd NVARCHAR(4000)
DECLARE @dbname VARCHAR(1000)
DECLARE @LogicalName VARCHAR(256)
DECLARE @PhysicalName VARCHAR(256)
DECLARE @LogicalName_log VARCHAR(256)
DECLARE @PhysicalName_log VARCHAR(256)
DECLARE @fileName VARCHAR(256)
DECLARE @counter INT
DECLARE @startid INT
DECLARE @charIndex INT
DECLARE @MDF VARCHAR(256)
DECLARE @NDF VARCHAR(256)
DECLARE @LDF VARCHAR(256)
DECLARE @dataFiles TABLE (rowid INT, fileid INT, physicalname VARCHAR(256))
DECLARE @count INT
DECLARE @ndfCmd VARCHAR(max)
DECLARE @isDataPathNull BIT
DECLARE @isLogPathNull BIT
DECLARE @info NVARCHAR(MAX)

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

SET @backupFilesPath = 'I:\Backup' -- Where are bak files?
SET @dataFilesPath = 'F:\Data' -- Where will create mdf and ndf files?
SET @logFilesPath = 'G:\Log' -- Where will create ldf files?
SET @replaceDB = 1 -- 1: Replace, 0: NoReplace
SET @recoveryDB = 1 -- 1: Recovery, 0: NoRecovery
SET @subFolder = 1 -- 1: Include subfolder, 0: No subfolder
SET @exec = 1 -- 1: Execute, 0: Only generate script

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

SET @info = 'Started: ' + CONVERT(NVARCHAR(19),GETDATE(), 104) + ' ' + CONVERT(NVARCHAR(16),GETDATE(), 108)

SET @startid = 1
SET @ndfCmd = ''

IF (@dataFilesPath IS NOT NULL)
BEGIN
SET @isDataPathNull = 0
IF((SELECT RIGHT(@dataFilesPath,1)) = '\')
SET @dataFilesPath = LEFT(@dataFilesPath,LEN(@dataFilesPath)-1)
END
ELSE
SET @isDataPathNull = 1

IF (@logFilesPath IS NOT NULL)
BEGIN
SET @isLogPathNull = 0
IF((SELECT RIGHT(@logFilesPath,1)) = '\')
SET @logFilesPath = LEFT(@logFilesPath,LEN(@logFilesPath)-1)
END
ELSE
SET @isLogPathNull = 1

IF EXISTS (SELECT name FROM tempdb.sys.tables WHERE name = '#headerTable')
DROP TABLE #headerTable

IF EXISTS (SELECT name FROM tempdb.sys.tables WHERE name = '#fileListTable')
DROP TABLE #fileListTable

CREATE TABLE #headerTable (
[BackupName] NVARCHAR(128),
[BackupDescription] NVARCHAR(255),
[BackupType] SMALLINT,
[ExpirationDate] DATETIME,
[Compressed] TINYINT,
[Position] SMALLINT,
[DeviceType] TINYINT,
[UserName] NVARCHAR(128),
[ServerName] NVARCHAR(128),
[DatabaseName] NVARCHAR(128),
[DatabaseVersion] INT,
[DatabaseCreationDate] DATETIME,
[BackupSize] NUMERIC(20,0),
[FirstLSN] NUMERIC(25,0),
[LastLSN] NUMERIC(25,0),
[CheckpointLSN] NUMERIC(25,0),
[DatabaseBackupLSN] NUMERIC(25,0),
[BackupStartDate] DATETIME,
[BackupFinishDate] DATETIME,
[SortOrder] SMALLINT,
[CodePage] SMALLINT,
[UnicodeLocaleId] INT,
[UnicodeComparisonStyle] INT,
[CompatibilityLevel] TINYINT,
[SoftwareVendorId] INT,
[SoftwareVersionMajor] INT,
[SoftwareVersionMinor] INT,
[SoftwareVersionBuild] INT,
[MachineName] NVARCHAR(128),
[Flags] INT,
[BindingID] UNIQUEIDENTIFIER,
[RecoveryForkID] UNIQUEIDENTIFIER,
[Collation] NVARCHAR(128),
[FamilyGUID] UNIQUEIDENTIFIER,
[HasBulkLoggedData] BIT,
[IsSnapshot] BIT,
[IsReadOnly] BIT,
[IsSingleUser] BIT,
[HasBackupChecksums] BIT,
[IsDamaged] BIT,
[BeginsLogChain] BIT,
[HasIncompleteMetaData] BIT,
[IsForceOffline] BIT,
[IsCopyOnly] BIT,
[FirstRecoveryForkID] UNIQUEIDENTIFIER,
[ForkPointLSN] NUMERIC(25,0),
[RecoveryModel] NVARCHAR(60),
[DifferentialBaseLSN] NUMERIC(25,0),
[DifferentialBaseGUID] UNIQUEIDENTIFIER,
[BackupTypeDescription] NVARCHAR(60),
[BackupSetGUID] UNIQUEIDENTIFIER,
[CompressedBackupSize] BIT
)

IF ((SELECT SUBSTRING(CAST(SERVERPROPERTY('productversion') AS NVARCHAR),1, (CHARINDEX('.',CAST(SERVERPROPERTY('productversion') AS NVARCHAR))-1))) = 10)
BEGIN
ALTER TABLE #headerTable ADD [containment] TINYINT NULL
END
ELSE IF ((SELECT SUBSTRING(CAST(SERVERPROPERTY('productversion') AS NVARCHAR),1, (CHARINDEX('.',CAST(SERVERPROPERTY('productversion') AS NVARCHAR))-1))) > 11)
BEGIN
ALTER TABLE #headerTable ADD [containment] TINYINT NULL,
[KeyAlgorithm] NVARCHAR(32) NULL,
[EncryptorThumbprint] VARBINARY(20) NULL,
[EncryptorType] NVARCHAR(32) NULL
END


CREATE TABLE #fileListTable (
[LogicalName] NVARCHAR(128),
[PhysicalName] NVARCHAR(260),
[Type] CHAR(1),
[FileGroupName] NVARCHAR(128),
[Size] NUMERIC(20,0),
[MaxSize] NUMERIC(20,0),
[fileid] BIGINT,
[CreateLSN] NUMERIC(25,0),
[DropLSN] NUMERIC(25,0),
[UniqueID] UNIQUEIDENTIFIER,
[ReadOnlyLSN] NUMERIC(25,0),
[ReadWriteLSN] NUMERIC(25,0),
[BackupSizeInBytes] BIGINT,
[SourceBlockSize] INT,
[FileGroupID] INT,
[LogGroupGUID] UNIQUEIDENTIFIER,
[DifferentialBaseLSN] NUMERIC(25,0),
[DifferentialBaseGUID] UNIQUEIDENTIFIER,
[IsReadOnly] BIT,
[IsPresent] BIT,
[TDEThumbprint] VARBINARY(32)
)

IF ((SELECT SUBSTRING(CAST(SERVERPROPERTY('productversion') AS NVARCHAR),1, (CHARINDEX('.',CAST(SERVERPROPERTY('productversion') AS NVARCHAR))-1))) >= 13)
BEGIN
ALTER TABLE #fileListTable ADD [SnapshotURL] NVARCHAR(360) NULL
END

IF((SELECT RIGHT(@backupFilesPath,1)) = '\')
SET @backupFilesPath = LEFT(@backupFilesPath,LEN(@backupFilesPath)-1)

SET @cmd = 'DIR ' + @backupFilesPath + '\*.bak /B' + (CASE WHEN (@subFolder = 1) THEN ' /S' ELSE '' END)
SET @Ncmd = @cmd

IF((SELECT value FROM sys.configurations WHERE name = 'xp_cmdshell') = 0)
BEGIN
--Enable xp_cmdshell
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

INSERT INTO @DBList
EXEC xp_cmdshell @Ncmd

--Disable xp_cmdshell
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
END
ELSE
BEGIN
INSERT INTO @DBList (name)
EXEC xp_cmdshell @Ncmd
END

DELETE
FROM @DBList
WHERE NAME IS NULL
OR NAME NOT LIKE '%.bak'
or Name NOT LIKE '%Atlantis%'

IF (@subFolder = 0)
UPDATE @DBList SET NAME = @backupFilesPath + '\' + NAME

IF((SELECT COUNT(*) from @DBList) = 0)
BEGIN
GOTO ExitCode
END

SET @cmd = ''

SELECT @counter = count(NAME)
FROM @DBList

-- Parçali gelen backuplar için eklendi
DECLARE @fileName2 VARCHAR(256), @fileName3 VARCHAR(256), @fileName4 VARCHAR(256)

WHILE (@startid <= @counter)
BEGIN
SELECT @fileName = NAME
FROM @DBList
WHERE rowid = @startid

-- Parçali gelen backuplar için eklendi
Select @fileName2 = @fileName + '02', @fileName3 = @fileName + '03', @fileName4 = @fileName + '04'

DELETE
FROM #headerTable
INSERT INTO #headerTable
EXEC ('RESTORE HEADERONLY FROM disk=''' + @filename + '''')

IF ((SELECT [DatabaseName] FROM #headerTable) NOT IN ('AtlantisDB'))
GOTO ContinueLoop;

DELETE
FROM #fileListTable
INSERT INTO #fileListTable
EXEC ('RESTORE FILELISTONLY FROM disk=''' + @filename + '''')


SELECT @MDF = physicalname
FROM #fileListTable
WHERE TYPE = 'D'
AND fileid = 1

SET @MDF = REVERSE(@MDF)
SET @charIndex = charindex('\', @MDF)
SET @MDF = LEFT(@MDF, @charIndex - 1)
SET @MDF = REVERSE(@MDF)

SELECT @LDF = physicalname
FROM #fileListTable
WHERE type = 'L'

SET @LDF = REVERSE(@LDF)
SET @charIndex = charindex('\', @LDF)
SET @LDF = LEFT(@LDF, @charIndex - 1)
SET @LDF = REVERSE(@LDF)

DELETE
FROM @dataFiles

INSERT INTO @dataFiles
SELECT ROW_NUMBER() OVER (
ORDER BY fileid
) AS rowid
,fileid
,physicalname
FROM #fileListTable
WHERE type IN ('D','F')
AND fileid <> 1

SELECT @count = count(*)
FROM @dataFiles
WHERE fileid <> 1

WHILE (@count > 0)
BEGIN
SELECT @NDF = physicalname
FROM @dataFiles
WHERE rowid = @count

SET @NDF = REVERSE(@NDF)
SET @charIndex = charindex('\', @NDF)
SET @NDF = LEFT(@NDF, @charIndex - 1)
SET @NDF = REVERSE(@NDF)

IF (@isDataPathNull = 1)
BEGIN
SET @dataFilesPath = REVERSE(SUBSTRING(REVERSE((SELECT physicalname FROM @dataFiles WHERE rowid = @count)),charindex('\', REVERSE((SELECT physicalname FROM @dataFiles WHERE rowid = @count)))+1,DATALENGTH(REVERSE((SELECT physicalname FROM @dataFiles WHERE rowid = @count)))))
--print @dataFilesPath
END

SELECT @ndfCmd = @ndfCmd + ', MOVE N''' + (
SELECT logicalname
FROM #fileListTable
WHERE type IN ('D','F')
AND fileid = (
SELECT fileid
FROM @dataFiles
WHERE rowid = @count
)
) + ''' TO N''' + @dataFilesPath + '\' + @NDF + ''''

SET @count = (@count - 1)
END

IF (@isDataPathNull = 1)
BEGIN
SET @dataFilesPath = REVERSE(SUBSTRING(REVERSE((SELECT physicalname FROM #fileListTable WHERE type = 'D' AND fileid = 1)),charindex('\', REVERSE((SELECT physicalname FROM #fileListTable WHERE type = 'D' AND fileid = 1)))+1,DATALENGTH(REVERSE((SELECT physicalname FROM #fileListTable WHERE type = 'D' AND fileid = 1)))))
--print @dataFilesPath
END


IF (@isLogPathNull = 1)
BEGIN
SET @logFilesPath = REVERSE(SUBSTRING(REVERSE((SELECT physicalname FROM #fileListTable WHERE type = 'L')),charindex('\', REVERSE((SELECT physicalname FROM #fileListTable WHERE type = 'L')))+1,DATALENGTH(REVERSE((SELECT physicalname FROM #fileListTable WHERE type = 'L')))))
--print @dataFilesPath
END

IF (@replaceDB = 1)
BEGIN
IF EXISTS (SELECT name FROM master.sys.databases WHERE [state] = 0 AND name = (SELECT databasename FROM #headerTable))
BEGIN
SET @cmd = @cmd + '
ALTER DATABASE ['+ (SELECT databasename FROM #headerTable) + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
'
END
END

-- Parcali backuplar için eklendi
SET @cmd = @cmd + '
PRINT ''Restoring: '' + ''"' + @fileName + '" '' + CONVERT(NVARCHAR(19),GETDATE(), 104) + '' '' + CONVERT(NVARCHAR(16),GETDATE(), 108)

RESTORE DATABASE [' + (
SELECT databasename
FROM #headerTable
) + '] FROM
DISK = N''' + @filename + '''
,DISK = N''' + @filename2 + '''
,DISK = N''' + @filename3 + '''
,DISK = N''' + @filename4 + '''
WITH FILE = 1, MOVE N''' + (
SELECT logicalname
FROM #fileListTable
WHERE type = 'D'
AND fileid = 1
) + ''' TO N''' + @dataFilesPath + '\' + @MDF + ''', MOVE N''' + (
SELECT logicalname
FROM #fileListTable
WHERE type = 'L'
) + ''' TO N''' + @logFilesPath + '\'+ @LDF + '''' + @ndfCmd

IF (@replaceDB = 1)
SET @cmd = @cmd + ', REPLACE'

IF (@recoveryDB = 0)
SET @cmd = @cmd + ', NORECOVERY'

SET @cmd = @cmd + ', NOUNLOAD, STATS = 5
'

IF (@replaceDB = 1)
SET @cmd = @cmd + '
ALTER DATABASE ['+ (SELECT databasename FROM #headerTable) + '] SET MULTI_USER; '


/*
IF (@replaceDB = 1 AND @recoveryDB = 1)
BEGIN
IF EXISTS (SELECT name FROM master.sys.databases WHERE [state] = 0 AND name = (SELECT databasename FROM #headerTable))
BEGIN
SET @cmd = @cmd + '
USE [master];
ALTER DATABASE ['+ (SELECT databasename FROM #headerTable) + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE
'
END
END
*/

ContinueLoop:
SET @ndfCmd = ''
SET @startid = @startid + 1
END

SET @cmd = '-- Start Script --

USE [master];
' + @cmd + '
-- End Script --'


--================================================--


set @cmd = REPLACE(@cmd,'J:\MSSQL\AtlantisDB_AdayLogDB.ndf','F:\Data\AtlantisDB_AdayLogDB.ndf')
set @cmd = REPLACE(@cmd,'J:\MSSQL\AtlantisDB_MesajHareketDB.ndf','F:\Data\AtlantisDB_MesajHareketDB.ndf')
set @cmd = REPLACE(@cmd,'H:\MSSQL\AtlantisDB_PartitionBlob.ndf','F:\Data\AtlantisDB_PartitionBlob.ndf')
set @cmd = REPLACE(@cmd,'M:\MSSQL\AtlantisDB_PartitionBlob_Index.ndf','F:\Data\AtlantisDB_PartitionBlob_Index.ndf')
set @cmd = REPLACE(@cmd,'G:\MSSQL\AtlantisDB_V2.mdf','G:\Data\AtlantisDB_V2.mdf')
set @cmd = REPLACE(@cmd,'H:\MSSQL\AtlantisDB_V2_2.ndf','G:\Data\AtlantisDB_V2_2.ndf')
set @cmd = REPLACE(@cmd,'M:\MSSQL\AtlantisDB_V2_3.ndf','G:\Data\AtlantisDB_V2_3.ndf')
set @cmd = REPLACE(@cmd,'I:\MSSQL\AtlantisDB_V2_history.ndf','G:\Data\AtlantisDB_V2_history.ndf')
set @cmd = REPLACE(@cmd,'J:\MSSQL\AtlantisDB_V2_index.ndf','F:\Data\AtlantisDB_V2_index.ndf')
set @cmd = REPLACE(@cmd,'N:\MSSQL\AtlantisDB_V2_index2.ndf','F:\Data\AtlantisDB_V2_index2.ndf')
set @cmd = REPLACE(@cmd,'K:\MSSQL\AtlantisDB_V2_index3.ndf','F:\Data\AtlantisDB_V2_index3.ndf')
set @cmd = REPLACE(@cmd,'F:\MSSQL\AtlantisDB_V2_index4.ndf','F:\Data\AtlantisDB_V2_index4.ndf')
set @cmd = REPLACE(@cmd,'F:\MSSQL\AtlantisDB_V2_index5.ndf','F:\Data\AtlantisDB_V2_index5.ndf')
set @cmd = REPLACE(@cmd,'L:\MSSQL\AtlantisDB_V2_log.ldf','G:\Log\AtlantisDB_V2_log.ldf')
set @cmd = REPLACE(@cmd,'M:\MSSQL\AtlantisDB_V2_temp.ndf','F:\Data\AtlantisDB_V2_temp.ndf')
set @cmd = REPLACE(@cmd,'F:\MSSQL\AtlantisDB_V2_temp2.ndf','F:\Data\AtlantisDB_V2_temp2.ndf')
set @cmd = REPLACE(@cmd,'F:\MSSQL\AtlantisDB_IsAramalogDB3.ndf','F:\Data\AtlantisDB_IsAramalogDB3.ndf')
set @cmd = REPLACE(@cmd,'K:\MSSQL\AtlantisDB_IsAramaLogDB_Index.ndf','F:\Data\AtlantisDB_IsAramaLogDB_Index.ndf')



--================================================--


IF ( @exec = 1 )
BEGIN
RAISERROR (@info , 0, 1) WITH NOWAIT

EXEC (@cmd)

SET @info = 'Finished: ' + CONVERT(NVARCHAR(19),GETDATE(), 104) + ' ' + CONVERT(NVARCHAR(16),GETDATE(), 108)
RAISERROR (@info , 0, 1) WITH NOWAIT
END
ELSE
BEGIN
SELECT @cmd
END

ExitCode:
IF((SELECT COUNT(*) from @DBList) = 0)
BEGIN
PRINT '- - - File Not Found - - -'
END

DROP TABLE #headerTable
DROP TABLE #fileListTable
SET NOCOUNT OFF
RecordBycunay
Record Date19-08-2019 11:35:26
Düzenle
Kopyala
Sil