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

  • Question

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

     

    Thanks!!!

    Monday, June 19, 2006 3:17 PM

Answers

  • 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!

    Monday, June 19, 2006 5:10 PM
    Moderator
  • Why bother with scripts when off-the-shelf freeware like SqlBackupAndFTP would do it better?
    • Proposed as answer by Mike Shilov Wednesday, April 22, 2009 9:24 AM
    • Marked as answer by Naomi NModerator Monday, July 4, 2011 4:15 PM
    Monday, October 13, 2008 7:29 PM
  •  

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

    Tuesday, June 20, 2006 5:47 AM
  •  

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

    Saturday, October 7, 2006 7:55 AM
  • 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
    Tuesday, March 24, 2009 1:50 AM

All replies

  • 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!

    Monday, June 19, 2006 5:10 PM
    Moderator
  •  

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

    Tuesday, June 20, 2006 5:47 AM
  • 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.

     

     

     

    Thursday, June 29, 2006 6:37 PM
  • 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
     

     

    Monday, July 3, 2006 4:42 PM
  • Just a thought on this subject.

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

    Tailor

    Monday, July 3, 2006 11:26 PM
  • 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

    Saturday, July 8, 2006 4:48 AM
  • 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.
    Tuesday, September 19, 2006 10:00 AM
  • 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

    Tuesday, September 19, 2006 8:55 PM
    Moderator
  • 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.

     

    Thursday, October 5, 2006 5:18 AM
  • 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.
    Thursday, October 5, 2006 5:57 AM
  • 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

    Thursday, October 5, 2006 3:50 PM
    Moderator
  •  

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

    Saturday, October 7, 2006 7:55 AM
  •  
    >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");

    Tuesday, October 10, 2006 9:31 AM
  • 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.

    • Proposed as answer by Wilson.Shen Monday, August 23, 2010 8:52 AM
    Sunday, October 15, 2006 7:07 PM
  • Thank you all..

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

    • Proposed as answer by nam99 Friday, January 4, 2013 1:50 PM
    Monday, October 16, 2006 4:22 AM
  • 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.

    Friday, December 22, 2006 4:04 PM
  • 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.

     

    Thursday, January 11, 2007 7:45 PM
  • 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.

    Friday, January 4, 2008 4:31 PM
  • 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.


    Wednesday, April 2, 2008 9:54 PM
  • I get post not found when I click that link.  I also tried searching for sql agent and nothing was found.  *shrug*

    Thursday, May 1, 2008 12:39 AM
  • 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.

    Thursday, May 1, 2008 12:42 AM
  • You can also refer Automate Backups in SQL Express article to configure SQLExpress backup!!!
    Thursday, May 1, 2008 10:44 AM
    Moderator
  • You must specify the instance of your sql server.

     

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

     

    Monday, August 4, 2008 9:51 AM
  • 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!!!!

    Thursday, August 14, 2008 4:32 PM
  • 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

    Thursday, August 14, 2008 5:41 PM
  • Why bother with scripts when off-the-shelf freeware like SqlBackupAndFTP would do it better?
    • Proposed as answer by Mike Shilov Wednesday, April 22, 2009 9:24 AM
    • Marked as answer by Naomi NModerator Monday, July 4, 2011 4:15 PM
    Monday, October 13, 2008 7:29 PM
  • 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.

    Friday, October 24, 2008 5:18 PM
  • 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.
    Friday, October 24, 2008 7:03 PM
  •  

    I this this thread will do great job...  http://developerskb.blogspot.com
    Tuesday, December 9, 2008 5:51 AM
  • 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
    Tuesday, March 24, 2009 1:50 AM
  • I'm agreed with it. SQl Backup and FTP is a cool tool to backup your databases. Very lightweight and easy to use.  Just try it.
    Wednesday, May 5, 2010 1:29 PM
  • you can use a tool like this: http://www.sql-server-backup.de/en/ 

    This is a .Net Framework based solution to backup and restore MS SQL Server in running mode.
    It's free

    MS SQL Server backups with one click or via MS Windows based Schedule jobs.
    DBSave is very easy to set up.
    Backups Local, to Network or via FTP.
    Remote Backup via VDI.
    Supports up to 4 SQL servers instances on different servers.
    Compression of the backups regardless of the SQL Server version.
    Encryption of the backups via AES regardless of the SQL Server version.
    DBSave supports german and english language.

     

    Tuesday, June 15, 2010 5:20 PM
  • you can use a tool like this: http://www.sql-server-backup.de/en/ 

    This is a .Net Framework based solution to backup and restore MS SQL Server in running mode.
    It's free

    MS SQL Server backups with one click or via MS Windows based schedule jobs.
    DBSave is very easy to set up.
    Backups Local, to Network or via FTP.
    Remote Backup via VDI.
    Supports up to 4 SQL servers instances on different servers.
    Compression of the backups regardless of the SQL Server version.
    Encryption of the backups via AES regardless of the SQL Server version.
    DBSave supports german and english language.

    Tuesday, June 15, 2010 5:23 PM
  • Can you send sample using c# of tacking database backup with sqlserver2005/08
    Wednesday, October 27, 2010 11:34 AM
  • I totally agree with RSudentas. We have a customer with no IT staff and SQL Backup and FTP perfectly solved the daily backup problems, plus the free version lets you to schedule up to 2 database backups daily.


    Monday, July 4, 2011 2:16 PM
  • I found this a great help. It also deals with db backup retention, in that it doesn't keep any more than x amount of copies of the backup.

    http://www.sqldbatips.com/showarticle.asp?ID=27

    • Proposed as answer by Magefyre-OH Monday, February 25, 2013 7:01 PM
    Tuesday, September 6, 2011 1:08 PM
  • Hi Do you perhaps have that T-SQL scripr lying anywhere ? i'm looking to back up an sql express across the network, it also needs to be automated to run at specific times
    Tuesday, November 1, 2011 12:57 PM
  • Hi Do you perhaps have that T-SQL scripr lying anywhere ? i'm looking to back up an sql express across the network, it also needs to be automated to run at specific times
    the t-sql script that i used to do the backups is at http://www.sqldbatips.com/showarticle.asp?ID=27
    Tuesday, November 1, 2011 1:12 PM
  • The file path must exist in the machine where the instance runs

    Sqlcmd -S .\SQLEXPRESS -E -Q "Backup database XYZ to disk=N'C:\mybackup\myback.bak' with init

    with init is optional ,makes no append ,replace file backup .Might usefull for automating Backup.

    A backup set can take 64( or 128 not remember) backups then failed and return error.

    The N in frond is also optional if you use latin English for filepath (N represent unicode for non English Characters)

    -E connect with trust connection to -S server local sqlexpress instance

    -Q execute and exist.

    Hope help somehow.


    P Velachoutakos
    Friday, December 30, 2011 5:50 PM
  • Because this call might wanted to be added to a client Windows  Apllication

    Maybe?


    P Velachoutakos
    Friday, December 30, 2011 5:52 PM
  • This works fine as long as you have only 2 DBs to back up. What about the system DBs? I know they don't change often on SQLExpress, but you still need a backup,.
    Tuesday, July 24, 2012 2:46 PM