/* Copy AdventureWorks Database Using Backup File Seth Delconte 4/19/2013 -Backs up original database, restores to new database name -Changes data file, log file, and logical names */ --declare var for backup filepath DECLARE @backup_name_to_restore VARCHAR(MAX) = 'C:\Backups\AdventureWorks2012_'+CONVERT(CHAR(8), GETDATE(), 112)+'.bak' -- restore file --create copy-only backup of source database BACKUP DATABASE AdventureWorks2012 TO DISK = @backup_name_to_restore WITH COPY_ONLY --create other variables DECLARE @new_db_name VARCHAR(100) = 'AdventureWorks2012b', --new db name @old_logical_db_name VARCHAR(100) = 'AdventureWorks2012_Data', -- current logical db name @new_logical_db_name VARCHAR(100) = 'AdventureWorks2012b_Data', -- new logical db name @old_logical_log_name VARCHAR(100) = 'AdventureWorks2012_Log', -- current logical log name @new_logical_log_name VARCHAR(100) = 'AdventureWorks2012b_Log', -- new logical log name @new_mdf_pathname VARCHAR(MAX) = 'C:\Data\AdventureWorks2012b_Data.mdf', -- new mdf name @new_ldf_pathname VARCHAR(MAX) = 'C:\Data\AdventureWorks2012b_Log.ldf', -- new ldf name @set_logical VARCHAR(MAX) --restore as new database RESTORE DATABASE @new_db_name --new db name FROM DISK = @backup_name_to_restore -- restore file WITH MOVE @old_logical_db_name TO @new_mdf_pathname, -- current logical name; new mdf name MOVE @old_logical_log_name TO @new_ldf_pathname -- current logical name; new ldf name --update logical names SET @set_logical = 'ALTER DATABASE '+@new_db_name+' MODIFY FILE (NAME = '''+@old_logical_db_name+''', NEWNAME = '''+@new_logical_db_name+''')'+ ' ALTER DATABASE '+@new_db_name+' MODIFY FILE (NAME = '''+@old_logical_log_name+''', NEWNAME = '''+@new_logical_log_name+''')' EXECUTE (@set_logical)