Although there is many ways to backup a SQL Server Database the script below is one that I use quite a lot. If you do not put a drive letter in front of the filename (d:\) then the backup will be placed in the root folder. In most cases it would be located in the backup sub-directory of SQL. For this example it is in the (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup)
folder.

The backupName is designed to never be duplicated it is using Backup + YEAR + MONTH + DAY + HOURS + MINUTES}.bak

If you use this syntax make sure that you handle the number of backups you keep because it will grow pretty quickly.

[code language=”sql”]
Declare @sBackupName varchar(30)

Select @sBackupName =
‘d:\Backup’ +
convert(varchar(4),datepart(yyyy,getdate())) +
convert(varchar(2),datepart(mm,getdate())) +
convert(varchar(2),datepart(dd,getdate())) +
convert(varchar(2),datepart(hh,getdate())) +
convert(varchar(2),datepart(mi,getdate())) + ‘.bak’

BACKUP DATABASE ‘DatabaseName’ TO DISK = @sBackupName
WITH NOFORMAT,
NOINIT,
NAME = @sBackupName,
SKIP,
REWIND,
NOUNLOAD,
STATS = 5

[/code]

Looking for quality web hosting? Look no further than Arvixe Web Hosting!