I need help scripting a Restore of an SQL database

[aleph01]

I have one (1) bit more than an inkling (which really means nothing) about SQL, but my wife, who does software support, is having an issue with a client who needs to have a database restored.  She takes an SQL database.bak file from a server, copies it to a client machine and she needs me to write a script to restore the database.

This is what code I have, which copies the .bak file to the client machine, and I have it opening the sql executable, but I'm not even sure if that's what needs to happen next.  Maybe a ShellExecute is in order, but I'm really at a loss.

Help me Obiwan, you're my only hope.  Oh, and btw, thanks in advance.

_Aleph_

FileDelete ("C:\path\filename.bak") ; delete the old .bak file
FileCopy ("path to file - this part works fine", "path to destination, also works fine", 1) ;copy the file from the server to the client machine
Run ("C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\ssms.exe")

I can copy the file from the server to the client machine, but I no idea how to do the restore.  This is all done while the client business is closed,  but not always, so it needs to be in the background.  It's beyond me, but I like to learn

_aleph_

[orbs]

before i begin, this must be asked: are you sure a regular automated restore is a good thing? i'm not at all sure. whatever you need to do, i bet there are better ways to do it. database restore is not something to be taken lightly, anything and everything can go wrong.

but if that's out of the way, then...

hate SQL. but one good lead i found is this:

http://blog.nitorsys.com/restore-sql2008-bak-file-to-local-machine/

basically, an SQL query does what you need (as long as you have all the details, like logical names). that link provides 2 queries: one to get the logical name, and one to do the restore. i'll quote it here, but you should read the full article.

this to list logical names:

restore filelistonly from disk = 'E:path_to_bak_filedatabase_name_634182143083281212.bak'
GO

this to restore:

RESTORE DATABASE my_new_database FROM disk = 'E:path_to_bak_filedatabase_name_634182143083281212.bak'
WITH
   MOVE 'the logical name from previous operation check row 1' TO 'E:path_to_sql2008_file_onyour_machinemy_new_database.mdf',
   MOVE 'the logical name from previous operation check row 2' TO 'E:path_to_sql2008_file_onyour_machinemy_new_database_log.ldf'
GO

i didn't check it, but it seems sound.

of course, you need to make sure the database you restore over is not in use.

to get it to work, AutoIt has an SQL UDF you can use. see here:

'?do=embed' frameborder='0' data-embedContent>>

enjoy!

PS don't look me up when things go messy. i warned you in the first sentence... 

[jchd]

I totally ignore specifics of MS SQL but I also question the need to restore a DB, except after some kind of rare disaster.

Alternative ways are to devise the correct sync-ing of the client wrt the server or to use replication capabilities possibly offered by SQL server.

[t0nZ]

From an old script of  mine, but used nowadays:

FileDelete("c:\mssql\backup\log_restore.txt")
    Run(@ComSpec & ' /c sqlcmd -U sa -P password -Q "RESTORE DATABASE [dbname] FROM  DISK= ''' & $DBFILE & ''' WITH  FILE = 1, NOUNLOAD, REPLACE, STATS = 10" -o c:\mssql\backup\log_restore.txt')
    $fermaciclo = 1
    $ultimalinea = " "
    $salvaultimalinea = " "
    While 1
        Sleep(1000)
        If FileExists("c:\mssql\backup\log_restore.txt") Then
            $ultimalinea = FileReadLine("c:\mssql\backup\log_restore.txt", -1)
            If $salvaultimalinea <> $ultimalinea Then
                _GUICtrlEdit_AppendText($Edit5, " Restore ->" & $ultimalinea & @CRLF)
                $salvaultimalinea = $ultimalinea ; to show one time %
                Sleep(3000)
            EndIf
            ;_GUICtrlEdit_AppendText($Edit5, "log_Restore.txt -->" & $ultimalinea & @CRLF)
            Sleep(3000)
            If StringInStr($ultimalinea, "RESTORE DATABASE") <> 0 Then ; exit on restore finished


                ExitLoop
            EndIf
        Else
            $fermaciclo = $fermaciclo + 1
            If $fermaciclo = 40 Then ExitLoop
        EndIf
    WEnd
    _GUICtrlEdit_AppendText($Edit5, "Restore database eseguito (log in c:\mssql\backup)..  " & @CRLF)

This one works with m$sql versions form 2005 to 2012, and the cycle is to display from the log the % of running restore operation.

We have some clients that uses a script like a time machine to go back and forth with databases, for testing purposes.

[aleph01]

Thanks, all.  I'll look all this over this evening and see if I can get something figured out.  As far as the restore is concerned, she usually has a tool that performs this periodically, but the programmer for the company periodically breaks the tool while fixing the tool.  Though it sounds like a contradiction, I'm sure everyone here has broken a script while fixing it.  Anyway, her programmer, for some reason, isn't getting this fixed in a timely manner, so if we can get the database copied off the server to the client and restored, she'll be able to run the tool.  The tool fails because the database isn't being restored.  This script just needs to get it copied and restored.