MSSQL Database Backup

Database backup is a very important thing if you don’t want to drop any of your valuable data that you are storing. Here, I’ll explain how to create MSSQL database backup.

Step-1:
Create a stored procedure from following script.

CREATE procedure prcDBBackup
as

declare @dbname varchar(100)
select @dbname = CONVERT(VARCHAR(10),GETDATE(),101)+CONVERT(VARCHAR(8),GETDATE(),108)
select @dbname = replace(replace(@dbname,'/',''),':','')
set @dbname = (select'E:\ERP_DB_BACKUP\ERP_DB_' + @dbname + '.BAK')
BACKUP DATABASE ERP
TO DISK = @dbname

Replace 'E:\ERP_DB_BACKUP\ERP_DB’ with your destination path.

Step-2:
Execute following sql command to create a backup manually.

exec prcDBBackup 

To create an automatic daily backup you can create a MS SQL SERVER JOB.

Categories: MSSQL