How do a restore to a different DB name having different file locs as well?

willg

We use SQL Backup 9.2.7.543

I need to take a restore file made for database X and restore it to database Y.  Both databases, X and Y, must co-exist on the same server. 

For a simple example, say I run the following:

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [hoit] TO DISK = ''G:\Backup\FULL_hoit_by_me.sqb'' WITH CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, INIT, THREADCOUNT = 3"'

Now I want to restore HOIT under a new name on the same server, so the file names have to change too.

To do that, I tried the following:

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [hoit001] FROM DISK = '' G:\Backup\FULL_hoit_by_me.sqb'' WITH MOVE DATAFILES TO ''F:\hoit001'', MOVE LOGFILES TO ''G:\hoit001'', REPLACE, DISCONNECT_EXISTING, ORPHAN_CHECK, RECOVERY"'

That fails with the following errors:

SQL error 3013: RESTORE DATABASE is terminating abnormally.
SQL error 3119: Problems were identified while planning for the RESTORE statement. Previous messages provide details.
SQL error 3156: File 'hoit_log' cannot be restored to 'G:\hoit001\hoit_log.ldf'. Use WITH MOVE to identify a valid location for the file.
SQL error 5133: Directory lookup for the file "G:\hoit001\hoit_log.ldf" failed with the operating system error 2(The system cannot find the file specified.).
SQL error 3156: File 'hoit' cannot be restored to 'F:\hoit001\hoit.mdf'. Use WITH MOVE to identify a valid location for the file.
SQL error 5133: Directory lookup for the file "F:\hoit001\hoit.mdf" failed with the operating system error 2(The system cannot find the file specified.).

It seems like it expects the files to already be there.  They aren't.  This is a new db.  How can I do this?

petey2

SQL Backup will not create new folders for the MOVE options, but will create new folders for backup locations and COPYTO/MOVETO locations.

So one option would be to create the new folders by backing up a small database to the new folders prior to performing the restore e.g.

EXEC master..sqlbackup '-sql "BACKUP DATABASE model TO DISK  = [F:\hoit001\dummy_01.sqb], DISK = [G:\hoit001\dummy_02.sqb] WITH INIT"'

Dan B

Hi willg,

Syntactically this looks correct and it does indeed restore a Database on my local machine.

Are the original HOIT data files stored in the same G and F folders?

willg

No, the original HOIT files are in other folders.  They are on G and F but different folder names than the script.   

petey2

Do the target folders (F:\hoit001 and G:\hoit001) already exist?  Neither SQL Backup nor SQL Server will automatically create the target MOVE folders, you will need to create them manually prior to running the restore.

willg

petey2 said:

Do the target folders (F:\hoit001 and G:\hoit001) already exist?  Neither SQL Backup nor SQL Server will automatically create the target MOVE folders, you will need to create them manually prior to running the restore.

No, the folders don't exist.  SQL Sever's MOVE will not create a folder that doesn't exist?  I recall doing that before with a tsql RESTORE, not with RedGate.   The only issue I've seen was using a login without permission to restore, I do have restore permission.  That's why this is odd for me.

petey2

Have you tried creating the folders (F:\hoit001 and G:\hoit001) and running the restore again?

willg

petey2 said:

Have you tried creating the folders (F:\hoit001 and G:\hoit001) and running the restore again?

Well, I have to do this all via tsql and as far as I know I can't create a folder in tsql without adding permissions to shell, which I don't want to do for security reasons.  So it really doesn't matter if it works when someone manually creates the folders, we need this to be a hands-off automated process.

To give some context, the end goal here is fixing an issue we have with a required middle of the day restore.  During the restore, users can't access the data.  So the idea here is do the restore under a different name so the main db is up and accessible the whole time.   Then when the restore is complete, we flip the users to the new db by taking down the old one and renaming the new one before bringing it on-line.  In that way, we go from a db that's down for 60 to 90 minutes to one that down 5 or less minutes.