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)
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.
Declare @sBackupName varchar(30)
Select @sBackupName =
convert(varchar(2),datepart(mi,getdate())) + ‘.bak’
BACKUP DATABASE ‘DatabaseName’ TO DISK = @sBackupName
NAME = @sBackupName,
STATS = 5
Looking for quality web hosting? Look no further than Arvixe Web Hosting!