Backup your database using an SQL command (Export to .bak)

You can backup your database to a .bak file using a sql command. This would let you issue a backup command from within an admin panel of your website.

The sql command used follows this format:

BACKUP DATABASE [NameOfDatabase] TO  DISK = N'D:\path\filename.bak' WITH NOFORMAT, NOINIT,  NAME = N'FriendlyNameOfDatabase', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 

In the above example the following items would be replaced:

NameOfDatabase

This must match the actual name of your database.

D:\path\filename.bak

The path and filename of the backup file to be generated. You must add on the .bak file extension yourself as it will not be automatically added on for you. If you specify a file that doesn't exist a new file will be created. If you specify a file that does exist and is a valid sql server backup file then the new backup set will be appended to the existing backup.

FriendlyNameOfDatabase

This is a friendly name that will be used to identify your database backup from a list of other backups should you come to restore it. This can be anything but it is normally simply the name of the database.

How to generate this snippet using Microsoft SQL Server Management Studio Express

You can script almost any window of mssms, just look for the Script button in the toolbar of that window.

In this case you would follow the steps that you would take to create a .bak file via mssms but before you press the OK button to create the backup you can script it out.

If you dont already have mssms installed then you can get it from here:

• http://www.microsoft.com/express/sql/download/

Follow these steps:

1. Open SQL Server Management Studio Express
2. Connect to your database server
3. Right click on the database you want to back up
4. Select the Tasks menu item
5. Select the Backup menu item
6. In the Destination section of the Back Up Database window make sure you have selected Disk
7. Click the Add button and type in a location for the backup
8. Make sure you type the .bak on the end of the filename as it will not automatically add this in for you
9. Click the Script button at the top of the window
10. A new query window will be opened pre-populated with the SQL snippet

You can now use this in any place you want to such as a website admin panel.

How to backup several databases at once

Dont forget that as this is a normal sql script you can separate commands with the GO keyword. This lets you chain together as many commands as you like for saving each of your databases to file.

Here is a demo script that backs up two databases to a single .bak file:

BACKUP DATABASE [Project1] TO  DISK = N'D:\path\databases-2009-09-12.bak' WITH NOFORMAT, NOINIT,  NAME = N'Project1-FullDatabaseBackup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 
GO
BACKUP DATABASE [Project2] TO  DISK = N'D:\path\databases-2009-09-12.bak' WITH NOFORMAT, NOINIT,  NAME = N'Project2-FullDatabaseBackup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 
GO

If you specify the same filename for your .bak then sql server just combines them into a single package. This means that when you restore the database you can choose which database backup you want to restore.

You can also export them to their own files just as easily:

BACKUP DATABASE [Project1] TO  DISK = N'D:\path\project1-backup.bak' WITH NOFORMAT, NOINIT,  NAME = N'Project1-FullDatabaseBackup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 
GO
BACKUP DATABASE [Project2] TO  DISK = N'D:\path\project2-backup.bak' WITH NOFORMAT, NOINIT,  NAME = N'Project2-FullDatabaseBackup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 
GO

Using this technique you could run the same file every day and the .bak would just keep growing with each daily backup. If you did need to restore to a point in the past you would have them all available to you.