asked on

Restoring a Databse in SQL Server using ASP.Net

Using ASP.net how do i programmtically restore a database in SQL Server from a dat file created using the dump device? is thier a stored proc which i can call to do this.

ASKER CERTIFIED SOLUTION

gabeso

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

unisyn

ASKER

Thanks for that, how do i implement this in the code? Im using vbscript.

unisyn

ASKER

Is it Something Like This?

dim strQuery
strQuery = "RESTORE DATABASE MyDatabase "
strQuery = strQuery & " FROM DISK = 'c:\MyDatabase.bak' "
strQuery = strQuery & " WITH MOVE 'MyDatabase' TO 'c:\newlocation\MyDatabase.mdf', "
trQuery = strQuery & " MOVE 'MyDatabase_log' TO 'c:\newlocation\MyDatabase.ldf' "

dim objCmd = new SqlCommand(strQuery, MasterCon)
objCmd.CommandType = CommandType.Text
Dim myReader As SqlDataReader = objCmd.ExecuteReader
myReader.Close()

gabeso

Yes. This is just a Transact-SQL command to restore a backup. So execute it with ado or ado.net.

unisyn

ASKER

I am trying to restore a database over an existing one so when trying to run the above function it returns an error saying that it can't do it because other users are still using the database. Any ideas on how i drop all users from the database before i try to restore

unisyn

ASKER

This Kils all the connections to the databse first so that you can then restore a db over the top of an existing one.

Dim strQuery, DBName, MasterCon

MasterCon = 'connection to the master database'
DBName = 'databse name'

strQuery = "DECLARE @command NVARCHAR(500)"
strQuery = strQuery & " DECLARE @spid INT"
strQuery = strQuery & " DECLARE active_spids CURSOR LOCAL"
strQuery = strQuery & " FOR SELECT s.spid FROM master.dbo.sysprocesses s"
strQuery = strQuery & " WHERE (s.dbid = (SELECT dbid FROM master.dbo.sysdatabases WHERE (name = 'DBName')))"
strQuery = strQuery & " OPEN active_spids"
strQuery = strQuery & " FETCH NEXT FROM active_spids INTO @spid"
strQuery = strQuery & " WHILE (@@fetch_status = 0) BEGIN"
strQuery = strQuery & " SET @command = 'KILL ' + CONVERT(VARCHAR(10), @spid)"
strQuery = strQuery & " EXECUTE ( @command )"
strQuery = strQuery & " FETCH NEXT FROM active_spids INTO @spid"
strQuery = strQuery & " END"

dim TSQL = new SqlCommand(strQuery, MasterCon)
TSQL.CommandType = CommandType.Text
TSQL.ExecuteNonQuery

Hope this helps for all those trying to do this after me