Simple Way to Backup SQL Server 2012

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.

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!

Tags: , , , , , , , , , | Posted under Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *


3 × 2 =

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>