Comments
Sort by recent activity
Fixed syntax errors and took seconds off default <AUT0> name, so we've got this, which works: DECLARE @cmd NVARCHAR(1024)
DECLARE @exitcode INT
DECLARE @errorcode INT
DECLARE @dt CHAR(8)
SET @dt = CONVERT(CHAR(8),GETDATE(),112)
SET @cmd = '''N -SQL "RESTORE DATABASE [CaliforniaSE]
FROM DISK = ''\\principal\backups\database\FULL_(local)_California_' + @dt + '_0300.sqb'' WITH
MOVE ''California'' TO ''F:\FileGroups\DataSE\CaliforniaSE.mdf'',
MOVE ''California _Data'' TO ''F:\FileGroups\DataSE\CaliforniaSE_Data.ndf'',
MOVE ''California_Indexes'' TO ''G:\FileGroups\IndexesSE\CaliforniaSE_Indexes.ndf'',
MOVE ''California_Log'' TO ''H:\FileGroups\LogsSE\CaliforniaSE_Log.ldf''" WITH RECOVERY, REPLACE'
PRINT @cmd
EXEC master..sqlbackup @cmd, @exitcode OUTPUT, @errorcode OUTPUT
/ comments
Fixed syntax errors and took seconds off default <AUT0> name, so we've got this, which works:DECLARE @cmd NVARCHAR(1024)
DECLARE @exitcode INT
DECLARE @errorcode INT
DECLARE @dt CHAR...
Peter,
Thanks for the help. Just to complete the loop, since I've run the command once and created the target db, I need to replace
WITH RECOVERY with WITH RECOVERY, REPLACE
yes? / comments
Peter,
Thanks for the help. Just to complete the loop, since I've run the command once and created the target db, I need to replace
WITH RECOVERY with WITH RECOVERY, REPLACE
yes?
RE the MOVE clauses, I'm not actually trying to change any file paths. Both servers are identical - so I'm just tying to restore the database backed up from server A onto server B with all files residing in the same drives/folders.
Based on your reply, I tried this (actual DB name and file names):
EXEC master..sqlbackup '-sql "RESTORE DATABASE California FROM DISK = [h:\backup\database\California.sqb] WITH
MOVE [California.mdf] TO [F:\FileGroups\Data],
MOVE [California_Data.ndf] TO [F:\FileGroups\Data],
MOVE [California_Indexes.ndf] TO [G:\FileGroups\Indexes],
MOVE [California_Log.ldf] TO [H:\FileGroups\Logs]"'
And get this error:
SQL error 3013: SQL error 3013: RESTORE DATABASE is terminating abnormally.
SQL error 3234: SQL error 3234: Logical file 'California.mdf' is not part of database 'California'. Use RESTORE FILELISTONLY to list the logical file names.
If I remove the file name extensions, like this:
EXEC master..sqlbackup '-sql "RESTORE DATABASE California FROM DISK = [h:\backup\database\California.sqb] WITH
MOVE [California] TO [F:\FileGroups\Data],
MOVE [California_Data] TO [F:\FileGroups\Data],
MOVE [California_Indexes] TO [G:\FileGroups\Indexes],
MOVE [California_Log] TO [H:\FileGroups\Logs]"'
I get the same error:
SQL error 3234: SQL error 3234: Logical file 'California_Data' is not part of database 'California'. Use RESTORE FILELISTONLY to list the logical file names.
Clearly, I'm missing something here... / comments
RE the MOVE clauses, I'm not actually trying to change any file paths. Both servers are identical - so I'm just tying to restore the database backed up from server A onto server B with all files re...
Ok, I did find an ealier thread - thought I had a workaround before - which had me leave file names in place and then copy/alter the script from the script tab. This, below, works:
EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE [CaliforniaSE]
FROM DISK = ''h:\backup\database\california.sqb'' WITH RECOVERY,
MOVE ''California'' TO ''F:\FileGroups\Data\CaliforniaSE.mdf'',
MOVE ''California _Data'' TO ''F:\FileGroups\Data\California_DataSE.ndf'',
MOVE ''California_Indexes'' TO ''G:\FileGroups\Indexes\California_IndexesSE.ndf'',
MOVE ''California_Log'' TO ''H:\FileGroups\Logs\California_LogSE.ldf''"'
6:33 for a 20GB database.
Your second option below, works as well.
EXEC master..sqlbackup '-sql "RESTORE DATABASE California FROM DISK = [h:\backup\database\California.sqb]" '
All of which begs the question as to why you don't fix the product to do this itself. Thanks for your help. Late here. Nite. / comments
Ok, I did find an ealier thread - thought I had a workaround before - which had me leave file names in place and then copy/alter the script from the script tab. This, below, works:
EXECUTE master.....
Jason,
As is the norm in a mirrored configuration, I am simply trying to restore a replica of the principal db on the mirror server. In books on-line this is supposed to be accomplished via
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\AdventureWorks.bak'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks
FROM DISK = 'C:\ AdventureWorks.bak'
WITH FILE=1, NORECOVERY
GO
BOL shows the MOVE option, but implies I only need to do this if the drive letter or path differs, which is not the case here. In your Restore Wizard, you give the user the option to change the restore paths.
I presume I should just do a backup with the Management Studio and use T-SQL to restore?
Regarding your feature set, I don't see any difficulty in restoring to original locations while allowing the user to use your existing dialog to select alternates. You can easily enough test for the existence of the target default paths and guide the user if they're not found. / comments
Jason,
As is the norm in a mirrored configuration, I am simply trying to restore a replica of the principal db on the mirror server. In books on-line this is supposed to be accomplished via
RESTORE...
Ok, your workaround worked. Thanks.
The bottom line in any app is that it should behave as expected. It's fine to default to the new server default location, but offering a dropdown with 'Current Locations' also seems quite sensible, as it could be used to pre-populate the path for each file being restored. If the user did not change these settings, and the paths exist, you can run the code I just did at your suggestion. If the user changes any of the paths, you can add a MOVE clause as required, and warn the user of the consequences. / comments
Ok, your workaround worked. Thanks.
The bottom line in any app is that it should behave as expected. It's fine to default to the new server default location, but offering a dropdown with 'Current L...