Loading ...

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

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

(14151)
0
/5
Avg: 0/5: (0 votes)
Published: 9/12/2009 by Matthew Harris

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:

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.

 

Comments (5)

asifqadri
Asif Qadri said:
Thanks Harry it is a good article. Can anyone tell me SQl command to restore a database with old .bak file.
10/22/2009
 · 
 
by
rtpHarry
Asifqadri, I have written a blog post which explains how to restore .bak files step by step at http://runtingsproper.blogspot.com/2009/10/learn-exactly-how-you-can-restore-bak.html
11/7/2009
 · 
 
by
kaushal.pathak
Thanks Harry,Its very helpful article. Now if i want to same thing reverse back like same .bak user want to upload and tried to restore existing .bak file to DB from asp.net web application than how can i do that? can you suggest me please?
11/27/2009
 · 
 
by
rtpHarry
Kaushal, I jave written a blog post which explains how to restore .bak files via the gui at http://runtingsproper.blogspot.com/2009/10/learn-exactly-how-you-can-restore-bak.html Most screens within the Sql Server Management Studio have a "Script" button along the top which can be used to script out the current window to a sql statement. From looking at the screenshots in my article it seems that the script button is on the dialog we use to restore the database so you should be able to script it out and get a good idea of what you need to do. There is one issue that I can think of though - you might get an error stating the database is use if people are currently accessing your database. You might also run in to issues if the database you are trying to restore also has the aspnet application services tables in it that you are currently using. Let me know how you get on!
11/29/2009
 · 
 
by
pushpak.pop
Pushpak Patel said:
thanks dear.. but i dont know how to execute sql commands in asp.net(c#) I want to execute this command by clicking a button.. so can anyone pls help me.. thank you.
5/6/2010
 · 
 
by

Top articles

Quick Vote

What kind of email newsletter would you prefer to receive from CodeAsp.Net?