• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

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.
0
unisyn
Asked:
unisyn
  • 4
  • 2
1 Solution
 
gabesoCommented:
RESTORE DATABASE MyDatabase
   FROM DISK = 'c:\MyDatabase.bak'
   WITH MOVE 'MyDatabase' TO 'c:\newlocation\MyDatabase.mdf',
   MOVE 'MyDatabase_log' TO 'c:\newlocation\MyDatabase.ldf'
0
 
unisynAuthor Commented:
Thanks for that, how do i implement this in the code? Im using vbscript.
0
 
unisynAuthor Commented:
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()
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

0
 
unisynAuthor Commented:
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
0
 
unisynAuthor Commented:
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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now