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

Question

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

 

Thanks!!!

Answer 1

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!

Answer 2

 

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(  ));
        }
    }

Answer 3

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.

 

 

 

Answer 4

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
 

 

Answer 5

Just a thought on this subject.

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

Tailor

Answer 6

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

Answer 7

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.

Answer 8

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

Answer 9

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.

 

Answer 10

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.

Answer 11

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

Answer 12

 

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....

Answer 13

 
>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");

Answer 14

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.

Answer 15

Thank you all..

Ryec, adding the -S Servername\DatabaseName did the trick. 

Answer 16

anyway, my application NEEDS the SQLServerAgent engine to auto-schedule it's own queries. it creates jobs based on user-selectable options from a web-based interface. the use of task-scheduler is simply impossible.

i'm sorry to MS, but we will not migrate all our customers to SQL2005standard while we cannot have an easily replicable testing environment running with the Express edition.

that simple.

Answer 17

I am also tryin gto back up sql express using sqlcmd.

Sqlcmd -S .\SQLEXPRESS - q " BAckup Database XYZ  to disk = N'D:\dbbackup\test11.bak'"

i am getting error

Can not open back up device 'D:\dbbackup\test11.bak'  .OPerating system error. Back up database terminating abnormally.

 

Answer 18

Another solution to building a process to manage your T-SQL inside a service can be found here:

 

http://www.codeproject.com/KB/database/SQLAgent.aspx

 

I'm being driven to use this tool, but have not yet done so...Nothing like a deadline to keep the momentum going.

Answer 19

In case someone comes to this thread through search, and is looking for what Glenn Wilson linked to (which is no longer there), I think he is talking about this:

http://www.codeproject.com/KB/database/SQLAgent.aspx

If not, this does the same thing... a replacement for SQL Agent.

Answer 20

I get post not found when I click that link.  I also tried searching for sql agent and nothing was found.  *shrug*

Answer 21

This sounds great, but it looks like it waits for a user to hit a key to continue... so don't see how it would be usable as a service.

Answer 22

You can also refer Automate Backups in SQL Express article to configure SQLExpress backup!!!

Answer 23

 

Answer 24

You must specify the instance of your sql server.

 

i.e. if you are using SQLEXPRESS type sqlcmd -S .\sqlexpress

 

Answer 25

I chose to use the SQLCMD route to backup 2005 Express databases.

Works fine with most instances, but my Sharepoint databases that have dashes are failing.

Please advise on how to modify code to work with Sharepoint:

....

DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200);
SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] > 0 AND NAME NOT IN ('TEMPDB')
WHILE @IDENT IS NOT NULL
BEGIN
   SELECT @DBNAME = NAME FROM SYS.DATABASES WHERE database_id = @IDENT;
   SELECT @SQL = 'BACKUP DATABASE '+ @DBNAME +' TO DISK = ''D:\SQLBack\'+@DBNAME+'_db_' + @dateString +'.BAK'' WITH INIT';
   EXEC (@SQL)
     SELECT @IDENT=MIN(database_id)
     FROM SYS.DATABASES
     WHERE [database_id] > 0
     AND database_id>@IDENT
     AND NAME NOT IN ('TEMPDB')
END

 

I have tried specifying the Sharepoint DB's in brackets [name] - for example -

AND NAME IN ('[SharedServices_123-456-789-cxvvsefse-193747]') but this does not work either.

 

Msg 102, Level 15, State 1, Server MSOCSP02\OFFICESERVERS, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Server MSOCSP02\OFFICESERVERS, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

 

Please advise.  Thank you!!!!

Answer 26

With more research I got it working by replacing the original line 6 with this line.

 

 SELECT @DBNAME = '['+NAME+']' FROM SYS.DATABASES WHERE database_id = @IDENT

 

Many thanks to Jonathan Kehayias code on " How to Automate Database Backups with SQL Server Express"

Link is here: http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ExpressBackups&referringTitle=ExpressMaintenance

 

Thanks

Answer 27

Why bother with scripts when off-the-shelf freeware like SqlBackupAndFTP would do it better?

Answer 28

Why bother with scripts when off-the-shelf freeware like DBSave would do it better?

 

DBSave is a net framework based software for backup and restore of running MS SQL Server, backups all versions: 7.0 up to 2005 including MSDE and express editions. DBSave supports Completely and different backups and can restore databases on another server. Automatically or with only one mouse click a complete server can be backuped. Backup via FTP, FTPS u. SFTP (SCP) is possible.

Answer 29

Well, for a start DBSave is 54MB when SqlBackupAndFTP is 100 times smaller at 0.5MB. DBSave site has no English description and screenshots are too small to understand. Does it have English interface? If you tell me it does Remote backups (on hosted servers) - I'd try it. But otherwise it is safer to stay away from behemoths that try to do and be everything, but loose focus on strightforward backup process.

Answer 30

 

I this this thread will do great job...  http://developerskb.blogspot.com

Answer 31

Here is an alternative method for backing up the database:

http://sqlservernation.com/blogs/tipweek/archive/2009/02/23/automated-sql-server-express-backups.aspx

Tim Chapman
www.sqlservernation.com

---

Tim Chapman, MCITP