Solved

How to backup a SQL 2008 Express Database.

Posted on 2011-09-27
18
503 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Managing Columnstore Indexes 2 33
sql query 8 50
SQL 2008 R2 syntax 11 32
How to search for strings inside db views 4 29
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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

825 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