-2

I'm New to Sql server and dont have experience in automate restores

I need a script to restore a database from a .bak file and move the logical_data and logical_log files to a specific path.

I can do:

restore filelistonly from disk='D:\backups\my_backup.bak'

This will give me a result set with a column LogicalName, next I need to use the logical names from the result set in the restore command:

restore database my_db_name from disk='d:\backups\my_backups.bak' with file=1, move 'logical_data_file' to 'd:\data\mydb.mdf', move 'logical_log_file' to 'd:\data\mylog.ldf'

How do I capture the logical names from the first result set into variables that can be supplied to the "move" command?

I think the solution might be trivial, but I'm pretty new to SQL Server.

1 Answer 1

0

You should be able to use this. Run it with the select to see your output. Use your values for @dbName and @backup_path

DECLARE @nsql nvarchar(MAX)
DECLARE @backup_path nvarchar(MAX) = 'd:\backups\my_backups.bak'
DECLARE @dbName sysname = 'my_db_name'
DECLARE @logical_log sysname
DECLARE @physical_log sysname
DECLARE @logical_data sysname
DECLARE @physical_data sysname
DECLARE @file TABLE (file_id int, logical_file_name varchar( 256), physical_name varchar(256))

SET @nsql = 'SELECT file_id, name as [logical_file_name],physical_name 
FROM '+QUOTENAME( @dbName)+ '.sys.database_files'

INSERT INTO @file EXEC master .dbo .sp_executesql @nsql

SELECT @logical_data = logical_file_name, @physical_data = physical_name 
FROM @file WHERE file_id = 1
SELECT @logical_log = logical_file_name, @physical_log = physical_name 
FROM @file WHERE file_id = 2

SET @nsql = 'restore database '+QUOTENAME( @dbName)+ ' from disk='''+@backup_path+''' with file=1, move '''+@logical_data+''' to '''+@physical_data+''', move '''+@logical_log+''' to '''+@physical_log+''', REPLACE'
SELECT @nsql
--EXEC master.dbo.sp_executesql @nsql
2
  • i am getting errors Msg 3154, Level 16, State 4, Line 1 The backup set holds a backup of a database other than the existing 'Resore1' database. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. please help me Dec 27, 2017 at 8:43
  • You will need to use REPLACE (i updated the code, stackoverflow.com/a/10204687/424323), this will overwrite the existing Restore1 db with whatever was in .bak. Note that it is reading sys.database_files for your physical and logical disk names. If you don't want that you can just set them to what you want in the declare and comment out that section. Dec 27, 2017 at 17:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .