?
Solved

How to backup a SQL 2008 Express Database.

Posted on 2011-09-27
18
Medium Priority
?
511 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

752 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