Activity overview
Latest activity by dterrie
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?
use dynamic string for sqlbackup command
Hi,
I need to run sqlbackup nightly to restore a backup using a dynmically constructed string inserting a date in the <AUTO> name string as below so that I can automate the restore process on a sec...
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.....
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...
counting to 10 not working - restore with move error
Be thankful you are a ocean and a continent away. I was asked to restore a db to a new test server on short notice tonight, and SQL Backup turned a simple job into a nightmare. Your UI allows me to...
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...
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...
Restore with multiple filegroups to different server problem
Hi,
I am trying to backup/restore a database with multiple filegroups to a 'MIRROR' server WITH NORECOVERY to set up mirroring for the 'PRINCIPAL' server db. The two servers have identical configur...