Solved

Full and Differential Backup T-SQL

Posted on 2010-08-31
8
417 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
  • 4
  • 3
8 Comments
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
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
Comment Utility
The command that I posted is all that is in the bat file
0
 
LVL 8

Expert Comment

by:mustaccio
Comment Utility
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
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:jrojas1213
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now