[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to backup a SQL 2008 Express Database.

Posted on 2011-09-27
18
Medium Priority
?
514 Views
Last Modified: 2012-05-12
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"?
0
Comment
Question by:victordr
  • 6
  • 6
  • 5
  • +1
18 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 36709605
Hi,

For password use option
 
sqlcmd -P yourpassword

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

/peter
0
 

Author Comment

by:victordr
ID: 36709615
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
 
LVL 22

Accepted Solution

by:
pivar earned 2000 total points
ID: 36709616
That should be in ExpressBackup.bat

sqlcmd -S db2\SQL2008 -U sa -P sa_pwd -u -i E:\SQLExpressBackup\FullBackup.sql
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 16

Expert Comment

by:vdr1620
ID: 36709625
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 36709638
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
 

Author Comment

by:victordr
ID: 36709640
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 36709659
I use http://expressmaint.codeplex.com/ with great success
0
 
LVL 22

Expert Comment

by:pivar
ID: 36709660
No that is handled in FullBackup.sql (Express)
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36709687
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
 

Author Comment

by:victordr
ID: 36709718
So if i wanted to test the script inside of Mangement Studio, just click on Execute?
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36709736
yes
0
 

Author Comment

by:victordr
ID: 36709773
So i have two of these databases that i need to backup. How can i add an addtional database into this SQL script?
0
 
LVL 22

Assisted Solution

by:pivar
pivar earned 2000 total points
ID: 36709803
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
 
LVL 22

Expert Comment

by:pivar
ID: 36709815
You'll need

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

first in the script of course
0
 

Author Closing Comment

by:victordr
ID: 36711409
It worked! many thanks to the both of you.
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36711680
Well, you accepted multiple solutions from the same person... guess i had a share in it
0
 

Author Comment

by:victordr
ID: 36711711
my mistake.. please split off 200 to 36709605


0
 
LVL 16

Expert Comment

by:vdr1620
ID: 36711909
well, thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question