21

I have a website I've created in Visual Studio 2008 and I need to take it live. How can I backup the database file to a .bak so I can hand it over to the hosting company to place on the server?

5 Answers5

45

From a SQL prompt:

BACKUP DATABASE MyDatabase TO DISK='E:\MyDatabase.bak'
andyhky
  • 1,798
  • 1
  • 17
  • 30
  • I guess he's using SQL Express and relying on auto attach feature. This won't work in that case. – Mehrdad Afshari May 19 '09 at 16:18
  • If he's using a SQL Express DB, the answer still applies. You just get to a SQL prompt by using OSQL (usually located in your PATH) – andyhky May 19 '09 at 16:20
  • @bofe: The database will be attached to SQLExpress user instance. It won't be there in the primary instance. This will cause a lot of confusion. – Mehrdad Afshari May 19 '09 at 16:28
  • I've tried this SQL prompt before, it didn't work. The database was auto created when I started my project, it is a DotNetNuke website. –  May 19 '09 at 16:41
  • Incorrect syntax at 'Database' <-- that's the name of my database, here is what I entered... BACKUP DATABASE Database TO DISK='C:\Database.bak' –  May 19 '09 at 16:49
  • Rename your database and try again. It may be a reserved word. – andyhky May 19 '09 at 18:34
  • If it's REALLY called Database, try with square brackets like: BACKUP DATABASE [Database] TO DISK – Andomar May 19 '09 at 22:36
9

Go to Microsoft Server Management Studio and right click on your database name. Go to "Tasks" -> "Back Up..."

Then assign your properties, ensure that "Backup Type" is full.

Then at the right there is a button "Add" press that and set your filename, ensure that you place the extension .bak at the end of the file name.

Finally hit ok and wait for the backup to complete.

MSDN also has an article that explains this http://msdn.microsoft.com/en-us/library/ms187510.aspx

Nathan Koop
  • 24,803
  • 25
  • 90
  • 125
  • I can't find no "Back Up..." under "Tasks". Does that mean that my db doesn't support it or do i have to enable it somewhere? I'm hosting my db on the Microsoft Azure cloud. – Jo Smo Jul 06 '16 at 15:04
3

In ssms right click on the database, go to tasks, click back up. should be self explanatory from there.

kscott
  • 1,866
  • 3
  • 25
  • 41
0

You don't need a backup to do so (I assume you are using the AttachDbFilename model that Visual Studio uses by default). Just send them the mdf and tell them to attach it to their SQL server instance.

Mehrdad Afshari
  • 414,610
  • 91
  • 852
  • 789
0

I assume you use SQL Server. This links will guide you:

How to: Back Up a Database (SQL Server Management Studio):

bluish
  • 26,356
  • 27
  • 122
  • 180
NinethSense
  • 8,824
  • 2
  • 23
  • 23