RESTORE Sql-Server BACKUP using scripts

SQL-Restore.sql

-- #1 determine  logical names / physical names
declare @sourceFile	 varchar(150) = 'D:\Path\To\File\Mybackup.BAK'

RESTORE FILELISTONLY 
	FROM DISK = @sourceFile	
GO -- go clears any variables below here.. need to redefine them again.


-- #2 copy logical filenames from step#1, and define target filenames
/*
Restore full backup WITH MOVE; i.e. Move to a different location than original (logical names remain the same)
*/
declare @sourceFile	varchar(150)	= 'D:\Path\To\File\Mybackup.BAK'
declare @logicalNameDB 	varchar(50) = 'MyData'
declare @logicalNameLOG varchar(50) = 'MyData_log'

declare @targetPath varchar(150)	= 'D:\MyProgramFiles\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\'; -- note the trailing backslash
declare @physicalNameMDF varchar(200) = @targetPath + 'MyDb.mdf'
declare @physicalNameLDF varchar(200) = @targetPath + 'MyDb_log.ldf'


RESTORE DATABASE MyDB -- NEW DBNAME
	FROM DISK = @sourceFile	
WITH 
	MOVE @logicalNameDB		TO	@physicalNameMDF,
	MOVE @logicalNameLOG	TO	@physicalNameLDF
GO



-- #3  Rename Logical names to new name; NB: can't use variables, but you could use dynamic sql, e.g. Exec( @sqlStr )
ALTER DATABASE MyDB  -- NEW DBNAME
	MODIFY FILE ( NAME = 'MyData', NEWNAME = 'MyDB' )
ALTER DATABASE MyDB 
	MODIFY FILE ( NAME = 'MyData_log', NEWNAME = 'MyDB_log' )




-- ////////////////////////////////////////////
-- #4. Restore by Re-attaching from MDF / LDF (not from BACKUP file)
use master
GO

CREATE DATABASE TargeDBName
	ON ( FILENAME = 'D:\Path\To\Data\MyDataDB.mdf' )
	LOG ON ( FILENAME = 'D:\Path\To\Data\MyDataDB_log.ldf')
For attach; 
GO

--> ERROR, if you ommit 'For attach'; ensure you use FOR attach as shown above  
/*
	Msg 1036, Level 16, State 2, Line 5
	File option NAME is required in this CREATE/ALTER DATABASE statement.
	Msg 1036, Level 16, State 2, Line 5
	File option NAME is required in this CREATE/ALTER DATABASE statement.
/*
--> ERROR, use an account with permission to create, e.g. sa.
/*
	CREATE DATABASE permission denied in database 'master'.
*/


-- ////////////////////////////////////////////
-- #5 RENAME A DATABASE using TSQL; (if using the SSMS (GUI), it may fail if you do not set to single-mode first)
USE master;  
GO  

ALTER DATABASE oldNameDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE oldNameDB MODIFY NAME = newNameDB ;
GO  

ALTER DATABASE newNameDB SET MULTI_USER
GO