Restore database in different location

achauvet

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

petey

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' "

achauvet

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

petey

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.

achauvet

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

petey

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.

achauvet

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

HJoyce

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!

achauvet

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

petey

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.

achauvet

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