Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Microsoft SQL Server 2000 Desktop Engine and backup

Answered Microsoft SQL Server 2000 Desktop Engine and backup

  • Tuesday, May 08, 2012 7:46 AM
     
     
    Sign In to Vote
    0

    I need to schedule a backup from old server that has MSDE2000 (I think it's MSDE 2000).

    I tried this from the command line:

    C:\>osql -E -S SERVER\INSTANCE

    1> osql -E -Q "BACKUP DATABASE MYDB TO DISK = 'E:\Backup\DB\db.bak' WITH INIT"
    2> go
    Msg 170, Level 15, State 1, Server SERVER\INSTANCE, Line 1
    Line 1: Incorrect syntax near 'E'.

    I need a script that runs as a scheduled job but I'm very unfamiliar with SQL stuff :(

     

All Replies

  • Tuesday, May 08, 2012 7:52 AM
    Moderator
     
     
    Sign In to Vote
    0

    on 1> you need to write backup command directly.

    If you want to schedule it then a batch file should contact below

    osql -E -Q "BACKUP DATABASE MYDB TO DISK = 'E:\Backup\DB\db.bak' WITH INIT"

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

     
  • Tuesday, May 08, 2012 9:01 AM
     
     
    Sign In to Vote
    0

    Could you be more specific?

    "on 1> you need to write backup command directly."

    If i write this to cmd file "osql -E -Q "BACKUP DATABASE MYDB TO DISK = 'E:\Backup\DB\db.bak' WITH INIT"" how I can make it to choose the right database?

     
  • Tuesday, May 08, 2012 9:03 AM
    Moderator
     
     
    Sign In to Vote
    0

    Could you be more specific?

    "on 1> you need to write backup command directly."

    If i write this to cmd file "osql -E -Q "BACKUP DATABASE MYDB TO DISK = 'E:\Backup\DB\db.bak' WITH INIT"" how I can make it to choose the right database?


    While taking the backup you need to specify the database name (backup database <Name Here> to disk = 'path here')

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

     
  • Tuesday, May 08, 2012 9:52 AM
     
     
    Sign In to Vote
    0

    Yes i got it know :)

    Now I have more problems. How do I know what is the DB name? It seems that I cannot use the instance name?

     
  • Tuesday, May 08, 2012 10:26 AM
    Moderator
     
     
    Sign In to Vote
    0

    Do you want to backup all databases?
    You need to make script smart enough to take backup of all databases?

    Check http://www.howtogeek.com/50299/batch-script-to-backup-all-your-sql-server-databases/
    and
    http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

     
  • Thursday, May 10, 2012 11:11 AM
     
     
    Sign In to Vote
    0

    It's enough to backup only one database.

    I just cannot figure out why I cannot get this cmd-script to work.

    osql -E -S SERVER\INSTANCE BACKUP DATABASE DBNAME TO DISK = 'E:\Backup\DBNAME.bak' WITH INIT

    What are the right switches?

     
  • Thursday, May 10, 2012 11:16 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    It's enough to backup only one database.

    I just cannot figure out why I cannot get this cmd-script to work.

    osql -E -S SERVER\INSTANCE BACKUP DATABASE DBNAME TO DISK = 'E:\Backup\DBNAME.bak' WITH INIT

    What are the right switches?

    you need to add -Q before the command.
    osql -E -Q "BACKUP DATABASE MYDB TO DISK = 'E:\Backup\DB\db.bak' WITH INIT"
    use osql /? to learn more

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    • Marked As Answer by jqx12 Thursday, May 10, 2012 11:25 AM
    •  
     
  • Thursday, May 10, 2012 11:25 AM
     
     
    Sign In to Vote
    0

    I tried that but it does nothing.

    This command works:

    osql -E -S SERVER\INSTANCE -Q "BACKUP DATABASE MYDB TO DISK = 'E:\Backup\DB\db.bak' WITH INIT"

    Thank you Balmukund for your help!