erreur script restore from sql 2008 in sql 2017

• abdalah.mehdoini

  Ten Centuries

  Points: 1012

  More actions

  December 13, 2018 at 5:35 am

  #373245

  
  Hello everyone
  I have a script that works well on SQL Server 2008 which deals with restoring multi databases by pointing to a backup folder

  if now i want to run it on sql server 2017 it does not work anymore

  Who has an idea about the root cause of the problem ?

  IF EXISTS (SELECT name FROM sysobjects WHERE name = 'migration_lester')
DROP TABLE migration_lester
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'header_lester')
DROP TABLE header_lester
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'cmdshell_lester')
DROP TABLE cmdshell_lester
--Create Tables
--(cmdshell_lester table for the cmdshell command)
--(migration_lester table for the restore filelistonly command)
--(header_lester table for the restore headeronly command)
CREATE TABLE cmdshell_lester(fentry VARCHAR(1000))
CREATE TABLE migration_lester(
LogicalName VARCHAR(1024),
PhysicalName VARCHAR(4000),
type CHAR(1),
FileGroupName VARCHAR(50),
size REAL,
MaxSize REAL,
FileID BIGINT,
CreateLSN NUMERIC(25,0),
DropLSN NUMERIC(25,0),
UniqueID BINARY,
ReadOnlyLSN NUMERIC(25,0),
ReadWriteLSN NUMERIC(25,0),
BackupSizeInBytes BIGINT,
SourceBlockSize INT,
FileGroupID INT,
LogGroupGUID BINARY,
DifferentialBaseLSN NUMERIC(25,0),
DifferentialBaseGUID BINARY,
IsReadOnly BIT,
IsPresent BIT,
TDEThumbprint BINARY
)
CREATE TABLE header_lester (
BackupName VARCHAR(50),
BackupDescription VARCHAR(100),
BackupType INT,
ExpirationDate NVARCHAR(50),
Compressed INT,
Position INT,
DeviceType INT,
UserName VARCHAR(30),
ServerName VARCHAR(30),
DatabaseName VARCHAR(50),
DatabaseVersion INT,
DatabaseCreationDate DATETIME,
BackupSize BIGINT,
FirstLsn BINARY,
LastLsn BINARY,
CheckpointLsn BINARY,
DatabaseBackupLSN BINARY,
BackupStartDate DATETIME,
BackupFinishDate DATETIME,
SortOrder INT,
CodePage INT,
UnicodeLocaleid INT,
UnicodeComparisonStyle INT,
CompatibilityLevel INT,
SoftwareVendorId INT,
SoftwareVersionMajor INT,
SoftwareVersionMinor INT,
SoftwareVersionBuild INT,
MachineName VARCHAR(50),
Flags INT,
BindingId NVARCHAR(50),
RecoveryForkId NVARCHAR(50),
Collation NVARCHAR(50),
FamilyGUID BINARY,
HasBulkLoggedData INT,
IsSnapshot INT,
IsReadOnly INT,
IsSingleUser INT,
HasBackupChecksums INT,
IsDamaged INT,
BeginsLogChain INT,
HasIncompleteMetaData INT,
IsForceOffline INT,
IsCopyOnly INT,
FirstRecoveryForkID BINARY,
ForkPointLSN BINARY,
RecoveryModel NVARCHAR(60),
DifferentialBaseLSN BINARY,
DifferentialBaseGUID BINARY,
BackupTypeDescription nvarchar(60),
BackupSetGUID BINARY,
CompressedBackupSize BIGINT
)
--Declare Variables
DECLARE @path VARCHAR(1024),@restore VARCHAR(1024)
DECLARE @restoredb VARCHAR(2000),@extension VARCHAR(1024),@newpath_ldf VARCHAR(1024)
DECLARE @pathension VARCHAR(1024),@newpath_mdf VARCHAR(1024),@header VARCHAR(500)
--Set Values to the variables
SET @newpath_mdf = 'C:\backup\' --new path wherein you will put the mdf
SET @newpath_ldf = 'C:\backup\' --new path wherein you will put the ldf
SET @path = 'C:\backup\' --Path of the Backup File
SET @extension = '*.BAK'
SET @pathension = 'dir /OD '+@Path+@Extension
--Insert the value of the command shell to the table
INSERT INTO cmdshell_lester EXEC master..xp_cmdshell @pathension
--Delete non backup files data, delete null values
DELETE FROM cmdshell_lester WHERE FEntry NOT LIKE '%.BAK%'
DELETE FROM cmdshell_lester WHERE FEntry IS NULL
--Create a cursor to scan all backup files needed to generate the restore script
DECLARE @migrate VARCHAR(1024)
DECLARE migrate CURSOR FOR
SELECT SUBSTRING(FEntry,40,50) AS 'FEntry' FROM cmdshell_lester
OPEN migrate
FETCH NEXT FROM migrate INTO @migrate
WHILE (@@FETCH_STATUS = 0)BEGIN
--Added feature to get the dbname of the backup file
SET @header = 'RESTORE HEADERONLY FROM DISK = '+''''+@path+@migrate+''''
INSERT INTO header_lester EXEC (@header)
--Get the names of the mdf and ldf
SET @restore = 'RESTORE FILELISTONLY FROM DISK = '+''''+@path+@migrate+''''
INSERT INTO migration_lester EXEC (@restore)
/*Update value of the table to add the new path+mdf/ldf names*/
UPDATE migration_lester SET physicalname = REVERSE(physicalname)
UPDATE migration_lester SET physicalname = SUBSTRING(physicalname,1,CHARINDEX('\',physicalname)-1)
UPDATE migration_lester SET physicalname = @newpath_mdf+reverse(physicalname) WHERE type = 'D'
UPDATE migration_lester SET physicalname = @newpath_ldf+reverse(physicalname) WHERE type = 'L'
/*Added by Erin. - Update database logical and physical names*/
UPDATE header_lester SET DatabaseName = LEFT(@migrate, LEN(@migrate) - 4 )
--UPDATE migration_lester SET LogicalName = LEFT(@migrate, LEN(@migrate) - 4) + '_Data' FROM migration_lester WHERE type = 'D'
--UPDATE migration_lester SET LogicalName = LEFT(@migrate, LEN(@migrate) - 4) + '_Log' FROM migration_lester WHERE type = 'L'
--UPDATE migration_lester SET PhysicalName = LEFT(@migrate, LEN(@migrate) - 4) FROM migration_lester WHERE physicalname LIKE '%mdf'
--UPDATE migration_lester SET PhysicalName = LEFT(@migrate, LEN(@migrate) - 4) FROM migration_lester WHERE physicalname LIKE '%ldf'
/*Set a value to the @restoredb variable to hold the restore database script*/
IF (SELECT COUNT(*) FROM migration_lester) = 2
BEGIN
SET @restoredb = 'RESTORE DATABASE '+(SELECT TOP 1 DatabaseName FROM header_lester)
+' FROM DISK = '+ ''''+@path+@migrate+''''+' WITH norecovery , MOVE '+''''
+(SELECT logicalname FROM migration_lester WHERE type = 'D')+''''
+' TO '+ ''''+(SELECT physicalname FROM migration_lester WHERE physicalname LIKE '%mdf%')
+''''+', MOVE '+''''+ (SELECT logicalname FROM migration_lester WHERE type = 'L')
+''''+' TO '+''''+(SELECT physicalname FROM migration_lester WHERE physicalname LIKE '%ldf%')+''''
PRINT (@restoredb)
END
IF (SELECT COUNT(*) FROM migration_lester) > 2
BEGIN
SET @restoredb = 'RESTORE DATABASE '+(SELECT TOP 1 DatabaseName FROM header_lester)
+' FROM DISK = '+''''+@path+@migrate+''''+'WITH MOVE '
DECLARE @multiple VARCHAR(1000),@physical VARCHAR(1000)
DECLARE multiple CURSOR FOR
SELECT logicalname,physicalname FROM migration_lester
OPEN multiple
FETCH NEXT FROM multiple INTO @multiple,@physical
WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @restoredb=@restoredb+''''+@multiple+''''+' TO '+''''+@physical+''''+','+'MOVE '+''
FETCH NEXT FROM multiple INTO @multiple,@physical
END
CLOSE multiple
DEALLOCATE multiple
SET @restoredb = SUBSTRING(@restoredb,1,LEN(@restoredb)-5)
PRINT (@restoredb)
END
/*Run print @restoredb first to view the databases to be restored
When ready, run exec (@restoredb)*/
EXEC (@restoredb)
/*Clear data inside the tables to give way for the next set of
informations to be put in the @restoredb variable*/
TRUNCATE TABLE migration_lester
TRUNCATE TABLE header_lester
FETCH NEXT FROM migrate INTO @migrate
END
CLOSE migrate
DEALLOCATE migrate
/*Drop Tables*/
DROP TABLE migration_lester
DROP TABLE cmdshell_lester
DROP TABLE header_lester

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  December 13, 2018 at 5:45 am

  #2016206

  Please tell us what the error message said.
  "It doesn't work" is not very helpful.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• abdalah.mehdoini

  Ten Centuries

  Points: 1012

  More actions

  December 13, 2018 at 6:14 am

  #2016208

  I'm having trouble getting the full backup path

  

  

• Thomas Rushton

  SSC-Insane

  Points: 22649

  More actions

  December 13, 2018 at 7:25 am

  #2016217

  That looks more like a security issue - check the permissions on the C:\backup folder and that the SQL Server service account has access to that directory and the files in it.

  Thomas Rushton
  blog: https://thelonedba.wordpress.com