Creating a database backup file using SQL Express

DarkShadow

DarkShadow

DarkShadow

Soldato
Joined
14 Apr 2004
Posts
11,869
Location
UK
I'm in the market for a new job after being laid off and the requirements for a particular job to sauce an interview is to create a C#/ASP.NET app using SQL.

I must submit the source code and the database backup file using SQL. Before my new pc install I had an existing MS SQL Managaement studio from MSDN (student freedbie) that allowed me to do this but I no longer have that. I installed SQL EE 2012 and there's no option to create a backup file and export my data. There is an alternative to create a bcp file but it wouldn't be wise submitting something they'd have to hack to work.

Could someone please suggest some options?
 
Long time no speak, we should catch up some time ;).

Did you download the express edition with tools (SQLEXPRWT_x64_ENU.exe)? It comes with Management Studio Basic which will let you create a backup just fine (at least it has in every other edition, I haven't tried 2012 express).

You can get management studio separately from here.
 
Alternatively this may help: http://msdn.microsoft.com/en-us/library/ms187510.aspx

Just make sure that if you are using sql authentication for the database ensure you give them full instructions on what these should be.

After recently being involved with something similar for a graduate role we had no end of issues getting peoples projects working because they failed to provide details to how they were connecting to the database etc. It meant I had to look through their c# code before I could actually get their code working and was a negative against them straight away. I would suggest before packaging up the code and sending it off is test it on a clean dev machine.

Just my thoughts there. Good luck anyway.
 
Pho said:
Long time no speak, we should catch up some time ;).

Did you download the express edition with tools (SQLEXPRWT_x64_ENU.exe)? It comes with Management Studio Basic which will let you create a backup just fine (at least it has in every other edition, I haven't tried 2012 express).

You can get management studio separately from here.
Click to expand...
Holy moly, it's been such a long time! Drop me a line would be nice to catch up some time :)

I downloaded the full version and just picked my modules. Oddly enough I think there was some conflict going on with an older version I had installed.

davetherave2 said:
Alternatively this may help: http://msdn.microsoft.com/en-us/library/ms187510.aspx

Just make sure that if you are using sql authentication for the database ensure you give them full instructions on what these should be.

After recently being involved with something similar for a graduate role we had no end of issues getting peoples projects working because they failed to provide details to how they were connecting to the database etc. It meant I had to look through their c# code before I could actually get their code working and was a negative against them straight away. I would suggest before packaging up the code and sending it off is test it on a clean dev machine.

Just my thoughts there. Good luck anyway.
Click to expand...
That's exactly what I was trying to avoid as I can can imagine it being a nightmare just to get the dependencies right/spending time faffing about.

Thank you for the advice kind sir :)
 
DarkShadow said:
Holy moly, it's been such a long time! Drop me a line would be nice to catch up some time :)

I downloaded the full version and just picked my modules. Oddly enough I think there was some conflict going on with an older version I had installed.
Click to expand...

I'm notoriously bad at getting around to contacting people at the moment :D.

Hmm.. do you have management studio installed at all? Guessing you must be to interact with the db for testing?
 
Pho said:
I'm notoriously bad at getting around to contacting people at the moment :D.

Hmm.. do you have management studio installed at all? Guessing you must be to interact with the db for testing?
Click to expand...
I forgot to add the obvious, it seems to be working. Will start the task tomorrow and update.

I did have management studio initially installed but a re-installed fixed things it seems :)

And terrible excuse :p
 
Oh joy, this is becoming a pain!

I have VS 2008. After Set up the DB and insert a table, I was ready to get cracking on.

A datasource through the error that it's only compatible with SQL 2005!

So I've downloaded that and now I'm having issues with the datasource in identifying other damn tables.

Now I can get this working through some other means but for simplicity and the nature of the task, I'd prefer to get this set up running so I could just fire it across.

TestDB isn't appearing. I was under the impression all DB's under Databases should display? That said, system databases cannot be modified.

3HXcp2W.jpg
 
Oneother thing do you have more than one sql instance installed and are you looking at the right instance?

I have copies of 2005,2008,2012 installed on my dev machine with the 2005 instance being the default SQLEXPRESS and then 2008,2012 being SQL2008 & 12 Respectively.
 
davetherave2 said:
Oneother thing do you have more than one sql instance installed and are you looking at the right instance?

I have copies of 2005,2008,2012 installed on my dev machine with the 2005 instance being the default SQLEXPRESS and then 2008,2012 being SQL2008 & 12 Respectively.
Click to expand...
I think you're on to something here. How do I determine which is the default? I've had a Google and a look around but alas no such luck.
 
The default instance will be the sqlexpress edition.

When you connect to the server in sql studio which does it say you are connecting to eg localhost\XXXXXX

use that server name in the connection string.

so for example if I use my previous example I would use <my machine>\SQLExpress to connect to the 2005 install and then <my machine>\SQL2012 to connect to the sql 2012 instance.
 
you can perform the required action via SqlCMD.exe, the command line tool as



assuming you have a backup we get here with the backup statement

1>BACKUP DATABASE [dbNam] TO DISK = 'C:\bck.bck';

2>GO



you have a file in c:\ and you can restore it as an existing database, overwriting it, or even creating a new one, as we'll do with the following:

1>RESTORE DATABASE [copyOfDb] FROM DISK = N'C:\bck.bck'

2>WITH FILE = 1,

3>MOVE N'Data_Logical_Name' TO N'c:\copyOfDb.Mdf',

4>MOVE N'Log_Logical_Name' TO N'c:\copyOfDb.Ldf';

5>GO



a new database is created named [copyOfDb] and the WITH MOVE syntax will create the related physical files pointing to new files, 'c:\copyOfDb.Mdf' for the data file and 'c:\copyOfDb.Ldf' for the transaction log..



the actual logical names you have to provide can be found executing the statement
RESTORE FILELISTONLY FROM DISK = 'C:\bck.bck';



if you use SQL Server Management Studio Express, the graphical management tool you can download and install for free, you can execute the restore dialog.. it's very simple..
 
Thanks for the posts guys, I've spent 14 hours today and with my lame connection, I've downloaded almost every version possible. I even created multiple VM's and still no bloody luck. And my SSD decided to freeze multiple times today :(

Pretty much worst case scenario for any developer.

Eventually, I got rid of all the versions and started with 2005. That kept throwing up an error through installing, I managed to find a fix through a blog by setting BOOT.INI with 1 core and it worked a treat for the installation.

Now I have:

TITLE: Connect to Server
------------------------------

Cannot connect PC.

------------------------------
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
Click to expand...

Even though all the services are enabled and the correction configuration is set up as per link and link.

Bah, I think I'm going to have to let this opportunity ride. Such a crap reflection :(

Edit: This has by far been my most loathing experience to date :/
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom