Options
Command line restore where logical name may be unknown
Hexteque
Posts: 7
I ship about 1500 databases daily to a remote server where they are restored every morning.
The source data servers store the physical DB files in a different location than the destination backup server. Therefore when restoring I usually specify the MOVE parameter to show where I want the DB files to be kept.
I have a program that creates a batch file for me. Since customer name = database name I can simply get the list of customers and use that to create the restore batch files.
The issue is that the source data servers are not uniform in the DB's logical name. Some databases have a logical filename that ends in _Data and others do not.
An example:
CustomerDB_Data
vs
CustomerDB
A further example:
"C:\Program Files\Red Gate\Sql Backup\SQLBackupC" -SQL "RESTORE
DATABASE [HealthClub] FROM DISK='D:\SQL Backup\DB\HealthClub_FULL*.sqb' WITH STANDBY = 'D:\SQL Backup\Undo Files\HealthClub_Undo.ldt', MOVE 'HealthClub' TO 'D:\SQLData\MSSQL\Data\HealthClub.mdf', MOVE 'HealthClub_log' TO 'D:\SQLData\MSSQL\Data\HealthClub.ldf'"
vs
"C:\Program Files\Red Gate\Sql Backup\SQLBackupC" -SQL "RESTORE DATABASE [HealthClub] FROM DISK='D:\SQL Backup\DB\HealthClub_FULL*.sqb' WITH STANDBY = 'D:\SQL Backup\Undo Files\HealthClub_Undo.ldt', MOVE 'HealthClub_data' TO 'D:\SQLData\MSSQL\Data\HealthClub.mdf', MOVE 'HealthClub_log' TO 'D:\SQLData\MSSQL\Data\HealthClub.ldf'"
So, is there a way to not have to specify the logical file name?
My current solution is to make two batch files, one with the _Data and one without. The issue is that errors are expected so I can't use the Mailto_OnError option to let myself know when the restore failed.
Any ideas?
The source data servers store the physical DB files in a different location than the destination backup server. Therefore when restoring I usually specify the MOVE parameter to show where I want the DB files to be kept.
I have a program that creates a batch file for me. Since customer name = database name I can simply get the list of customers and use that to create the restore batch files.
The issue is that the source data servers are not uniform in the DB's logical name. Some databases have a logical filename that ends in _Data and others do not.
An example:
CustomerDB_Data
vs
CustomerDB
A further example:
"C:\Program Files\Red Gate\Sql Backup\SQLBackupC" -SQL "RESTORE
DATABASE [HealthClub] FROM DISK='D:\SQL Backup\DB\HealthClub_FULL*.sqb' WITH STANDBY = 'D:\SQL Backup\Undo Files\HealthClub_Undo.ldt', MOVE 'HealthClub' TO 'D:\SQLData\MSSQL\Data\HealthClub.mdf', MOVE 'HealthClub_log' TO 'D:\SQLData\MSSQL\Data\HealthClub.ldf'"
vs
"C:\Program Files\Red Gate\Sql Backup\SQLBackupC" -SQL "RESTORE DATABASE [HealthClub] FROM DISK='D:\SQL Backup\DB\HealthClub_FULL*.sqb' WITH STANDBY = 'D:\SQL Backup\Undo Files\HealthClub_Undo.ldt', MOVE 'HealthClub_data' TO 'D:\SQLData\MSSQL\Data\HealthClub.mdf', MOVE 'HealthClub_log' TO 'D:\SQLData\MSSQL\Data\HealthClub.ldf'"
So, is there a way to not have to specify the logical file name?
My current solution is to make two batch files, one with the _Data and one without. The issue is that errors are expected so I can't use the Mailto_OnError option to let myself know when the restore failed.
Any ideas?
Comments
As far as I know, you absolutely need to MOVE the files if the physical filename can't be used between servers.
You may need to add an extra bit to the restore script: use SQLBackup's RESTORE FILELISTONLY to get information about the backup file's physical and logical file names, then put the logical filename into a variable and use this as the logical filename for the MOVE command in the subsequent restore.
If you need some help writing this, please let us know.
I would be interested in an example of a script that would restore all sqb files from a directory (with the FILELISTONLY option), in my database. I'm only beginning with the sql backup scripting, for the moment.
Actually, i sometime need to restore about 20 base on a new server, and i'm doing this with the sqlbackup GUI witch can only do this one by one
Thanx for the help.
You could use the SQL Backup command line within a batch script to iterate over the files and restore them:
FOR %%f IN (C:\mybackups\*.sqb) do "C:\Program Files\Red Gate\SQL Backup\SQL2005\SQLBackupC.exe" -SQL "RESTORE FILELISTONLY FROM DISK = '%%f'"
Hope this helps
Alan.