Solved

How to Create Database Backup Using SQL Server 2008 Express

Posted on 2011-03-12
19
1,115 Views
Last Modified: 2012-05-11
Hello -

I have been building my first ASP.NET web application using Visual Web Developer 2010 Express and SQL Server 2008 Express.  All is working fine on my development machine.

I want to do some testing on my commercial host, and to load the database to the site they require it to be a backup (BAK) which then gets restored after I upload it.

I have lost about 5 hours trying to install SQL Server Management Studio Express, and still can't get it to install properly.  Before I invest more time there, my first question is ...

... are there any other ways to create a database backup (BAK format) besides Management Studio?

Thank you!
0
Comment
Question by:JMS1965
  • 9
  • 6
  • 4
19 Comments
 
LVL 7

Expert Comment

by:relliott66
Comment Utility
If you need a true backup that is what I would use.  If not, you could always cheat, take the DB offline and then just create a backup of it using a simple copy. If you reach a point where the DB needs to be restored, reverse the process.
0
 
LVL 12

Expert Comment

by:jmcmunn
Comment Utility
0
 

Author Comment

by:JMS1965
Comment Utility
Hello -

I tried the command line option, but am getting this error:

[ODBC Driver Manager] Data source name not found and no default driver specified

Here is the command line I'm using:

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\OSQL.EXE"      -E -Q "BACKUP DATABASE C:\... path to the database ... \ASPNETDB.MDF TO DISK='C:\ ... path to the backup location ...\ASPNETDB.BAK' WITH FORMAT"

Although the OSQL.EXE file exists in the path I specified, I'm wondering if this error is occuring because I don't have the SQL Server Management Studio Express installed.

Any ideas?
0
 
LVL 12

Expert Comment

by:jmcmunn
Comment Utility

That command appears to take the database name (or rather the name of an ODBC datasource) not the mdf file.  If you don't already have an odbc datasource for that mdf you should be able to create one.  Search Google for directions on how to do that, it should be trivial.
0
 

Author Comment

by:JMS1965
Comment Utility
OK, I'm still getting errors ... "SQL Native Client is not installed correctly. To correct this, run SQL Server Setup."

So, I think I'm going to have to tackle the installation issue (I was trying to avoid it for now, but knew I would have to tackle it eventually).  I'll do some more research and try to get SQL Server Management Studio Express installed.  If I stay stuck, I'll post another question about the issues I'm having on that.

Thanks!
0
 
LVL 12

Expert Comment

by:jmcmunn
Comment Utility

Are the issues that you are having specific to your host, or generic install issues?  We could likely help with generic install issues.  Host specific, you would be better off asking the support staff at your host.
0
 
LVL 7

Expert Comment

by:relliott66
Comment Utility
Concur with jmcmunn,  Let us know what the errors are when you are trying to install the management sudio...
0
 

Author Comment

by:JMS1965
Comment Utility
This is on my local machine. Thanks for offering to help; I'll go back at it tonight and if I get stuck again I'll post the error here.

Thanks!
0
 
LVL 7

Expert Comment

by:relliott66
Comment Utility
any luck?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:JMS1965
Comment Utility
No luck so far. I've been documenting my steps and the error messages; during this process I discovered something else to try. Will give that a go this afternoon and if that doesn't work, I'll post my issues here.

Thanks for following up!
0
 

Author Comment

by:JMS1965
Comment Utility
Okay, I still can’t get the SQL Server Management Tools installed.  Here’s what’s happening …
I originally installed Visual Web Developer 2010 Express from a CD that came with a book. That installation also installed SQL Server Express. (This is on a stand-alone PC; no network.) This has worked fine for building the database from within VWD, but I later learned I also need the SQL Server Management Tools. I have downloaded a number of files from Microsoft to accomplish this, and have turned off Windows firewall, but haven’t been able to get it to work.

The first problem came right after starting the Setup program, where I received this message: "This program has known compatibility issues ... After SQL Server Setup completes, you must apply SQL Server 2008 Service Pack 1 (SP1) or a later service pack before you run SQL Server 2008 on this version of Windows". So, I downloaded SP2 and tried to install it. The install progress stopped at about 40%, and the description under the progress bar read "SqlEngineConfigAction_patch_validation_Cpu64". The error read "Registry properties are not valid under this context." Clicking the help button on the error window produced "We’re sorry. There is no additional information about this issue." Installation resumed after clicking OK on the error message.  At the last screen "Setup process complete", I got the "SQL Server 2008 Setup has stopped working. Windows will close the program and notify you if a solution is available."

I then tried installing SQL Server 2008 R2 Express With Tools, which includes Management Tools.  
This time the Installation Progress stopped about 60% complete with this error: "Error writing to file: c:\Config.Msi\54c67c9.rbf. Verify that you have access to that directory." I don’t have a "Config.msi" folder on the C drive. After clicking OK on this message, I reached the "Setup process complete" screen which read: "Management Tools - Basic - Failed. Error: One or more affected files have operations pending. You must restart your computer to complete this process." After clicking OK, I got "SQL Server 2008 R2 Setup has stopped working correctly. Windows will close the program and notify you if a solution is available."

