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
Solved

Full and Differential Backup T-SQL

Posted on 2010-08-31
8
420 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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 …
In this article I will describe the Copy Database Wizard 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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

856 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