Restore SQL Server Database 3

[kingduck]

I am running SQL server 2000
I have created a backup of a database through VB 6.0
How can i restore that backup to the database through code?
Can i restore the backup to another database on the server, through code?

Thanks in advance.

 

[mrdenny]

Look up the RESTORE DATABASE command in SQL Server Books Online.

Can i restore the backup to another database on the server, through code?

Yes (see the RESTORE DATABASE command).

The basic syntax is:

RESTORE DATABASE DatabaseName FROM DISK='c:\path\to\file.bak'

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[kingduck]

Thanks very much Denny I looked up the restore command it is all that i need. Thanks for your Help

 

[macleod1021]

If you want to get real creative on the backup and restore (and other administrative tasks) google "SQLDMO". This is the same reference that Enterprise Manager uses to administer a server so you can do whatever the manager can do programatically.

 

[kingduck]

Thanks macleod1021 for the tip but when i searched google it returned 128,000 Hit. the first was about the C language.

The restore worked fine, But when i tried to restore it to a newly created database it raised an error that the original database -That i created the backup from- file is in use by SQL server the old database is called Hospetail and the new database that i am tring to restore the database to is called AAA.

Thanks in advance

 

[tazuk]

A slightly more detailed search on Google like this returns a more manageable 187 hits, including a useful article here. To access the article you'll need to register (free) but apart from the odd email I haven't experienced any spam problems with the site (

http://www.sqlserver.com),

and they have a number of articles on SQLDMO.

There are also a number of useful faqs and posts in Tek-Tips forum183 (SQL Server Programming Forum) such as faq183-1637 by tlbroadbent.

HTH

TazUk

Blue-screening PCs since 1998

 

[kingduck]

Hi tazUK thanks for your reply .

I am still looking but still no luck. I am tring to restore a database on a new blank database that i created. I have done this from the SQL Enterprise Manager using an option called force restore over existing databse but i can't find semilar option through code.

Can any one Help, Please.
Thanks in advance

 

[tazuk]

There is a force restore example given here that may be worth testing out. I haven't tried it myself yet as I've only starting using SQLDMO in the last year or so. I've just finished a brain draining week and half of development in a 4 day timescale so I won't make an attempt at code myself.

HTH

TazUk

Blue-screening PCs since 1998

 

[macleod1021]

kk...here's what you need:
(Create a reference in your app to SQLDMO)

Set srv=Server.CreateObject("SQLDMO.SQLServer")
srv.Connect "servername", "username", "password"
Set rest=Server.CreateObject("SQLDMO.Restore")
rest.Action=0 ' full db restore
rest.Database="Northwind"
rest.Devices=Files
rest.Files="c:\nwind.bak"
rest.ReplaceDatabase=True 'Force restore over existing database
rest.SQLRestore srv

set rest=nothing
set srv=nothing

 

[macleod1021]

Oh yeah...forgot to mention that whenever I do searches on Google, I add "Visual Basic" to the end so that it narrows the search down a bit.

 

[mrdenny]

The flag to force the overwrite is REPLACE.

RESTORE DATABASE Test FROM DISK='d:\test.bak'
WITH MOVE 'Test_data' TO 'D:\MSSQL\MSSQL\Data\Test.mdf',
     MOVE 'Test_Log' to 'E:\MSSQL\MSSQL\Data\Test.ldf',
     REPLACE

I hate to break it to you, but all SQLDMO does is add another layer that SQL needs to process to run the command. Using SQLDMO just have the SQL Server translate that into T/SQL then it executes the T/SQL.

It's easier and cleaner for the SQL Server to simply write the T/SQL.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com