How do you backup a database using sql server 2005 express???

I know there isn't a backup feature but I was wondering if there was a way to back up a database

Thanks!!!

Answer this question

How do you backup a database using sql server 2005 express???

Stefan Grossmann

hi,

download SQL Server Management Studio Express, http://www.microsoft.com/downloads/details.aspx familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en, connect to the desired instance, navigate to the database you are interested in.. right click, tasks, backup... the backup dialog will appear.. specify your options/settings and click [ok] ... that's all..

for your convenience, before or after you are finished specifying your database backup, in the upper/middle of the dialog there's a "script" toolbar buttons set... you can save your settings to a cmd/bat file and just click on it to execute it, and even schedule it via AT/SCHTSKS native OS scheduler...

HTH
Helschaee

I do not understand why Microsoft went through the trouble of removing the Sql Agent scheduler from SQL 2005 Express. This is wrong for any product path/roadmap. There are many small applications, both in-house IT as well as distributed, that depend on the reliable SQL Server Agent Job scheduler.

I stopped using the Windows Task Scheduler years ago because you cannot count on it. It is not reliable. While it has improved since the NT4 and early Win2000 days, it is only marginally better due to a UI instead of the AT command line.

We need the service, SQLSERVERAGENT, to be included with SQL 2005 Express in order to migrate applications to 2005.

If you have a system that is configured for Client - Server - Notebook/offline and you utilize SQL2005 on the servers, you need SQL2005 Express on the Client and notebooks for replication. For the same reasons we distribute MSDE now, we would distribute SQL2005 Express in the future for the Notebook/offline engine. Out installations, updates, and backups, depend on the server and notebook being the same, (i.e. the same service names, the same scheduler, etc.). If the notbooks cannot have Express with the SQLSERVERAGENT service, then that complicates everything and increases the cost to everyone involved.

Put SQLSERVERAGENT in SQL 2005 Express.
Mesia

why not just write a windows service using VS2005 templates and run the service on the server The service could execute a SP that backs up the database. That sounds like the most reliable way (and very simple) to emulate the sql server agent backup functionality.

-Andy
aaronmarissa
Extra Info:
1. I have set up a scheduled task in Windows so that it runs. It refers to the sa user and password. I get the same error
2. I have looked at the SQL Browser Service and it is active.
3. I have looked at the Firewall settings and these are not a problem as the firewall is not in use on this server.
4. I have looked at the Surface Area Config for my database and it says that Local and Remote Connections can be used using TCPIP.
Yared Kassa
Hi,

You're not quite right here, SQL Express fully supports backing up a database. What it does not have is SQL Agent, which allows you to schedule backups and the Mataintenance Plan wizard which allows you to create a plan to perform a number of tasks, including backup.

You can backup your database in two ways:
1. Use Management Studio Express (available separately or as part of Express advanced from this download page) which has the Backup option on the right click menu for each database under Tasks.
2. Use T-SQL to manually write your backup script. You can learn about the T-SQL backup command in this BOL topic.
If you want to schedule your backups, you would write a T-SQL script and then use Windows Task Schedule to call SQLCmd to run the script on what every schedule you're interested in.

Regards,

Mike Wachal
SQL Express team

----
Mark the best posts as Answers!
fabien7474

One option that you could use is the SMO system, Here is a small c# application that backs up a database. You will have to compile it and change the values to reflect your server. Remember that the Visual Studio C# Express system, is free to use.

using System;
using System.Data;
using System.Collections;

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

class Program
{
static void Main(string[] args)
{
BackupDeviceItem bdi =
new BackupDeviceItem("AdventureWorks.bak", DeviceType.File);

Backup bu = new Backup( );
bu.Database = "AdventureWorks";
bu.Devices.Add(bdi);
bu.Initialize = true;

// add percent complete and complete event handlers
bu.PercentComplete +=
new PercentCompleteEventHandler(Backup_PercentComplete);
bu.Complete +=new ServerMessageEventHandler(Backup_Complete);

Server server = new Server("localhost");
bu.SqlBackup(server);

Console.WriteLine(Environment.NewLine + "Press any key to continue.");
Console.ReadKey( );
}

protected static void Backup_PercentComplete(
object sender, PercentCompleteEventArgs e)
{
Console.WriteLine(e.Percent + "% processed.");
}

protected static void Backup_Complete(object sender, ServerMessageEventArgs e)
{
Console.WriteLine(Environment.NewLine + e.ToString( ));
}
}
Kasinathan

