Sign in to follow this  
Followers 0
aleph01

I need help scripting a Restore of an SQL database

5 posts in this topic

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 :thumbsup:

_aleph_


Meds.  They're not just for breakfast anymore. :'(

Share this post


Link to post
Share on other sites



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...  ;)

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.


Meds.  They're not just for breakfast anymore. :'(

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0