Solved

SQL restore data base through Vbscript

Posted on 2015-02-19
11
432 Views
Last Modified: 2015-03-08
Hello,

I have the following VB script to restore a data base

I would like to add a loop in order to restore the most recent file from a path. With this I wouldn't need to type every time the name of the file to restore.


'VBScript to Restore the database

Option Explicit

Dim adoCommand
Dim strConnectionstring
Dim strBackupFile
Dim strServreName
Dim strDatabaseName

' Specify backup path to be restored to the database.
strBackupFile = "D:\BackUpPath\BackUpFile.bak"

'Specify the name of server and instance if present to which backup file need to be restored .
strServreName ="ServerName"

'Specify the database name to restore the file.
strDatabaseName ="DataBaseName"

' Specify connection string to Master Database on SQL server, else u may see errors like
' Error: RESTORE cannot process database 'DataBaseName' because it is in use by this session. It is recommended that the master database be used when performing this operation.
' If using the default instance, use SERVER=MyServer.
strConnectionstring= "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MASTER;Data Source=" & strServreName

' Restore database from backup.
Set adoCommand = CreateObject("ADODB.Command")
On Error Resume Next
adoCommand.ActiveConnection = strConnectionstring
If (Err.Number <> 0) Then
Wscript.Echo "Unable to connect to SQL database."
Wscript.Echo Err.Description
Wscript.Quit
End If
On Error GoTo 0
adoCommand.CommandText = " Create database " & strDatabaseName
adoCommand.CommandText = "RESTORE DATABASE "& strDatabaseName &" FROM DISK='" & strBackupFile & "' with Replace"
adoCommand.Execute
MsgBox "Restore Completed Successfully "
Set adoCommand = Nothing
0
Comment
Question by:LD16
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40621342
You don't need the CREATE DATABASE statement. Will give error if the database already exists.
The RESTORE command will create or overwrite the database in case of it already exists. And if it exists you need to be sure that no one is connected to the database or else the command will fail.

Also, you are only taking in considerations the full backups? What about the differential backups? And the transaction log backups?
What about if there is more than one backup file from the same database? How do you'll handle this?

And the most important thing. You are creating a dangerous script. Bad use of this script will destroy your databases quickly. Use it very, very wise.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 40631410
Hi,

As above: Be very very careful.

A mistake I've seen is attempting to restore a database with a corrupt backup, and while the current database had issues, its far better than the nothing a corrupt backup may give.

Regards
  David
0
 

Author Comment

by:LD16
ID: 40631660
Hello,
Thank you all for your comment, in that case could you please prove an SQLCMD, PowerShell in which I need to put just the path of the DB that I want to restore.

The restore need to be with Overwrite existing DB.

Thank you again for your help.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 35

Expert Comment

by:David Todd
ID: 40631759
Hi,

Not so simple.

The reason is that when you restore from a foreign backup, SQL has no idea where to put the datafiles. If you restore a database from a recent backup then SQL may guess on the location and name of the physical datafiles.


ie
restore database mydatabase
from disk = 'ThePhysicalPath&Filename&Extension'
with
    replace
    , move 'datalogicalfile' to 'dataphysicalPath&File&Extension'
    , move 'loglogicalfile' to 'logphysicalPath&File&Extension'
    , stats - 5
;

So, all that to ask, how from the path is the script supposed to guess the databasename, the datalogicalfile, the loglogicalfile etc. Assuming that there is only one data file and one transaction log file.

Regards
  David
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40632717
The restore need to be with Overwrite existing DB.
Data and transaction log files included? If so, you need to assure that directory structure in the target server it's the same as in the source server.
0
 

Author Comment

by:LD16
ID: 40632777
Yes, it is the case.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40632943
Then the command should be simple as:
RESTORE DATABASE DatabaseName
FROM DISK = 'drive:\\path\filename.bak'

Or by your code:
adoCommand.CommandText = "RESTORE DATABASE "& strDatabaseName &" FROM DISK='" & strBackupFile

Open in new window

0
 

Author Comment

by:LD16
ID: 40643359
@Victor: Can I run this code with a .bat file?
How can I add a loop to restore the most recent file available at:
drive:\\path\?
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40646716
What you mean a .bat file?
You can use SQLCMD that accepts a SQL Server script as input.
0
 

Author Comment

by:LD16
ID: 40648215
Yes I mean a .bat file?
Could you please provide the full SQLCMD version?

Thank you in advance.
0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40648724
This is an example to run a script (-i) in a named instance (-S) with integrated authentication (-E)
SQLCMD -S ServerName\InstanceName -E -i Restore.sql

Open in new window

You can add that command into a .bat file if you want.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question