-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.

DBA_Fresher
  • 1

1 Answers1

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
Smörgåsbord
  • 11
  • 3
  • 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 – DBA_Fresher Dec 27 '17 at 08:43
  • You will need to use REPLACE (i updated the code, https://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. – Smörgåsbord Dec 27 '17 at 17:06