?
Solved

Full and Differential Backup T-SQL

Posted on 2010-08-31
8
Medium Priority
?
427 Views
Last Modified: 2012-05-10
Hello All, I need to back up a few databases for a program called Primavera.  its connected to MS sql express 2005.  I see that in studio manager that you can run a backup but in the express version the backups cant be scheduled.

I saw this command online to back up
which in turn I created this

-- For FULL backup

"BACKUP DATABASE PMDB TO DISK = 'F:\Primavera server backup\Primavera database backup\Full\PMDB.bak'
GO"
pmdb=db name

I created a .bat file so I could schedule it.  in my testing it looks like it failed.  I happened to go into the directory where the script file resides and I saw this sql log.

"Fatal NI connect error 12560, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleXE)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))(CONNECT_DATA=(SID=XE)(CID=(PROGRAM=C:\oraclexe\app\oracle\product\10.2.0\server\bin\sqlplus.exe)(HOST=PRIMAVERA-SRV)(USER=administrator))))

  VERSION INFORMATION:
      TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
      Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production
  Time: 31-AUG-2010 16:18:57
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12560
    TNS-12560: TNS:protocol adapter error
    ns secondary err code: 0
    nt main err code: 530
    TNS-00530: Protocol adapter error
    nt secondary err code: 2
    nt OS err code: 0

"
im wondering if i DID something wrong in the command or if i couldnt connect to the DB for some reason.
 This product  also uses ORACLE 10 g for Db connectivity.

I was curious to see if I have incorrect syntax or is my issue larger.
 
0
Comment
Question by:jrojas1213
[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
  • 4
  • 3
8 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 33571607
A copy of the .BAT file would be nice - sanitized of passwords/logins/addresses as needed, of course.

However, if you look at:

http://www.sqldbatips.com/showarticle.asp?ID=27

you should find all of the information you would need.
0
 
LVL 1

Author Comment

by:jrojas1213
ID: 33571695
The command that I posted is all that is in the bat file
0
 
LVL 8

Expert Comment

by:mustaccio
ID: 33572070
The error log you have shown is from Oracle, not MS SQL Server. You seem to have an Oracle database on the same machine, but I highly doubt it's related to the SQL Server backup.

Is there a backup image in F:\Primavera server backup\Primavera database backup\Full?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 32

Expert Comment

by:Brendt Hess
ID: 33572076
Ah - that probably explains the issue.  Your command should look something like this by preference:

sqlcmd -S.\SQLExpress -i"c:\backup scripts\userfullbackup.sql"

Where -S is used to specify the SQL instance to be targeted, and -i is the path to your backup command.

So, for an example, create a directory on your C: drive called SQLScripts.

Create a file containing the backup command and save it there.  Let's call it bu.sql (for ease of typing).

Create a .BAT file containing the command:
     sqlcmd -S.\SQLExpress -i"c:\SQLScripts\bu.sql"

Save and run the the .bat file  (this is a test)

If the batch ran correctly, make the command a Scheduled Task command and go with it.

Note that the linked site in my previous post will show a much more versatile backup scripting mechanism.
0
 
LVL 1

Author Comment

by:jrojas1213
ID: 33580604
bhess1, thank you very much for your response they were very helpful, the parameters that are executed after the sql command is executed.  are they to be included in the bat file as well ?
0
 
LVL 1

Author Comment

by:jrojas1213
ID: 33580734
also if I selected 'all users' or 'all systems' to backup, will I be able to restore an individual instance using the restore function in sql 2005 express ?
0
 
LVL 1

Author Comment

by:jrojas1213
ID: 33580904
in regards to this comment"

"Where -S is used to specify the SQL instance to be targeted, and -i is the path to your backup command."

does the entire qualifier have to be included for the  instance path another words
i.e. "PRIMAVERA-SRV\SQLEXPRESS\Databases\PMDB"
refer to image as part of example




ScreenHunter-02-Sep.-01-16.12.gif
0
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 2000 total points
ID: 33590456
No - this does not specify the DB, just the SQL Server Instance.  Usually, .\SQLExpress (indicating the SQLExpress instance on this server) is correct.

Since you are choosing to use the more complete backup scripts from the examples in the link:

The ALL_USER option provided by the script will backup all user databases.  As described in the @backupfldr parameter description, "The base folder to write the backups to. Sub folders will be created for each database", meaning that the backups are in one file per DB, so you don't have to restore multiple DBs at once.

0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

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