Backup is probably the most critical aspect for any application, whether it’s a web application or desktop application. Remember Murphy’s Law? If it can happen, it will. For example, if you are are updating a CRM application on a daily basis and the application server crashed, you had better hope you had backups. If not, all your data will be lost. Backups are always necessary when it comes to data protection.

Another problem with protecting data is the schedule of the backups. For example, if a database was set up to be backed up once a day and then that database corrupts or fails, data will have to be re-entered to the point of the last backup. Scheduling backups effectively is essential in protecting your data.

To create a backup of an SQL 2008 database, we need to first code an SQL query that will create a backup file. Standard SQL backup files have a .bat file extension indicating that it is a backup file.

1. Choose a file name for your backup and make a note of the folder location you will backing up to (Locally or remotely)

For example:

Databasename.bat located will be stored in c:\dbbackups

2. Now let’s create the query for defining the backup filename, also adding a time and date stamp to the file. This will help in determining the time and date of a backup you need to restore or archive.

[sql]

DECLARE @BackupFileName varchar(50)

SELECT @BackupFileName = (‘c:\DBbackups\database_’ + REPLACE(convert(nvarchar(20),GetDate(),120),’:’,’-‘) + ‘.bak’)

[/sql]

The above syntax declares the name of the backup file – “database date time.bat” i.e. database_2013-05-03 17-30-00.bat

3. Create a query to perform the backup

[sql]

BACKUP DATABASE yourdatabase TO DISK = @BackupFileName

WITH NOFORMAT, INIT,  NAME = N’ yourdatabase’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

[/sql]

The above query will backup any database that you replace “yourdatabase” with.

4. Put the queries together in a text file and save it as a .sql file. i.e. backscript.sql 

[sql]

DECLARE @BackupFileName varchar(50)

SELECT @BackupFileName = (‘c:\DBbackups\database_’ + REPLACE(convert(nvarchar(20),GetDate(),120),’:’,’-‘) + ‘.bak’)

BACKUP DATABASE yourdatabase TO DISK = @BackupFileName

WITH NOFORMAT, INIT,  NAME = N’yourdatabase’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

[/sql]

5. Test the sql script by running it from a command prompt. If it creates the backup in the folder you specified, then you know it works. For example, if we store the SQL script in the same location as our backup folder and name it sqlbackup.sql we can run the following command:

“C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE” -S local\instance -i c:\DBbackups\sqlbackup.sql

The first part of the command highlighted in  yellow specifies the location of SQLCMD.exe which is the executable that will run our SQL query.

Note: This location may differ depending on Microsoft SQL installations. So it’s best to search for SQLCMD.exe before you run this command or it may fail.

The second part highlighted in grey defines your server/workstation and your SQL instance name. This will vary on your setup.

6. You should now see your backup file generated in the location you set previously. Once we know the backup works we can schedule this file to be run at specific times to achieve automatic backup.

Like this post? Share it!