CODEWITHSUNDEEP
sql-server
Amelia
Amelia

Reputation: 159

How to get .bak file name automatically from directory to restore?

I have used below script to restore databases where I have given .bak file name manually. I need to do the automate process to restore databses. so I tried using stored procedure with xp_DirTree to get .bak file name from remote server folder. but stored procedure executing with no results. when I tried the local path I can able to see .bak files into folder. at first I thought it is permission issue but permissions are fine. could anyone suggest me what is the error causing?

script used to restore databases:

CREATE TABLE #CustomerrestoreFiles(
backupfile VARCHAR(100))
--Drop Table #CustomerrestoreFiles (backupfile)
VALUES  

('Customer_backup_2016_09_15_203001_9888161'),
('Customer_backup_2016_10_10_203001_7101588'),
('Customer_backup_2016_10_14_203001_6621303'),
('Customer_backup_2016_10_15_203001_5397847'),
('Customer_backup_2016_10_16_203002_0291343'),
('Customer_backup_2016_10_17_203002_2861353')


DECLARE @CustomerDBfileToRestore VARCHAR(100), @backupLocation varchar(500), @mdfLocation varchar(500), @ldfLocation varchar(500)

DECLARE restorecursor CURSOR FOR
SELECT backupfile FROM #CustomerrestoreFiles

OPEN restorecursor

FETCH NEXT FROM restorecursor
INTO @CustomerDBfileToRestore

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @backupLocation = 'F:\Customer bak files' + '\' + @CustomerDBfileToRestore + '\' + @CustomerDBfileToRestore +'.bak'
    SET @mdfLocation = 'F:\Files\Customer_Restore_Files\' + @CustomerDBfileToRestore + '.mdf'
    SET @ldfLocation = 'F:\Files\Customer_Restore_Files\' + @CustomerDBfileToRestore + '.ldf'
    RESTORE DATABASE @CustomerDBfileToRestore
    FROM DISK = @backupLocation

    WITH  FILE = 1,

    MOVE 'Customer_Data' TO @mdfLocation,
        MOVE 'Customer_Log'  TO @ldfLocation,
    NOUNLOAD,  REPLACE,  STATS = 1

    FETCH NEXT FROM restorecursor
    INTO @CustomerDBfileToRestore
END
CLOSE restorecursor
DEALLOCATE restorecursor

Upvotes: 0

Views: 254

Answers (1)

Ross Bush
Ross Bush

Reputation: 15175

I bet this is a mapped network drive issue. The drive can't be seen by the sql user. Try UNC naming instead. The article below may prove useful.

How to backup a database to a network drive

Upvotes: 0

Related Questions