Solved

How to backup a SQL 2008 Express Database.

Posted on 2011-09-27
18
497 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 500 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
 
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 69

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now