I had a problem where I need to restore database using shared folder on the network. I tried to copy it locally to my local harddrive but then the connection is not stable enough. Hence I decided to create a script

Single Media

USE MASTER
DECLARE @fromdatabase varchar(50)

DECLARE @todatabase varchar(50)
DECLARE @sql as nvarchar(4000)
DECLARE @BackupPath as nvarchar(250)



/***************************************/
/* Change this string to match the database
	name you want to create a backup of  */
Set @fromdatabase = 'DailySupport-1'
Set @todatabase = 'MyDatabaseName'
/***************************************/



/***************************************/
/*  Which db server are you using?     */
/***************************************/

Set @BackupPath='\\sharednetwork\SQL\DatabasesBackup\'

/***************************************/
/***************************************/


/** Kill off any existing connections **/
SET @sql = ' use master'
SET @sql = @sql + ' EXEC sp_KILLSPIDS ' + @todatabase + ';'
Print @sql
EXEC sp_executesql @sql


/** Perform the restore **/
SET @sql = 'RESTORE DATABASE ' + @todatabase
SET @sql = @sql + ' FROM DISK = N''' + @BackupPath + '' + @fromdatabase + '.bak'''
SET @sql = @sql + ' WITH FILE = 1,NOUNLOAD, REPLACE, STATS=10 ;'
Print @sql
EXEC sp_executesql @sql

Multiple Media(Sometimes the backup source is stripped multiple files hence if you try to run it with above script you will get this error “The media set has 2 media families but only 1 are provided. All members must be provided.”)

USE MASTER
DECLARE @fromdatabase varchar(50)
DECLARE @fromdatabase2 varchar(50)

DECLARE @todatabase varchar(50)
DECLARE @sql as nvarchar(4000)
DECLARE @BackupPath as nvarchar(250)



/***************************************/
/* Change this string to match the database
	name you want to create a backup of  */
Set @fromdatabase = 'DailySupport-1'
SET @fromdatabase2 = 'DailySupport-2'
Set @todatabase = 'MyDatabaseName'
/***************************************/



/***************************************/
/*  Which db server are you using?     */
/***************************************/

Set @BackupPath='\\sharednetwork\SQL\DatabasesBackup\'

/***************************************/
/***************************************/


/** Kill off any existing connections **/
SET @sql = ' use master'
SET @sql = @sql + ' EXEC sp_KILLSPIDS ' + @todatabase + ';'
Print @sql
EXEC sp_executesql @sql


/** Perform the restore **/
SET @sql = 'RESTORE DATABASE ' + @todatabase
SET @sql = @sql + ' FROM DISK = N''' + @BackupPath + '' + @fromdatabase + '.bak'''
SET @sql = @sql + ', DISK = N''' + @BackupPath + '' + @fromdatabase2 + '.bak'''
SET @sql = @sql + ' WITH FILE = 1,NOUNLOAD, REPLACE, STATS=10 ;'
Print @sql
EXEC sp_executesql @sql