Restore database in different location

Comments

• petey Redgate › Posts: 2,358 New member

  July 10, 2007 7:11AM

  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

  0 · Share on Twitter
• achauvet Posts: 9

  July 16, 2007 9:03AM

  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

  0 · Share on Twitter
• petey Redgate › Posts: 2,358 New member

  July 16, 2007 11:11PM

  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

  0 · Share on Twitter
• achauvet Posts: 9

  July 17, 2007 3:40AM

  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

  0 · Share on Twitter
• petey Redgate › Posts: 2,358 New member

  July 17, 2007 4:06AM

  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

  0 · Share on Twitter
• achauvet Posts: 9

  July 17, 2007 5:53AM

  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

  0 · Share on Twitter
• HJoyce Posts: 186

  July 17, 2007 5:59AM

  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

  0 · Share on Twitter
• achauvet Posts: 9

  July 17, 2007 9:00AM

  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

  0 · Share on Twitter
• petey Redgate › Posts: 2,358 New member

  July 17, 2007 11:38AM

  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

  0 · Share on Twitter
• achauvet Posts: 9

  July 17, 2007 12:09PM

  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

  0 · Share on Twitter