For those who are interested have a look at the following post, http://www.virtualrealm.com.au/blogs/mykre/archive/2006/09/01/SQL-Agent-for-SQL-Server-Express.aspx

I have found a codeproject system that simulates the Agent....
Tobias Rein
Been searching the net for about an hour now, theres billions of tutorials, but they go into too much detail, I just want to do a single backup of one database, once a day. Its so simple with 2000. But it seems SQL 2005 is a cut-down version of 2000.
I don't really want to write a script and then rely on Sheduler, I want to right click and be walked through a nice wizard like with 2000.
Simpzon

hi,

verify via SQL Server Configuration Manager that the desired/required network protocoll is actually enabled..

you say SQL Browser, thus you have a named instance installed, probably with dynamic port assignement..

verify your firewall has been set with an exception on port UDP 1434 for the SQL Browser too, as long as the required exception for the SQL Server engine service (port or service)

at http://support.microsoft.com/kb/841251/en-us you can find some related info about Windows Firewall related settings, but the "idea" is valid for all firewalls as well..

regards
tanishq1510
Just a thought on this subject.

Why not use Scheduled Tasks and a T-Sql.bat file to automate the backup.

Tailor
jack7701
Clifton is right. I've been a Microsoft certified partner for several years and developing in windows for 9 years. This is another example of Microsoft disregarding the needs of it's smaller ISV developers and indiscriminately "changing the rules" when they feels like it.

You don't expect someone to take away features when they release the next version of a product. You expect things to be added not taken away. Now we have to invest time, energy, and MONEY trying to figure out how to create a reliable automated backup facility for our small customers using SQL Express. The Windows scheduler is a joke. It’s not reliable and not easily managed by the end user. We had a tight and reliable backup and maintenance tool integrated directly into our product using DMO and the SQL Agent. Now it’s all useless code.

I'm sure MS has some half justifiable reason for pulling the Agent, but, I'm sure it wasn't the only path they could have taken. It was simply the easiest path for them. If it hurts us, oh well, there's more developers out there were we came from.

Mike Mazza MCP
Kressa Software Corp
dungcoi

>One option that you could use is the SMO system, Here is a small c# application that backs up a database. You will have to compile it and change the values to reflect your server.

Thanks for the sample!

Just some additional info to get it to work using Visual Studio 2005 and SQL Server Express (sorry if being redundant )

Add references to (.Net tab):
- Microsoft.SqlServer.ConnectionInfo
- Microsoft.SqlServer.Smo

Change "Adventureworks" (both) to the name of the database you want to backup, and change "localhost" to the name of your SQL instance.

E.g.

Server server = new Server("<my machine name>\\SQLEXPRESS");
SunLiWei
I was running into the same problem. I found that all I had to do was add an additional command line argument specifying the server name. In my case, the server runs on the same computer as the script, so I got it working with the following command:

sqlcmd -S LOCALHOST\SQLEXPRESS -i backup.sql -o output.txt

The -S needs to be in upper case.

Hope that helps.

Ryec.
Peeyush Agarwal
for your convenience, before or after you are finished specifying your database backup, in the upper/middle of the dialog there's a "script" toolbar buttons set... you can save your settings to a cmd/bat file and just click on it to execute it, and even schedule it via AT/SCHTSKS native OS scheduler...

I created an .sql script (as described above) and saved it as backup.sql. I am trying to run it from a batch file called backup.bat using sqlcmd. The batch file contains the command
sqlcmd -i c:\directory\backup.sql -o c:\directory\output.txt

It fails, saying it cannot connect remotely. I did a bit of digging to find where I can set 'allow remote connections' and it is under some setting called SQL Surface Area Configuration or something like that. Looking in there, it says SQL EXpress 2005 can only connect locally, not remotely!

The script runs fine. The sqlcmd command fails.

So does anyone have any more information that may help Thanks.
How do you backup a database using sql server 2005 express???

Answer this question