I then decided that maybe my original installation was causing the problem, so I uninstalled SQL Server Express with the intention of reinstalling it from this file, along with Management Tools. I uninstalled SQL Server 2008 Express (through Control Panel); but when I finished and re-started the PC, these programs were still in the Uninstall or Change a Program List: Microsoft SQL Server 2008 R2 (64-bit), and Microsoft SQL Server 2008 RS Setup (English). Also, I opened my project in Visual Web Developer and could still connect to the database and open the table structures.

I then tried installing SQL Server 2008 R2 Express with Tools again. This time I saw some different screens during the setup process, including the License Agreement. At the Feature Selection screen, I accepted the default which was Select All (Database Engine Services; SQL Server Replication; Management Tools - Basic; SQL Client Connectivity SDK). At the Instance Configuration screen: Default Instance (MSSQLSERVER); STILL showed installed Instance of "SQLEXPRESS"; it wouldn't let me select "Named Instance" and name it SQLEXPRESS. I didn’t want to install a new instance, and I had tried to uninstall SQL Server Express so that it would install a single instance, with Management Tools. Since it wouldn’t let me do that, I cancelled the setup.

I’m guessing that if I could uninstall what’s currently there and then re-install with tools, all would be well, but I can’t seem to get there from here!

Any advice would be much appreciated!  Please let me know if there's anything else I can provide that might help resolve this.
0
 
LVL 12

Accepted Solution

by:
jmcmunn earned 250 total points
Comment Utility

Can you finish removing the rest of the SQL stuff from the add/remove programs?  It definitely sounds like you have about half of an instance installed right now...just enough to create issues.

You'll want to make sure you find the MDF/LDF files of any databases and make sure they are backed up safely before removing everything just in case, but then try to remove everything that pertains to SQL server from your list and start fresh.  I think you have the right idea at this point, and the original issue is likely a conflict of whatever version you tried to install and the free version you originally had.  :-(
0
 

Author Comment

by:JMS1965
Comment Utility
I will try again to remove all "Microsoft SQL ... " listings in the Add/Remove program section of the Control Panel, and will let you know what happens.

Thanks!
0
 
LVL 7

Assisted Solution

by:relliott66
relliott66 earned 250 total points
Comment Utility
tis might help:
http://msdn.microsoft.com/en-us/library/ms143412.aspx

one person actually had to do a repair install and then uninstall...
0
 

Author Comment

by:JMS1965
Comment Utility
Great news! (And one more - hopefully final - question for this thread)

I was able to install Management Tools - finally! I never did get the SQL Server Express fully uninstalled, so I kept working until I got it reinstalled properly. Once that was done, then the Management Tools installed without a problem.

For anyone reading this in the future, one thing I learned throughout this process is that it is critical to make sure any downloads from Microsoft for SQL Server are all for the same version. Sounds logical, but they have so many downloads and the program names are so long with only minor variations that it's easy to get them mixed up. In my case, I had some downloads that were for SQL Server 2008 Express, when I really needed SQL Server 2008 R2 Express.

The other key to this working was the selection on the first screen of the Setup where you select the "Installation Type". I had been selecting the first option "New installation or add features to existing ...", but it worked correctlly when I selected the "Upgrade ... " option.

The files I used successfully in this last install attempt were:
SQLFULL_x64_ENU.exe
SQLManagementStudio_x64_ENU

------------------------------------
New question:
Now I have Management Studio installed, but of course I've never used it. When I open it, I'm asked to "Connect to Server".  The default Server Name says "(local)", but that doesn't work. The drop-down for that field has a Browse for more ... option, but the Local Servers list comes up empty when that is selected.

How do I get it to connect so that I can work with the database from my Web Application? Again, this is on a stand-alone PC, where I'm developing a web app that will eventually be deployed to a commerical host.

Thanks again for all your help and support!
0
 
LVL 12

Expert Comment

by:jmcmunn
Comment Utility
you mentioned earlier that you had an instance called SqlExpress...if that is still the case, your server would be:

(local)\sqlexpress

You should be able to check a registry key to find what instances are installed if the management studio is not showing them...but after all of the installing/uninstalling maybe something is still messed up under the hood.  Can you connect ok still through visual studio?

I think this is the reg key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL
0
 

Author Comment

by:JMS1965
Comment Utility
jmcmunn -

(local)\sqlexpress did the trick!

All set for now -- Thank you!
0
 
LVL 12

Expert Comment

by:jmcmunn
Comment Utility
Glad to help!  If we're all set here, please feel free to assign points to the answers that helped.  We're all glad to have helped.
0
 

Author Closing Comment

by:JMS1965
Comment Utility
Thanks again for sticking with me through this and helping me get it resolved!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now