How to Generate SQL Script to Automate SQL Server Database Backup and Restore Processes

				
					Declare @suffix varchar(100) = '_'+convert(varchar(10),getdate(),112)
Declare @Path  varchar(255) = '\\folder_location\temp\'
Declare @servername  varchar(255) = 'servername'

select
'BACKUP DATABASE ['+sd.name+'] TO  
DISK = N'''+sd.name+'_'+replace(replace(convert(varchar(16),getdate(),121),':',''),' ','_')+'.bak''
WITH  COPY_ONLY, NOFORMAT, INIT,  --VERIFY,
NAME = N'''+sd.name+'-Full Database Backup'', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 5
go' as ToLocalTempShort,


'RESTORE DATABASE ['+sd.name+'] 
FROM  DISK = N'''+sd.name+'_'+replace(replace(convert(varchar(16),getdate(),121),':',''),' ','_')+'.bak''
WITH  FILE = 1,  
MOVE N'''+dbF1.name+''' TO N''F:\Data\'+replace(right(dbF1.[physical_name],charindex('\',reverse(dbF1.[physical_name]),1)-1),'.mdf',@suffix+'.MDF')+''',  
MOVE N'''+dbF2.name+''' TO N''G:\Logs\'+replace(right(dbF2.[physical_name],charindex('\',reverse(dbF2.[physical_name]),1)-1),'.ldf',@suffix+'.LDF')+'''
 ,  NOUNLOAD,  STATS = 5
 go' as ToRestoreFrombackup,

'BACKUP DATABASE ['+sd.name+'] TO  
DISK = N'''+@path+sd.name+'_'+replace(replace(convert(varchar(16),getdate(),121),':',''),' ','_')+'.bak''
WITH  COPY_ONLY, NOFORMAT, INIT,  --VERIFY,
NAME = N'''+sd.name+'-Full Database Backup'', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 5
go' as ToSQLRestoreShare,

'
:connect '+ @servername+'
RESTORE DATABASE ['+sd.name+@suffix+'] 
FROM  DISK = N'''+@path+sd.name+'_'+replace(replace(convert(varchar(16),getdate(),121),':',''),' ','_')+'.bak''
WITH  FILE = 1,

 MOVE N'''+dbF1.name+''' TO N''F:\Data\'+replace(right(dbF1.[physical_name],charindex('\',reverse(dbF1.[physical_name]),1)-1),'.mdf',@suffix+'.MDF')+''',  
 MOVE N'''+dbF2.name+''' TO N''G:\Logs\'+replace(right(dbF2.[physical_name],charindex('\',reverse(dbF2.[physical_name]),1)-1),'.ldf',@suffix+'.LDF')+''',
 '
 +case when dbF3.name is null then '' else 'MOVE N'''+dbF3.name+''' TO N''F:\Data\'+replace(right(dbF3.[physical_name],charindex('\',reverse(dbF3.[physical_name]),1)-1),'.ndf',@suffix+'.NDF')+''',
 ' end
 +case when dbF4.name is null then '' else 'MOVE N'''+dbF4.name+''' TO N''F:\Data\'+replace(right(dbF4.[physical_name],charindex('\',reverse(dbF4.[physical_name]),1)-1),'.ndf',@suffix+'.NDF')+''',
 ' end
 +case when dbF5.name is null then '' else 'MOVE N'''+dbF5.name+''' TO N''F:\Data\'+replace(right(dbF5.[physical_name],charindex('\',reverse(dbF5.[physical_name]),1)-1),'.ndf',@suffix+'.NDF')+''',
 ' end
 +case when dbF6.name is null then '' else 'MOVE N'''+dbF6.name+''' TO N''F:\Data\'+replace(right(dbF6.[physical_name],charindex('\',reverse(dbF6.[physical_name]),1)-1),'.ndf',@suffix+'.NDF')+''',
 ' end
 +case when dbF7.name is null then '' else 'MOVE N'''+dbF7.name+''' TO N''F:\Data\'+replace(right(dbF7.[physical_name],charindex('\',reverse(dbF7.[physical_name]),1)-1),'.ndf',@suffix+'.NDF')+''',
 ' end
 +case when dbF8.name is null then '' else 'MOVE N'''+dbF8.name+''' TO N''F:\Data\'+replace(right(dbF8.[physical_name],charindex('\',reverse(dbF8.[physical_name]),1)-1),'.ndf',@suffix+'.NDF')+''',
 ' end
 +case when dbF9.name is null then '' else 'MOVE N'''+dbF9.name+''' TO N''F:\Data\'+replace(right(dbF9.[physical_name],charindex('\',reverse(dbF9.[physical_name]),1)-1),'.ndf',@suffix+'.NDF')+''',
 ' end
 +case when dbF10.name is null then '' else 'MOVE N'''+dbF10.name+''' TO N''F:\Data\'+replace(right(dbF10.[physical_name],charindex('\',reverse(dbF10.[physical_name]),1)-1),'.ndf',@suffix+'.NDF')+''',
 ' end
 +case when dbF11.name is null then '' else 'MOVE N'''+dbF11.name+''' TO N''F:\Data\'+replace(right(dbF11.[physical_name],charindex('\',reverse(dbF11.[physical_name]),1)-1),'.ndf',@suffix+'.NDF')+''',
 ' end
 +case when dbF12.name is null then '' else 'MOVE N'''+dbF12.name+''' TO N''F:\Data\'+replace(right(dbF12.[physical_name],charindex('\',reverse(dbF12.[physical_name]),1)-1),'.ndf',@suffix+'.NDF')+''',
 ' end
 +' NOUNLOAD,    STATS = 5
 
 alter database ['+sd.name+@suffix+'] set recovery simple
