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

You must specify the instance of your sql server.

 

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

 

Answer 24

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 25

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 26

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

Answer 27

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 28

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 29

 

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

Answer 30

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

Answer 31

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.

Answer 32

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.

 

Answer 33

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.

Answer 34

Can you send sample using c# of tacking database backup with sqlserver2005/08

Answer 35

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.

Answer 36

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

Answer 37

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

Answer 38

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

Answer 39

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

Answer 40

Because this call might wanted to be added to a client Windows  Apllication

Maybe?

---

P Velachoutakos

Answer 41

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