Avatar of VIVEKANANDHAN_PERIASAMY
VIVEKANANDHAN_PERIASAMY
 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?
Microsoft SQL Server 2005Microsoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
Qlemo

8/22/2022 - Mon
Ioannis Paraskevopoulos

Hi,

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,
Giannis
VIVEKANANDHAN_PERIASAMY

ASKER
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.
2.run the logins
3.run 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.
Scott McDaniel (EE MVE )

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
Loop

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:
http://www.connectionstrings.com/sql-server-2008, 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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Qlemo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question