go

:connect '+ @servername+'
USE   ['+sd.name+@suffix+'] 

DBCC SHRINKFILE (N'''+dbF2.name+''' , 0, TRUNCATEONLY)
GO
' as ToRestoreFromSQLRestoreShare
  from sysdatabases sd  (nolock)
	left join sys.master_files dbF1  (nolock) on dbF1.database_id=sd.dbid and dbF1.[file_id]=1
	left join sys.master_files dbF2 (nolock) on dbF2.database_id=sd.dbid and dbF2.[file_id]=2
	left join sys.master_files dbF3 (nolock) on dbF3.database_id=sd.dbid and dbF3.[file_id]=3
	left join sys.master_files dbF4 (nolock) on dbF4.database_id=sd.dbid and dbF4.[file_id]=4
	left join sys.master_files dbF5 (nolock) on dbF5.database_id=sd.dbid and dbF5.[file_id]=5
	left join sys.master_files dbF6 (nolock) on dbF6.database_id=sd.dbid and dbF6.[file_id]=6
	left join sys.master_files dbF7 (nolock) on dbF7.database_id=sd.dbid and dbF7.[file_id]=7
	left join sys.master_files dbF8 (nolock) on dbF8.database_id=sd.dbid and dbF8.[file_id]=8
	left join sys.master_files dbF9 (nolock) on dbF9.database_id=sd.dbid and dbF9.[file_id]=9
	left join sys.master_files dbF10 (nolock) on dbF10.database_id=sd.dbid and dbF10.[file_id]=10
	left join sys.master_files dbF11 (nolock) on dbF11.database_id=sd.dbid and dbF11.[file_id]=11
	left join sys.master_files dbF12 (nolock) on dbF12.database_id=sd.dbid and dbF12.[file_id]=12
	--left join sys.master_files dbF13 (nolock) on dbF13.database_id=sd.dbid and dbF13.[file_id]=13
	--left join sys.master_files dbF14 (nolock) on dbF14.database_id=sd.dbid and dbF14.[file_id]=14

	where sd.name not in ('tempdb')
order by sd.name





/*
SELECT          physical_device_name,
                backup_start_date,
                backup_finish_date,
                backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = @databasename
ORDER BY backup_finish_date DESC
*/