What are the challenges you face when working across database platforms? Take the survey
Options

Restore database in different location

achauvetachauvet Posts: 9
edited July 17, 2007 12:09PM in SQL Backup Previous Versions
Hi support

I have SQLBackup 4.6.0.815 and SQL Server 2000
i backup my bases on a server and I want to restore on another server

but I must change the location of the mdf and ldf. How is this possible to make via script?

my script is restore.bat

"C:\Program Files\Red Gate\SQL Backup\(LOCAL)\SqlBackupC.exe" -SQL "RESTORE DATABASE [NewDatabase] FROM DISK = 'E:\data\database1\FULL_(local)_database1_20070710_031501.sqb' WITH RECOVERY, MOVE [NewDatabase_Data] TO 'I:\Data\NewDatabase_Data.mdf', MOVE [NewDatabase_Data] TO 'i:\journal\NewDatabase_Log.ldf' ,REPLACE" -E

but i have
Server: Msg 3013
Restore Database is terminating anbormally
Server: 3234
logical file 'NewDatabase_Data' is not part of database 'NewDatabase'. Use Restore FileListonly to list the logical file names
SQL backup exit code : 1100
Last sqk error code : 3234

Thanks
Anthony

Comments

  • Options
    peteypetey Posts: 2,358 New member
    The names you use in the first part of the MOVE parameter is the logical name of the original database, not the logical name of the new database. E.g.

    "C:\Program Files\Red Gate\SQL Backup\(LOCAL)\SqlBackupC.exe" -SQL "RESTORE DATABASE [NewDatabase] FROM DISK = 'E:\data\database1\FULL_(local)_database1_20070710_031501.sqb' WITH RECOVERY, MOVE b]OldDatabase_Data[/b TO 'I:\Data\NewDatabase_Data.mdf', MOVE b]OldDatabase_Log[/b TO 'i:\journal\NewDatabase_Log.ldf' ,REPLACE" -E

    You can find out the logical names of the original database by using RESTORE FILELISTONLY e.g.

    "C:\Program Files\Red Gate\SQL Backup\(LOCAL)\SqlBackupC.exe" -SQL "RESTORE FILELISTONLY FROM DISK = 'E:\data\database1\FULL_(local)_database1_20070710_031501.sqb' "
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    thanks all run normally but i have another problem

    when i run RESTORE FILELISTONLY
    i have in command dos :
    "logical name : database_données"

    but with
    "C:\Program Files\Red Gate\SQL Backup\(LOCAL)\SqlBackupC.exe" -SQL "RESTORE DATABASE [database] FROM DISK = 'E:\data\database\FULL_(local)_database_20070716_020003.sqb' WITH RECOVERY, MOVE 'database_données' TO 'H:\Data\database_donnees.mdf',REPLACE" -E

    error message :
    logical file 'database_donnùes' is not part of database 'database'
    exit code : 1100

    I have a problem with the accents how then I to circumvent this concern?

    Thanks

    Anthony
    Anthony
  • Options
    peteypetey Posts: 2,358 New member
    I think that's an error with the accented character (é), that got lost in translation. What is the reason that you are using the command line interface instead of the extended stored procedure? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    petey wrote:
    I think that's an error with the accented character (é), that got lost in translation. What is the reason that you are using the command line interface instead of the extended stored procedure? Thanks.

    I try to create a procedure to restore in the event of cut. Indeed the replication not being more possible it will remain the copy then the restoration of the base. We want to automate this procedure that I would qualify exceptional.
    And it is faster because all is prepared in advance

    what's the extended stored procedure ?
    can you explain ?

    Thanks
    Anthony
  • Options
    peteypetey Posts: 2,358 New member
    You can run the backup using the SQL Backup extended stored procedure e.g. using Query Analyzer or Management Studio. In this way, you can pass the parameter as unicode text, to preserve all special characters e.g.

    EXEC master..sqlbackup N'-sql "RESTORE DATABASE [database] ..." '

    If you need to run via the command line, you can use osql, but instead of specifying the command directly, use the -i parameter to specify an input file saved in Unicode format, containing the above command.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    petey wrote:
    You can run the backup using the SQL Backup extended stored procedure e.g. using Query Analyzer or Management Studio. In this way, you can pass the parameter as unicode text, to preserve all special characters e.g.

    EXEC master..sqlbackup N'-sql "RESTORE DATABASE [database] ..." '

    If you need to run via the command line, you can use osql, but instead of specifying the command directly, use the -i parameter to specify an input file saved in Unicode format, containing the above command.

    ok thanks
    i am to oblige to use the commande line
    can you explain how I can use osql and -i I do not have to find reference in the assistance
    Anthony
  • Options
    The toolkit parameters are documented in the help file in the 'SQL Backup Toolkit' section.
    The help file is included in the installation of SQL Backup, but it's also available online at http://help.red-gate.com/help/SQLBackup5/0/en/SQLBackup.htm

    There is a section under 'SQL Backup Toolkit' called 'Toolkit Parameters'.

    Hope this helps!
    Helen Joyce
    SQL Backup Project Manager
    Red Gate Software
  • Options
    HJoyce wrote:
    The toolkit parameters are documented in the help file in the 'SQL Backup Toolkit' section.
    The help file is included in the installation of SQL Backup, but it's also available online at http://help.red-gate.com/help/SQLBackup5/0/en/SQLBackup.htm

    There is a section under 'SQL Backup Toolkit' called 'Toolkit Parameters'.

    Hope this helps!

    ok thanks but i have the same message avec "é"
    i use osql with a file in command DOS

    restore.bat :
    osql -E -S (local) -i C:\Exploitation\scripts\Database_directory\restore.sql /o RESULTAT.TXT

    restore.sql:
    EXEC master..sqlbackup N'-SQL "RESTORE DATABASE [database] FROM DISK = ''E:\data\database\FULL_(local)_database_20070716_020003.sqb'' WITH RECOVERY,MOVE ''database_Données'' TO ''H:\Data\database_Donnees.mdf'',MOVE ''FG_INDEX'' TO ''H:\Index\database_Index.NDF'',MOVE ''FG_INDEX2'' TO ''H:\index\database_Index2.NDF'',MOVE ''database_Journal'' TO ''H:\Journal\database_Journal.ldf'',REPLACE"'
    GO

    and RESULTAT.TXT :

    BEGIN

    1> 2> SQL Backup v4.6.0.815






    Restoring BoTampon (database) from:



    E:\data\BoTampon\FULL_(local)_BoTampon_20070716_020003.sqb







    SQL Server error







    Server: Msg 3013



    RESTORE DATABASE is terminating abnormally.



    Server: Msg 3234



    Logical file 'boTampon_Données' is not part of database 'BoTampon'. Use RESTOR
    E FILELISTONLY to list the logical file names.






    SQL Backup exit code: 1100



    SQL error code: 3234




    name
    value







    exitcode
    1100



    sqlerrorcode
    3234



    filename01
    E:\data\BoTampon\FULL_(local)_BoTampon_20070716_020003.sqb




    1>

    END

    i am completely lost
    thank you for your assistance
    Anthony
  • Options
    peteypetey Posts: 2,358 New member
    I suggest doing this:

    - first, run the restore command in Query Analyzer, to make sure that everything works

    - if it is fine, then save the script. Ensure that you save in the Unicode file format and not the ANSI format.

    - test the script using osql. It should run without errors if it ran successfully via Query Analyzer.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    petey wrote:
    I suggest doing this:

    - first, run the restore command in Query Analyzer, to make sure that everything works

    - if it is fine, then save the script. Ensure that you save in the Unicode file format and not the ANSI format.

    - test the script using osql. It should run without errors if it ran successfully via Query Analyzer.

    all is ok
    Thank you very much of your assistance
    Anthony
Sign In or Register to comment.