SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restore script not working


Restore script not working

Author
Message
cor_perlee
cor_perlee
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 365
Hi all,

I have been working on this script all day trying to get it to work.
Just can't figure it out. There are no error messages but nothing happens.
The script was copied of the internet and adjusted for my purposes. I have a large number of database that will have to be created soon. All I will get is a folder with a llot of backupfiles and possibly a list with databasenames .

Any help would be greatly appreciated,


-- Use VARCHAR as the restore statement doesn't like NVARCHAR
DECLARE
@data_file_path VARCHAR(512)
, @data_file_1_path VARCHAR(512)
, @log_file_path VARCHAR(512)
, @backup_path VARCHAR(512)
, @backup_extension VARCHAR(4)
, @mdf_extension VARCHAR(4)
, @ndf_extension VARCHAR(4)
, @ldf_extension VARCHAR(4)


-- ** VARIABLES THAT MUST BE SET **--
SET @data_file_path = 'D:\Data\'
SET @data_file_1_path = 'D:\Data\'
SET @log_file_path = 'D:\Logs\'
SET @backup_path = 'D:\Klantenmap\'
-- **----------------------------**--
SET @backup_extension = '.bak'
SET @mdf_extension = '.mdf'
SET @mdf_extension = '.ndf'
SET @ldf_extension = '.ldf'

DECLARE @DATABASES_TO_RESTORE TABLE
(rownum int IDENTITY (1,1) PRIMARY KEY NOT NULL,
backup_name VARCHAR(64),
restore_as VARCHAR(64));

-- ** Declare the Databases to be Restored ** -- INSERT INTO @DATABASES_TO_RESTORE
SELECT '25000994', '25000994'
UNION
SELECT '25001905', '25001905'

-- ** -------------------------------------** --
DECLARE @max_rows INT, @row_count INT
SET @row_count = 1
SELECT @max_rows=count(*) FROM @DATABASES_TO_RESTORE

WHILE @row_count <= @max_rows
BEGIN

DECLARE
@backup_name VARCHAR(32)
, @restore_as VARCHAR(32)
, @logical_data_name VARCHAR(64)
, @logical_data_1_name VARCHAR(64)
, @logical_log_name VARCHAR(64)
, @data_file_full_path VARCHAR(512)
, @data_file_1_full_path VARCHAR(512)
, @log_file_full_path VARCHAR(512)
, @full_backup_path VARCHAR(MAX)
, @cmd VARCHAR(128)
SELECT
@backup_name = backup_name,
@restore_as = restore_as
FROM @DATABASES_TO_RESTORE
WHERE rownum = @row_count
SET @full_backup_path = @backup_path + @backup_name + @backup_extension




DECLARE @filelist TABLE
(LogicalName NVARCHAR(128) NOT NULL,
PhysicalName NVARCHAR(260) NOT NULL,
[Type] CHAR(1) NOT NULL,
FileGroupName NVARCHAR(120) NULL,
Size NUMERIC(20, 0) NOT NULL,
MaxSize NUMERIC(20, 0) NOT NULL,
FileID BIGINT NULL,
CreateLSN NUMERIC(25,0) NULL,
DropLSN NUMERIC(25,0) NULL,
UniqueID UNIQUEIDENTIFIER NULL,
ReadOnlyLSN NUMERIC(25,0) NULL ,
ReadWriteLSN NUMERIC(25,0) NULL,
BackupSizeInBytes BIGINT NULL,
SourceBlockSize INT NULL,
FileGroupID INT NULL,
LogGroupGUID UNIQUEIDENTIFIER NULL,
DifferentialBaseLSN NUMERIC(25,0)NULL,
DifferentialBaseGUID UNIQUEIDENTIFIER NULL,
IsReadOnly BIT NULL,
IsPresent BIT NULL,
TDEThumbprint VARBINARY(32) NULL)



INSERT into @filelist
EXEC ('RESTORE FilelistOnly FROM DISK = ''' + @full_backup_path + '''')

IF @@ROWCOUNT = 2
BEGIN

--SELECT @logical_data_name = LogicalName FROM @filelist WHERE [Type] = 'D'
--SELECT @logical_log_name = LogicalName FROM @filelist WHERE [Type] = 'L'
SET @data_file_full_path = '@data_file_path' + '@restore_as' + '@mdf_extension'

SET @data_file_1_full_path = '@data_file_1_path' + '@restore_as' + '@ndf_extension'

SET @log_file_full_path = '@log_file_path' + '@restore_as' + '@ldf_extension'

RESTORE DATABASE @restore_as
FROM DISK = @full_backup_path WITH FILE = 1,
MOVE N'U4S33_Data' TO @data_file_full_path,
MOVE N'U4S33_Data1' TO @data_file_1_full_path,
MOVE N'U4S33_Log' TO @log_file_full_path

END

ELSE
PRINT 'CANNOT RESTORE DATABASE ' + @restore_as + ' THE BACKUP CONTAINS MORE THAN 1 BACKUP SET'
SELECT @row_count = @row_count + 1
END
Dev
Dev
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4446 Visits: 1602
Usually, we backup all databases with a script. Restore is usually per database (as on need). Why do you want to restore all databases? How many databases you need to restore with this script?

Copying from blogs & running it blindly on PROD server is very dangerous. Test your scripts on Test Server first.
cor_perlee
cor_perlee
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 365
Hi,

The databases are for a cloud invironment.
Al large number of customers will have to be created on the databaseserver.
The customerdatabases will be delivered as .bak files.

Cheers,

Cor
Dev
Dev
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4446 Visits: 1602
If it’s less than 20, I would still prefer writing individual restore commands else your approach.
cor_perlee
cor_perlee
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 365
Actually it os somewhere between 300 and 700

Cheers,

Cor
Dev
Dev
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4446 Visits: 1602
Now I agree with your approach.

Please add a PRINT statement in IF block for debugging and post the results.
IF @@ROWCOUNT = 2


Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54720 Visits: 32784
Looking at the restore command itself, logically it makes sense. You're going to have to break down & issue print statements to validate that everything is working. Instead of trying to run the restores, just issue a select statement so that you see all the data coming back and you can ensure that it's correct to fill in the properties for the restore statement. Then try using the data in a single restore statement. Nothing is jumping out as being especially problematic.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
cor_perlee
cor_perlee
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 365
Hi ,

Actually I added serveral ' print step(n) ' to the script to see how far it would go.

It stops short of this part:

-- ** -------------------------------------** --
DECLARE @max_rows INT, @row_count INT
SET @row_count = 1
SELECT @max_rows=count(*) FROM @DATABASES_TO_RESTORE




The piece that procedes it works just fine.
SELECT '25000994', '25000994'
UNION
SELECT '25001905', '25001905'

A select on the temporary database shows the values.


But after this nothing is printed anymore despite the print statements that follow.


cheers,


Cor
Dev
Dev
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4446 Visits: 1602
Please alter your script with following & let us know the result.
--IF @@ROWCOUNT = 2 
IF 1 = 1


cor_perlee
cor_perlee
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 365
Hi,

Same as before I am afraid.

(2 row(s) affected)
The values in the temporary database are shown. Nothing more.

cheers,

Cor
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search