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.

The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine

When I try to install the SQL SERVER version installed on my machine the error came

The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine 'MachinName'

So after a search I found a script which solved the issue.

----------------------------------------------------------
@echo on
cd /d c:\temp
if not exist %windir%\system32\wbem goto TryInstall
cd /d %windir%\system32\wbem
net stop winmgmt
winmgmt /kill
if exist Rep_bak rd Rep_bak /s /q
rename Repository Rep_bak
for %%i in (*.dll) do RegSvr32 -s %%i
for %%i in (*.exe) do call :FixSrv %%i
for %%i in (*.mof,*.mfl) do Mofcomp %%i
net start winmgmt
goto End

:FixSrv
if /I (%1) == (wbemcntl.exe) goto SkipSrv
if /I (%1) == (wbemtest.exe) goto SkipSrv
if /I (%1) == (mofcomp.exe) goto SkipSrv
%1 /RegServer

:SkipSrv
goto End

:TryInstall
if not exist wmicore.exe goto End
wmicore /s
net start winmgmt
:End
----------------------------------------------------------

1. open a notepad
2. Copy and paste the above scripts to that
3. save as FIXWMI.CMD (name can be anything, extension should be cmd)
4. Run the script (double click will do)
5. it will ask for a confirmation, allow that.
6. it will take some time to finish.
7. After finishing try, the error might have gone.

It worked for me, so thought of sharing this.

Regards,