Full and Differential Backup T-SQL

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

      TNS for 32-bit Windows: Version - Production
      Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version - 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.
Who is Participating?
Brendt HessSenior DBACommented:
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.

Brendt HessSenior DBACommented:
A copy of the .BAT file would be nice - sanitized of passwords/logins/addresses as needed, of course.

However, if you look at:


you should find all of the information you would need.
jrojas1213Author Commented:
The command that I posted is all that is in the bat file
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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?
Brendt HessSenior DBACommented:
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.
jrojas1213Author Commented:
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 ?
jrojas1213Author Commented:
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 ?
jrojas1213Author Commented:
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
refer to image as part of example

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.