Create Backup/Restore for SQL Server Database

Following sp can be used to take backup of database

CREATE PROCEDURE [dbo].[sp_backupDaily]
(
@backupPath varchar(300)
)
AS
DECLARE @DBName varchar(255)
set @DBName = 'DatabaseName'
declare @DBFileName varchar(256)
set @DBFileName = datename(dw, getdate()) + '-' +
replace(replace(@DBName,':','_'),'\','_') + '.bak'
exec ('BACKUP DATABASE [' + @DBName + '] TO DISK = N''' + @backupPath + ' ' +
@DBFileName + ''' WITH NOFORMAT, INIT, NAME = N''' +
@DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 100')

Value in @backuppath could be like '\\MyServer\Ayaz_Zaidi\DBFromProg\'

Similarly Database can be restored by using:

CREATE PROCEDURE [dbo].[sp_restoreDaily]
(
@backupPath varchar(300),
@MdfFileLoc varchar(300),
@LdfFileLoc varchar(300)
)
AS
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE DatabaseName
FROM DISK = @backupPath
WITH REPLACE,
MOVE N'DatabaseName' TO @MdfFileLoc,
MOVE N'DatabaseName_log' TO @LdfFileLoc
ALTER DATABASE DatabaseName set multi_user with rollback immediate

Values in variables can be:
@backupPath = '\\spckhi001\Everyone_old\Safia\BackupFileName.bak'
@MdfFileLoc = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MdfFileName.mdf'
@LdfFileLoc = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\LdfFileName.ldf'

Database is first altered to support single user before running restore's script as it cannot restore if other useres will be querying. In end it is revert back to support multiple users.