Restoring a Databse in SQL Server using ASP.Net

Posted on 2005-02-27
Medium Priority
Last Modified: 2008-02-01
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.
Question by:unisyn
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2

Accepted Solution

gabeso earned 1500 total points
ID: 13422985
   FROM DISK = 'c:\MyDatabase.bak'
   WITH MOVE 'MyDatabase' TO 'c:\newlocation\MyDatabase.mdf',
   MOVE 'MyDatabase_log' TO 'c:\newlocation\MyDatabase.ldf'

Author Comment

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

Author Comment

ID: 13423309
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.


Expert Comment

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


Author Comment

ID: 13433453
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

Author Comment

ID: 13435672
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

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

Featured Post

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this article, we’ll look at how to deploy ProxySQL.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question