February 14, 2021 at 2:41 pm
Hi,
I get SQL backup's of a database that I need to restore on a weekly basis, but this backup has an unknown and changing name.
I can list the name with:
RESTORE FILELISTONLY FROM DISK = 'D:\EOLBackup.BAK' WITH FILE = 1
and I get a Logical and Physical name, but how do I apply this name in a script to automatically restore to a database (in my case [010])
Can anyone set me (newbie) in the right direction?
Thanks...
February 14, 2021 at 9:34 pm
Something along the lines below, adjust it as needed to match your environment. You didn't say anything about changing path names for the files, so I just used the paths from the backup file.
DECLARE @sql nvarchar(max)
IF OBJECT_ID('tempdb.dbo.#restore_filelistonly') IS NOT NULL
DROP TABLE #restore_filelistonly;
CREATE TABLE #restore_filelistonly (
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1) NULL,
FileGroupName nvarchar(128) NULL,
Size numeric(20,0),
MaxSize numeric(20,0),
FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0) NULL,
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0) NULL,
ReadWriteLSN numeric(25,0) NULL,
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0) NULL,
DifferentialBaseGUID uniqueidentifier NULL,
IsReadOnly bit NULL,
IsPresent bit NULL,
TDEThumbprint varbinary(32) NULL,
SnapshotURL nvarchar(360) NULL
)
INSERT INTO #restore_filelistonly (
LogicalName, PhysicalName, Type,
FileGroupName,
Size, MaxSize, FileID,
CreateLSN, DropLSN, UniqueID,
ReadOnlyLSN, ReadWriteLSN,
BackupSizeInBytes, SourceBlockSize,
FileGroupID, LogGroupGUID,
DifferentialBaseLSN, DifferentialBaseGUID,
IsReadOnly, IsPresent,
TDEThumbprint, SnapshotURL
)
EXEC('RESTORE FILELISTONLY FROM DISK = ''D:\EOLBackup.BAK''')
--SELECT * FROM #restore_filelistonly
SELECT @sql = STUFF((
SELECT ', ' + 'MOVE ''' + LogicalName + ''' TO ''' + PhysicalName + ''''
FROM #restore_filelistonly
ORDER BY FileID
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
, 1, 2, '')
SET @sql = N'RESTORE DATABASE [010] FROM DISK = ''D:\EOLBackup.BAK'' WITH RECOVERY, ' + @sql
SELECT @sql AS [--sql]
--EXEC(@sql)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 14, 2021 at 10:49 pm
Thanks for your help Scott, but to "adjust this as needed" is going way over my head.
I am sure this is what I need, but I am not able to adjust it, or understand what is happening.
Do you mind explaining it a little, so I can follow along and make the adjustments?
February 14, 2021 at 10:55 pm
This works:
RESTORE DATABASE [010]
FROM DISK = N'D:\EOLBackup.bak'
WITH FILE = 1,
MOVE N'EOLBackup_{7b720fb4-e0b8-4476-a97f-fcb8cf291344}' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\010.mdf',
MOVE N'EOLBackup_{7b720fb4-e0b8-4476-a97f-fcb8cf291344}_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\010_log.ldf',
NOUNLOAD,
STATS = 5
but the logical name is changing with every backup I receive...
February 15, 2021 at 10:47 am
This was removed by the editor as SPAM
February 15, 2021 at 3:21 pm
Here's the revised code. It should work exactly as written now.
DECLARE @sql nvarchar(max)
IF OBJECT_ID('tempdb.dbo.#restore_filelistonly') IS NOT NULL
DROP TABLE #restore_filelistonly;
CREATE TABLE #restore_filelistonly (
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1) NULL,
FileGroupName nvarchar(128) NULL,
Size numeric(20,0),
MaxSize numeric(20,0),
FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0) NULL,
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0) NULL,
ReadWriteLSN numeric(25,0) NULL,
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0) NULL,
DifferentialBaseGUID uniqueidentifier NULL,
IsReadOnly bit NULL,
IsPresent bit NULL,
TDEThumbprint varbinary(32) NULL,
SnapshotURL nvarchar(360) NULL
)
INSERT INTO #restore_filelistonly (
LogicalName, PhysicalName, Type,
FileGroupName,
Size, MaxSize, FileID,
CreateLSN, DropLSN, UniqueID,
ReadOnlyLSN, ReadWriteLSN,
BackupSizeInBytes, SourceBlockSize,
FileGroupID, LogGroupGUID,
DifferentialBaseLSN, DifferentialBaseGUID,
IsReadOnly, IsPresent,
TDEThumbprint, SnapshotURL
)
EXEC('RESTORE FILELISTONLY FROM DISK = ''D:\EOLBackup.BAK''')
--SELECT * FROM #restore_filelistonly
SELECT @sql = STUFF((
SELECT ', ' + 'MOVE ''' + LogicalName + ''' TO ''' + PhysicalName + ''''
FROM #restore_filelistonly
ORDER BY FileID
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
, 1, 2, '')
SET @sql = N'RESTORE DATABASE [010] FROM DISK = ''D:\EOLBackup.BAK'' WITH RECOVERY, FILE = 1, STATS = 05, '
+ @sql
PRINT 'RESTORE command being run follows below:'
PRINT @sql
EXEC(@sql)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 15, 2021 at 10:12 pm
Thanks again for your help Scott.
I get this returned:
(2 rows affected)
RESTORE command being run follows below:
RESTORE DATABASE [010] FROM DISK = 'D:\EOLBackup.BAK' WITH RECOVERY, FILE = 1, STATS = 05, MOVE 'EOLBackup_{7b720fb4-e0b8-4476-a97f-fcb8cf291344}' TO 'h:\MSSQL\Data\EOLBackup_{7b720fb4-e0b8-4476-a97f-fcb8cf291344}.mdf', MOVE 'EOLBackup_{7b720fb4-e0b8-4476-a97f-fcb8cf291344}_log' TO 'h:\MSSQL\Logs\EOLBackup_{7b720fb4-e0b8-4476-a97f-fcb8cf291344}_log.ldf'
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "h:\MSSQL\Data\EOLBackup_{7b720fb4-e0b8-4476-a97f-fcb8cf291344}.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'EOLBackup_{7b720fb4-e0b8-4476-a97f-fcb8cf291344}' cannot be restored to 'h:\MSSQL\Data\EOLBackup_{7b720fb4-e0b8-4476-a97f-fcb8cf291344}.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "h:\MSSQL\Logs\EOLBackup_{7b720fb4-e0b8-4476-a97f-fcb8cf291344}_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'EOLBackup_{7b720fb4-e0b8-4476-a97f-fcb8cf291344}_log' cannot be restored to 'h:\MSSQL\Logs\EOLBackup_{7b720fb4-e0b8-4476-a97f-fcb8cf291344}_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Completion time: 2021-02-15T23:06:36.7593975+01:00
February 16, 2021 at 12:25 am
changing part of Scott's code should work.
SELECT @sql = STUFF((
SELECT ', ' + 'MOVE ''' + LogicalName + ''' TO '''
+ case
when type = 'L'
then 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\010_log.ldf'
else 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\010.mdf'
end
+ ''''
FROM #restore_filelistonly
ORDER BY FileID
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
, 1, 2, '')
February 16, 2021 at 12:22 pm
Thanks for helping also.
I get these messages when running the script.
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
RESTORE command being run follows below:
Completion time: 2021-02-16T13:14:57.0326251+01:00
Furthermore, I noticed that the commented line
SELECT * FROM #restore_filelistonly
gives no data. Is that correct?
February 16, 2021 at 1:38 pm
you changed something on your script as the output you had given before meant the script supplied by Scott worked.
you may have dropped 1 column from the table definition (or add new ones)
I've just copied and tried it on my own instance and it works as expected
February 16, 2021 at 3:29 pm
In your specific version of SQL, there may be different columns returned by the FILELISTONLY command. Run a FILELISTONLY command in the main window and compare the columns that come back from it to the columns in the temp table.
As I noted before, in your original example you didn't provide different path names for the restored files:
You didn't say anything about changing path names for the files, so I just used the paths from the backup file.
Keep in mind, we know NOTHING about your dbs and files.
DECLARE @data_path varchar(255);
DECLARE @log_path varchar(255);
DECLARE @sql nvarchar(max);
SET @data_path = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\'
SET @log_path = 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\'
IF OBJECT_ID('tempdb.dbo.#restore_filelistonly') IS NOT NULL
DROP TABLE #restore_filelistonly;
CREATE TABLE #restore_filelistonly (
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1) NULL,
FileGroupName nvarchar(128) NULL,
Size numeric(20,0),
MaxSize numeric(20,0),
FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0) NULL,
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0) NULL,
ReadWriteLSN numeric(25,0) NULL,
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0) NULL,
DifferentialBaseGUID uniqueidentifier NULL,
IsReadOnly bit NULL,
IsPresent bit NULL,
TDEThumbprint varbinary(32) NULL,
SnapshotURL nvarchar(360) NULL
)
INSERT INTO #restore_filelistonly (
LogicalName, PhysicalName, Type,
FileGroupName,
Size, MaxSize, FileID,
CreateLSN, DropLSN, UniqueID,
ReadOnlyLSN, ReadWriteLSN,
BackupSizeInBytes, SourceBlockSize,
FileGroupID, LogGroupGUID,
DifferentialBaseLSN, DifferentialBaseGUID,
IsReadOnly, IsPresent,
TDEThumbprint, SnapshotURL
)
EXEC('RESTORE FILELISTONLY FROM DISK = ''D:\EOLBackup.BAK''')
--SELECT * FROM #restore_filelistonly
SELECT @sql = STUFF((
SELECT ', ' + 'MOVE ''' + LogicalName + ''' TO ''' +
CASE WHEN Type = 'L' THEN ISNULL(@log_path, path_name) ELSE ISNULL(@data_path, path_name) END +
file_name + ''''
FROM #restore_filelistonly
CROSS APPLY (
SELECT
LEFT(PhysicalName, LEN(PhysicalName) - CHARINDEX('\', REVERSE(PhysicalName)) + 1) AS path_name,
RIGHT(PhysicalName, CHARINDEX('\', REVERSE(PhysicalName)) - 1) AS file_name
) AS aliases1
ORDER BY FileID
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
, 1, 2, '')
SET @sql = N'RESTORE DATABASE [010] FROM DISK = ''D:\EOLBackup.BAK'' WITH RECOVERY, FILE = 1, STATS = 05, '
+ @sql
PRINT 'RESTORE command being run follows below:'
PRINT @sql
EXEC(@sql)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 16, 2021 at 4:01 pm
In your specific version of SQL, there may be different columns returned by the FILELISTONLY command
In case helpful I've dug this out of my restore code. Hopefully the version-specific markers I have put in over the years are accurate
CREATE TABLE #restore_filelistonly
(
[LogicalName]nvarchar(128) NULL
, [PhysicalName]nvarchar(260) NULL
, [Type]char(1) NULL
, [FileGroupName]nvarchar(128) NULL
, [Size]numeric(20,0) NULL
, [MaxSize]numeric(20,0) NULL
-- SQL2005
, [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
-- SQL2008
, [TDEThumbprint]varbinary(32) NULL
-- SQL2017
, [SnapshotURL]nvarchar(360) NULL
)
February 16, 2021 at 8:24 pm
It's working now!
I got this message:
(2 rows affected)
RESTORE command being run follows below:
RESTORE DATABASE [010] FROM DISK = 'D:\EOLBackup.BAK' WITH RECOVERY, FILE = 1, STATS = 05, MOVE 'EOLBackup_{7b720fb4-e0b8-4476-a97f-fcb8cf291344}' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\EOLBackup_{7b720fb4-e0b8-4476-a97f-fcb8cf291344}.mdf', MOVE 'EOLBackup_{7b720fb4-e0b8-4476-a97f-fcb8cf291344}_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\EOLBackup_{7b720fb4-e0b8-4476-a97f-fcb8cf291344}_log.ldf'
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "010" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Completion time: 2021-02-16T21:17:39.0552495+01:00
So I did what it says: Use the WITH REPLACE clause, and that did the trick. The backup restores without errors.
Thanks again for helping me out with this issue.
February 17, 2021 at 6:17 am
FWIW I think the MS Default location for database files
C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\
is a dreadful choice. Quite apart from storing them under the PROGRAM FILES folder, also included the SQL Version in the path name makes a mess when the server is upgraded to a later version of SQL. But, hey, that was the choice Microsoft made way-back-when.
Also, personally I would use different folders for DATA and LOG, even if they are on the same disk, so that their separation is implicit in the design in expectation that one day they would be put on different disks for greater data safety 🙂
But you will still be stuck with the MS System Databases "master" / "msdb" etc. being in those stupid locations ...
February 18, 2021 at 10:18 am
You should read this thread: https://dba.stackexchange.com/questions/254796/restore-a-database-with-a-different-name-on-the-same-server
SQL Database Recovery Expert 🙂
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply