Hi I need to restore database, which I am tesing.I write this-Set sql = CreateObject("SQLDMO.SQLServer") sql.LoginSecure = Truesql.Connect cConnectionStringSet sqlRest = CreateObject("SQLDMO.Restore")sqlRest.Files = "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\as_tesing_05042012.bak"sqlRest.Database = "as_testing_test"sqlRest.Action = SQLDMORestore_DatabasesqlRest.ReplaceDatabase = TruesqlRest.SQLRestore sqlBut I have an error, which says "Microsoft VBScript runtime error ActiveX component can't create object: 'SQLDMO.SQLServer'".what to do avoid this?Thanks in advance

Also I try in this way.Set aCon = ADO.CreateConnectionaCon.ConnectionString = cConnectionString aCon.OpenaCon.CommandTimeout = 300Set aCmd = ADO.CreateCommandaCmd.ActiveConnection = aConaCmd.CommandType = adCmdTextaCmd.CommandText = "ALTER DATABASE as_testing_test SET SINGLE_USER WITH "& _ "ROLLBACK IMMEDIATE "&_ "RESTORE DATABASE as_testing_test "& _ "FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\as_tesing_05042012.bak' "& _ "WITH MOVE 'as_enterprise_model_Data' "& _ "TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\as_testing_test.mdf', "& _ "MOVE 'as_enterprise_model_Log' "& _ "TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\as_testing_test.ldf' "&_ "ALTER DATABASE as_testing_test SET MULTI_USER"Set aRecSet = aCmd.ExecuteBut I have an timeout error, although I set commandtimeot = 300 seconds. In Sql it works fine, and lasts about 180 seconds.

Hi,Where I work, we've had success using the SQL Server command line interface and the wscriptshell wrapper around it.We build the commandline as:'osql -S ' + ServerName + ' -U ' + UserID + ' -P ' + Password + ' -Q " RESTORE DATABASE ' + DBName + ' FROM DISK = ''' + Path + DBName+ '.BAK'' WITH REPLACE "' + ' -o "' + Project.Path + 'RestoreResult.txt"'Piping the output to a result file allows us to check that the restore process succeeded by reading the file.We access the command line via:Sys.OleObject('WScript.Shell')The only weakness we've found with this approach is that we do need to read the piped output file to check that the restoration succeeded, as the commandline interface will return 0 for successful to regardless of the outcome of the restore operation. If the restore was successful, the result file will contain the text "RESTORE DATABASE successfully processed" so it's relatively simple to use aqFile.ReadWholeTextFile to read the result into a string then search that string for the text and pass/fail based on whether it exists.I hope that helps, Kate

Thanks for your response Kate.But I have 2 questions.Must I write your code in connectionstring?Where in your code restrict access from Multi User to Single User?Also for me is very interesting why I have that two errors. If anybody knows please help me

In cmd.exe it works fine Kate. In first I didn't understand what i must do:)

Vahagn,Also for me is very interesting why I have that two errors. If anybody knows please help meI think, you got the "Microsoft VBScript runtime error ActiveX component can't create object" error, because some appropriate components (SQL Database Management Objects) are not installed on your computer, or they are not registered properly in the system. I think, you can try downloading SQLDMO modules from the Microsoft web site and install them on your computer. Follow the links below:http://technet.microsoft.com/en-us/library/ms141187(v=SQL.100).aspxhttp://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/5cfb1ef2-9da3-492f-ace6-4eafdb73b34aMicrosoft says that these objects are obsolete (http://msdn.microsoft.com/en-us/library/ms131540(v=SQL.105).aspx), so you may need to consider using some other approaches to restore your database. Kate's approach seems to be a reasonable solution.As for the second error, I think, performing this operation via COM ADO objects just takes more time than doing it in SQL Management Studio. You could try increasing the timeout, say, to 600 seconds, and check if this works.BTW, to restore databases and set up the test environment, you can try using Automated Build Studio. It provides an easy-to-use visual way to perform these and many other operations. As far as I know, it takes an active part in our QA tasks at SmartBear. A free trial is available here: http://smartbear.com/products/software-development/software-release-management/free-automated-build-studio-trial.Anyway, I'm glad to hear that Kate's solution works fine for you :-)

Restore database | SmartBear Community

11 Replies

ASV
Contributor
13 years ago
Also I try in this way.

Set aCon = ADO.CreateConnection

aCon.ConnectionString = cConnectionString

aCon.Open

aCon.CommandTimeout = 300

Set aCmd = ADO.CreateCommand

aCmd.ActiveConnection = aCon

aCmd.CommandType = adCmdText

aCmd.CommandText = "ALTER DATABASE as_testing_test SET SINGLE_USER WITH "& _

"ROLLBACK IMMEDIATE "&_

"RESTORE DATABASE as_testing_test "& _

"FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\as_tesing_05042012.bak' "& _

"WITH MOVE 'as_enterprise_model_Data' "& _

"TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\as_testing_test.mdf', "& _

"MOVE 'as_enterprise_model_Log' "& _

"TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\as_testing_test.ldf' "&_

"ALTER DATABASE as_testing_test SET MULTI_USER"

Set aRecSet = aCmd.Execute

But I have an timeout error, although I set commandtimeot = 300 seconds. In Sql it works fine, and lasts about 180 seconds.
katepaulk
Occasional Contributor
13 years ago
Hi,

Where I work, we've had success using the SQL Server command line interface and the wscriptshell wrapper around it.

We build the commandline as:

'osql -S ' + ServerName + ' -U ' + UserID + ' -P ' + Password + ' -Q " RESTORE DATABASE ' + DBName + ' FROM DISK = ''' + Path + DBName+ '.BAK'' WITH REPLACE "' + ' -o "' + Project.Path + 'RestoreResult.txt"'

Piping the output to a result file allows us to check that the restore process succeeded by reading the file.

We access the command line via:

Sys.OleObject('WScript.Shell')

The only weakness we've found with this approach is that we do need to read the piped output file to check that the restoration succeeded, as the commandline interface will return 0 for successful to regardless of the outcome of the restore operation. If the restore was successful, the result file will contain the text "RESTORE DATABASE successfully processed" so it's relatively simple to use aqFile.ReadWholeTextFile to read the result into a string then search that string for the text and pass/fail based on whether it exists.

I hope that helps,

Kate
ASV
Contributor
13 years ago
Thanks for your response Kate.

But I have 2 questions.

Must I write your code in connectionstring?

Where in your code restrict access from Multi User to Single User?

Also for me is very interesting why I have that two errors. If anybody knows please help me
ASV
Contributor
13 years ago
In cmd.exe it works fine Kate. In first I didn't understand what i must do:)
AlexeyK
SmartBear Alumni (Retired)
13 years ago
Vahagn,

Also for me is very interesting why I have that two errors. If anybody knows please help me

I think, you got the "Microsoft VBScript runtime error ActiveX component can't create object" error, because some appropriate components (SQL Database Management Objects) are not installed on your computer, or they are not registered properly in the system. I think, you can try downloading SQLDMO modules from the Microsoft web site and install them on your computer. Follow the links below:

http://technet.microsoft.com/en-us/library/ms141187(v=SQL.100).aspx

http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/5cfb1ef2-9da3-492f-ace6-4eafdb73b34a

Microsoft says that these objects are obsolete (http://msdn.microsoft.com/en-us/library/ms131540(v=SQL.105).aspx), so you may need to consider using some other approaches to restore your database. Kate's approach seems to be a reasonable solution.

As for the second error, I think, performing this operation via COM ADO objects just takes more time than doing it in SQL Management Studio. You could try increasing the timeout, say, to 600 seconds, and check if this works.

BTW, to restore databases and set up the test environment, you can try using Automated Build Studio. It provides an easy-to-use visual way to perform these and many other operations. As far as I know, it takes an active part in our QA tasks at SmartBear. A free trial is available here: http://smartbear.com/products/software-development/software-release-management/free-automated-build-studio-trial.

Anyway, I'm glad to hear that Kate's solution works fine for you :-)
auutumn
New Contributor
12 years ago
I tried to use the commands listed in this discussion without any success, and absolutely no success talking with SmartBear, so I'm looking to the users to help me out.

I'd like to know exactly what steps I need to do in order to restore a SQL database? If someone could show me a complete script I'd appreciate it.

Thx in advance,

Christopher
AlexKaras
Champion Level 3
12 years ago
Hi Christopher,

Actually, Kate's message contains everything needed. If you let us know what exactly does not work for you, it might help to provide you with some piece of advice...
auutumn
New Contributor
12 years ago
I created the following subroutine:

Sub RestoreDatabase()

Sys.OleObject("Wscript.Shell")

osql -S ' + 10.24.0.156 + ' -U ' + sa + ' -P ' + 4rkI0N + ' -Q " RESTORE DATABASE ' + ArkNET_TestComplete + ' FROM DISK = ''' + D:\Database\ArkNET_TestComplete\Backup\ + TestComplete_Backup_5_22_2012 + '.BAK'' WITH REPLACE "' + ' -o "' + c:\temp\ + 'RestoreResult.txt"'

End sub
AlexKaras
Champion Level 3
12 years ago
Hi Christopher,

> I created the following subroutine [...]

The subroutine has a couple of things missed and/or incorrect:

a) Sys.OleObject("Wscript.Shell") just instantiates an instance of script shell object. The Run method must be called to execute command passed as a parameter: Sys.OleObject("Wscript.Shell").Run(strCommandToExecute)

b) Single quote character ( ' ) starts single-line comment but not a string in VBScript. Double quote ( " ) must be used to define a string. Note, that if a string contains double quote inside it, such double quotes must be doubled: strString = "a string with a "" inside"

c) The second line in subroutine contains an attempt to concatenate several substrings, but the result is not assigned to any variable and thus is unusable. Should be something like this: strCommand = "osql - S " & strServerName & " -U "... Then the obtained command line should be passed as a parameter to the Shell.Run method: Sys.OleObject("Wscript.Shell").Run(strCommand)

d) Not all components of the command line you are trying to create can be evaluated as strings or variables. E.g.:

osql -S ' + 10.24.0.156 + ...

Here the 10.24.0.156 is neither a string nor a variable name and the script will eventually fail here.
husainmdk
12 years ago
Hi All,

i am trying to do restore using the below script.

def test = testRunner.testCase.getPropertyValue("Test");

Sys.OleObject("WScript.Shell");

def strCommandToExecute = 'osql -S ' + ServerName + ' -U ' + UserID + ' -P ' + Password + ' -Q " RESTORE DATABASE ' + DBName + ' FROM DISK = "' + Path + DBName + '.BAK';

//def strCommandToExecute = 'osql -S ' + 'localhost' + ' -U ' + 'hkadwalwala' + ' -P ' + 'HU99**en' + ' -Q " RESTORE DATABASE ' + "Test1' + ' FROM DISK = " + 'D:\database_backup\' + 'Ntier.security'+ '.BAK'' WITH REPLACE "' + ' -o "' + + 'c:\RestoreResult.txt"'

Sys.OleObject("Wscript.Shell").Run(strCommandToExecute);.

whey i try to execute in groovy . i get error : Sys property is not defined . can some one help ? am i missing some setting.

relatively new to SOAP UI world.

Regards,

Husen