Solved

How to backup a SQL 2008 Express Database.

Posted on 2011-09-27
18
509 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 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TSQL recursive CTE challenge... 8 36
Amazon RDS migrate to SQL Server 3 35
Index and Stats Management-Specific tables 8 40
Database Owner 3 21
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

697 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