Solved

Full and Differential Backup T-SQL

Posted on 2010-08-31
8
423 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:bhess1
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
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.

 
LVL 32

Expert Comment

by:bhess1
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:
bhess1 earned 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

707 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