Link to home
Start Free TrialLog in

asked on

restoring the databases

I like to do following steps automatically
Need to get the database name from e:\back\databasename_datetime.bak
excluding "_datetime.bak"
get all user and windows logins
restore the database with database name taken in the step 1
import all the user logins.
run orpan users
 and iterate all other files under e:\backup folder and do other mentioned above

Is anyone has done this before?
Problem is how to the database name from file from xp_cmdshell or by another means?
Avatar of Ioannis Paraskevopoulos
Ioannis Paraskevopoulos
Flag of Greece image


I am not sure if i get this right, but you actually need to create a database from a backup in a new server or instance.

If that is the case, then if you open SSMS and login to your server with an account that has the right priviledges(such as an sa) then in the explorer you may expand your instance name. Then right click on 'Databases' and choose 'Restore Database'.

In the window that will pop up instead of choosing 'Database' as a source, choose 'Device' and click the button with the '...'.

Then click the 'Add' button and browse to where your bak file resides.

You will have to choose the Database you need to restore from the backup.

In the 'Destination' section under it you will have to enter the name of the newly created db. This doesn't have to be the same as the one of the db in your backup.

In the grid make sure you have checked your restore plan.

Next you have to go to the 'Files' tab on the left of the window.

Here you have to define where the mdf(data file) and ldf(log file) should be stored on your system. You need to put these somewhere that makes sense to your environment as you may need to access them at some point in the future. Just change the 'Restore As' column.

For your case you are good to go.

Good luck,


nope,You got it wrong.

I need to automate in a such a way that,restore should happen automatically.

To restore database we need to know database name right?
So in this case, i don't want to do it by SSMS console or by command manually. So to get the database name, it should get it from backupfile.

Say,examples i have backup in e:\backup\abc_15032013145959.bak
now the automation should pick the database name(say abc in this case) from filename
In this case abc
1.restore<<pass the databasename dynamically>>with recovery. the logins the orpan users

then it should iterate for other files in e:\backup \ folder in the similar fashion. and perform from step 1 to 3.
This what,i want to achieve.
you can try this:

Dim con As ADODB.Connection
Set con = New ADODB.Connection

con.Open "Full connect string to the SQL Server"

Dim sName As String
Dim iUS As Integer
Dim iBS as Integer
Dim sFile As String
sFile = Dir("e:\back\*.bak")

Do While sFile <> "
   iUS = InStrRev(sFile, "_")
  If iUS <> 0 Then
    iBS = InStrRev(sFile, "\")
    If iBS <> 0 Then
      sName = Mid(sFile, iBS + 1, Len(sFile) - iUS)
    End If
  End If

  con.Execute "RESTORE DATABASE " & sName & " FROM disk='" & sFile & "'"
  sFile = Dir ' gets the next file with the .bak extension

I would test this THOROUGHLY on a testbed environment with your SQL Server. I would NOT run this against a production SQL Server until those tests were completed and you are satisfied that the process will work correctly.

For your connection string, see this page:, in the "SQL Server Native Client 10.0 OLE DB Provider" section. If you're using a different version of SQL Server, that page has all version up to 2012.
Avatar of Qlemo
Flag of Germany image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial