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 = '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
Looking for quality web hosting? Look no further than Arvixe Web Hosting!