How to backup a SQL 2008 Express Database.

I am looking to setup an automated back of two SQL 2008 Express databases i have. I ran across the following article below and had some questions.

http://www.sqlservernation.com/home/automated-sql-express-backups.html

For the command line code that goes into the bat file how do i add the sa username password in there?

What is the path for "sqlcmd"?
victordrAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
pivarConnect With a Mentor Commented:
That should be in ExpressBackup.bat

sqlcmd -S db2\SQL2008 -U sa -P sa_pwd -u -i E:\SQLExpressBackup\FullBackup.sql
0
 
pivarCommented:
Hi,

For password use option
 
sqlcmd -P yourpassword

sqlcmd canbe found in C:\Program Files\Microsoft SQL Server\100\Tools\Binn

/peter
0
 
victordrAuthor Commented:
sqlcmd -S db2\SQL2008 -u -i E:\SQLExpressBackup\FullBackup.sql

So in this line can it go anywhere? Also is -u for username?

 Do i need to refrence the path in the bat file? He does not in his code?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
vdr1620Commented:
You can use

sqlcmd -SServerName -Usa -PmyPasword -i "Path"

Path to SQlcmd

C:\Program Files\Microsoft SQL Server\xxx\Tools\Binn


xxx - SQL server 2005 / 2008 (depending on the version)
0
 
vdr1620Commented:
No you dont need to reference the path in the batch.. if you get an error set the PATH environment variable for the location of the SQLCMD.EXE

http://dbsourcetools.codeplex.com/discussions/260505
0
 
victordrAuthor Commented:
So i am connected in mangement studio to \\sername\instance.

do i need to put the name of the database also in this line?
0
 
Éric MoreauSenior .Net ConsultantCommented:
I use http://expressmaint.codeplex.com/ with great success
0
 
pivarCommented:
No that is handled in FullBackup.sql (Express)
0
 
vdr1620Commented:
the script that you use in the cmd line .. just needs name, access and path to the script file.. everything else should be handled in the SQL script
0
 
victordrAuthor Commented:
So if i wanted to test the script inside of Mangement Studio, just click on Execute?
0
 
vdr1620Commented:
yes
0
 
victordrAuthor Commented:
So i have two of these databases that i need to backup. How can i add an addtional database into this SQL script?
0
 
pivarConnect With a Mentor Commented:
Add (db names Express1 and Express2)

SET @Path = '\\db1\E$\SQLServer\2008\Backups\'
SET @FileName = 'Express1_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') + '.bak'
SET @FullPath = @Path + @FileName

BACKUP DATABASE Express1
TO DISK = @FullPath
WITH INIT

SET @FileName = 'Express2_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') + '.bak'
SET @FullPath = @Path + @FileName

BACKUP DATABASE Express2
TO DISK = @FullPath
WITH INIT

Open in new window

0
 
pivarCommented:
You'll need

DECLARE @Path NVARCHAR(1000), @FileName NVARCHAR(255), @FullPath NVARCHAR(1255)

first in the script of course
0
 
victordrAuthor Commented:
It worked! many thanks to the both of you.
0
 
vdr1620Commented:
Well, you accepted multiple solutions from the same person... guess i had a share in it
0
 
victordrAuthor Commented:
my mistake.. please split off 200 to 36709605


0
 
vdr1620Commented:
well, thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.