[RESOLVED] "Down" a SQL Server/Detach & Re-attach a Database to SQL Server

Hi guys. I would like to know if it is possible to "down" (stop) a running SQL Server or detach and then re-attach a database to an instance of SQL Server via code? You see, I would like to back-up (well, actually, it's more of to copy) the database, but, since you are not allowed to copy/delete/move any attached databases, what I would like to do is either to detach the attached database, copy it to a backup folder, then re-attach it, or to simply down the server, copy the database to a backup folder, and then restart the database.

Is this possible by coding in VB .net 2003? If not, then any other technique/method you guys can advise? Thanks a lot!

Why not use the built in backup within SQL server? You can setup a maintenance plan to perform a backup. When I setup some of my databases, I will do a full database backup nightly and backup the transaction log every hour. Worst case scenario, if there is a crash, I lose at most an hour's worth of data.

Well, what I initially want to do is, when the user clicks the "Backup Data" from the MDI Menu, the system will automatically backup the database. Any ideas?

Oh, and how do you use that built-in backup feature of SQL Server? By the way, we're using SQL Server 2000...

search the Database forum (where this thread should be in the first place) for "Backup SQL Server" .... there's scripts and code that people have written that will do this.

-tg

Will do! Just thought that it should be here since I'm looking for VB .net code as well. Is that fine?

You need to do this using SQL commands to the database. Connect to the server. Enusre no one is connected to the database you want to detach. Detach the DB (SQL Command), copy the files to where you want them (File system IO commands) then Reattach then DB (SQL Command)

It goes like this right?

Code:

---

Detach Dbase - SP_DETACH_DB ‘dbname’
Attach Dbase - SP_ATTACH_DB ‘dbname’, ’fileaddress\dbase.mdf’, ‘fileaddress\dbase_log.ldf’

---

So then, what SQL Command will I be using here?

ex for the Insert Command:

Code:

---

Dim dbInsert As New SqlCommand("INSERT INTO Violation(PatronNumber, Violation, Remarks, DateFiled, FiledBy, " & _
                "PatronID) VALUES (@insPatronNumber, @insViolation, @insRemarks, @insDateFiled, " & _
                "@insFiledBy, @insPatronID)", dbConn)
dbAdpViolation.InsertCommand = dbInsert

---

Is there something like, dbAdapter.Attachcommand or something?

Dont need to attach the ldf. SQL Server will do that automaticly. There is no need to change the data adapter just point it to the database on the server. It doesn't care where the file is located just what server and what database name.

Just the MDF right?

Quote:

---

Originally Posted by GaryMazzone

There is no need to change the data adapter just point it to the database on the server.

---

How do you do that? Example syntax?

It's really not that hard to look up:

sql Code:

--Detach
EXEC sp_detach_db N'AdventureWorks'
 
--Attach
EXEC sp_attach_db N'AdventureWorks',
    'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_Data.mdf',
    'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_Log.LDF'

Duplicate threads merged - please post each question (or variation of it) only once. If you think you have posted in the wrong forum, PM a moderator to ask them to move it.

Quote:

---

Originally Posted by riechan

Will do! Just thought that it should be here since I'm looking for VB .net code as well. Is that fine?

---

If your question is database related (even if it involves code), it belongs in the 'Database Development' forum.

To back-up the database you can create a stored procedure that you can call either from VB6 or VB.Net, it would be something like this.

Code:

---

CREATE PROCEDURE BackupDatabase
 AS
SET NOCOUNT ON
BACKUP DATABASE YourDatabase
  TO DISK = C:\DatabaseBackUp.bak'
GO

---

Purpose of backup is for recovery and not simply for the sake of adhering to backup requirement. You should align backup procedures with recovery procedures... have you confirmed that the simple file copy backup procedure you've come up with can be used by the recovery script, e.g. no file version sync conflicts? No point in creating backup files that can't be easily used for recovery (you end up spending hours instead of minutes recovering database).

Best practice is to use the backup and recovery tools that come with the database rather than reinventing the wheel since these ensure that the database is in the correct state for backup (e.g. dirty blocks flushed to disk) as well as perform other important activities and is not simply a "file copy" as most are led to believe.

@leinad: So then, can anyone direct me to a method of backup and restoring the database? I've searched a bit on the topic and found something about SQL DMOs...

Here's a sample code to back-up and restore using SQLDMO in VB6.

Code:

---

Set RestoreMeUp = New SQLDMO.Restore
RestoreMeUp.Action = SQLDMORestore_Database
RestoreMeUp.Database = Database
RestoreMeUp.ReplaceDatabase = True
RestoreMeUp.Files = RestoreLocalPath
RestoreMeUp.PercentCompleteNotification = 5
RestoreMeUp.SQLRestore SQLTarget
Set RestoreMeUp = Nothing

---

Code:

---

Set BackMeUp = New SQLDMO.Backup
BackMeUp.Action = SQLDMOBackup_Database
BackMeUp.Database = Database
BackMeUp.Files = BackupLocalPath
BackMeUp.PercentCompleteNotification = 5
BackMeUp.SQLBackup SQLSource
Set BackMeUp = Nothing

---

Okay, so I was able to backup my database using this code:

Code:

---

        Dim con As SqlConnection = New SqlConnection("Data Source=localhost; Integrated Security=SSPI; Initial Catalog=CBIS")
        Dim cmd As SqlCommand = New SqlCommand("BACKUP DATABASE CBIS TO DISK= 'C:\\CBIS.bak'", con)

        If userAccessLevel = "Librarian" Then
            If (MsgBox("Backup Data?", MsgBoxStyle.YesNo, "CBIS Data Backup")) = MsgBoxResult.Yes Then
                con.Open()
                cmd.ExecuteNonQuery()
                MsgBox("Data has been successfully backed up.", MsgBoxStyle.Information, "Data Backup Successful")
                con.Close()
            Else
                Exit Sub
            End If
        Else
            MsgBox("Cannot access this feature due to User Access Level.", MsgBoxStyle.Exclamation, "Access Denied")
            Exit Sub
        End If

---

Then, when you're going to restore a SQL Database using the RESTORE DATABASE command, you have to make sure that your database allows multiple user access, and then after the restore process, you re-enable the single-user access to the database, right? Like this:

Code:

---

USE master;
ALTER DATABASE CBIS SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO;

RESTORE DATABASE CBIS FROM DISK = 'C:\\CBIS.bak';

USE master;
ALTER DATABASE CBIS SET MULTI_USER WITH ROLLBACK IMMEDIATE;

---

Thing is, when I try to open a new connection after I execute the code above via the ExecuteNonQuery(), it gives me this error:

<b>A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 1 - I/O Error detected in read/write operation)</b>

Anyone know how to fix this?

You need to re-establish the connection. Once you set Single User mode all other connections are severed.

Question, when do I re-establish the connection? Is it before or after I initiate the RESTORE DATABASE command?

Quote:

---

Originally Posted by GaryMazzone

You need to re-establish the connection. Once you set Single User mode all other connections are severed.

---

And another thing, I did restore the connection to the database when I load a new form after I execute the restore command like this:

Code:

---

...
        dbConn.ConnectionString = "Integrated Security=SSPI; Initial Catalog=dbname; Persist Security Info=False;"
        dbConn.Open()
...

---

After you set MultiUser. The entire thing should be done on one connection once you start the process.

Quote:

---

Originally Posted by GaryMazzone

The entire thing should be done on one connection once you start the process.

---

What do you mean by this? Would you mind explaining it to me?

By the way, this is how I used the SQL Statement above in VB:

Code:

---

Private Sub mnuRestoreData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuRestoreData.Click
        Dim con As SqlConnection = New SqlConnection("Data Source=localhost; Integrated Security=SSPI; Initial Catalog=CBIS")
        Dim cmd As SqlCommand

        If userAccessLevel = "Librarian" Then
            If (MsgBox("Restore Data?", MsgBoxStyle.YesNo, "CBIS Data Backup")) = MsgBoxResult.Yes Then

                cmd = New SqlCommand("USE master; ALTER DATABASE CBIS SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO; RESTORE DATABASE CBIS FROM DISK = 'C:\\CBIS.bak'; USE master; ALTER DATABASE CBIS SET MULTI_USER WITH ROLLBACK IMMEDIATE;", con)
                con.Open()
                cmd.ExecuteNonQuery()
                MsgBox("Data has been successfully restored.", MsgBoxStyle.Information, "Data Restore Successful")
                con.Close()
            Else
                Exit Sub
            End If
        Else
            MsgBox("Cannot access this feature due to User Access Level.", MsgBoxStyle.Exclamation, "Access Denied")
            Exit Sub
        End If
    End Sub

---

USE MASTER? In a SQL statement executed against a different database?

Did that work for you?

Well, it was, according to this thread I was reading: [URL="http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/aad41cbb-10cb-4109-9e55-aab048bbeb9d[/URL]

And yes, I tried the code:

Code:

---

USE master
ALTER DATABASE CBIS SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

RESTORE DATABASE CBIS FROM DISK = 'C:\\CBIS.bak';

USE master
ALTER DATABASE CBIS SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

---

Resulting to:

Processed 128 pages for database 'CBIS', file 'CBIS_Data' on file 1.
Processed 1 pages for database 'CBIS', file 'CBIS_Log' on file 1.
RESTORE DATABASE successfully processed 129 pages in 0.136 seconds (7.728 MB/sec).

Quote:

---

Originally Posted by riechan

Well, it was, according to this thread I was reading: [URL="http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/aad41cbb-10cb-4109-9e55-aab048bbeb9d[/URL]

And yes, I tried the code:

Code:

---

USE master
ALTER DATABASE CBIS SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

RESTORE DATABASE CBIS FROM DISK = 'C:\\CBIS.bak';

USE master
ALTER DATABASE CBIS SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

---

Resulting to:

Processed 128 pages for database 'CBIS', file 'CBIS_Data' on file 1.
Processed 1 pages for database 'CBIS', file 'CBIS_Log' on file 1.
RESTORE DATABASE successfully processed 129 pages in 0.136 seconds (7.728 MB/sec).

---

Looks like you are posting something you "executed" in Management Studio - with the USE and GO statements.

Are you saying that also works as a SQL string from VB?

Yes and no. Yes, I did use it in Management Studio, and no, it doesn't work in VB. As I was saying, it would always result to this whenever I execute the RestoreData sub and then open a new form:

A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 1 - I/O Error detected in read/write operation)

This is really, really simple.

USE and GO are not SQL statements.

They are commands in MANAGEMENT STUDIO that are equivalent to you doing:

"USE" - open a connection to the DB from VB - I see you know how to do this - so open a connection to MASTER.

"GO" - this is the same as executing the statements prior to the GO in a string - all by itself. Each "GO" needs to be a separate EXECUTENONQUERY.

According to GaryMazzone,

Quote:

---

Once you set Single User mode all other connections are severed.

---

Does this mean I have to re-connect to the server (re-open the connection) after I set the database access to single-user mode?

You have a desire to RESTORE a database.

It would be best practice to close the connections to that database from within your application - if you happen to leave connections open (which is a whole other conversation we can have).

To restore a database you still need a connection to the server - but of course you need to connect to something other then the DB you are about to restore!

So connect to MASTER - if you feel that security is no issue here.

So - first two questions?

1) Can you close the connections to the DB you are about to restore?
2) Can you open a connection to a different DB on that server - such as MASTER or RestorePortal (if you were to create a DB with that name, for instance)?

1. Yes, I can. In fact, during the time that the restore command is about to be executed, there are no open connections to the database.
2. Yes, I can connect to the master database, actually.

So, I did it like this, according to what you've said ('use' statement, meaning connecting to the specified database, and go, meaning executenonquery):

Code:

---

...
        Dim con As SqlConnection = New SqlConnection("Data Source=localhost; Integrated Security=SSPI; Initial Catalog=master")
        Dim cmd As SqlCommand

            If (MsgBox("Restore Data?", MsgBoxStyle.YesNo, "CBIS Data Backup")) = MsgBoxResult.Yes Then

                cmd = New SqlCommand("ALTER DATABASE CBIS SET SINGLE_USER WITH ROLLBACK IMMEDIATE;", con)
                con.Open()
                cmd.ExecuteNonQuery()

                cmd = New SqlCommand("RESTORE DATABASE CBIS FROM DISK = 'C:\\CBIS.bak';")
                cmd.ExecuteNonQuery()

                cmd = New SqlCommand("ALTER DATABASE CBIS SET MULTI_USER WITH ROLLBACK IMMEDIATE;", con)
                cmd.ExecuteNonQuery()

                MsgBox("Data has been successfully restored.", MsgBoxStyle.Information, "Data Restore Successful")
                con.Close()
            Else
                Exit Sub
            End If
...

---

Now, it gives me this error:

ExecuteNonQuery: Connection property has not been initialized.

and points to the ExecuteNonQuery() line. I'm a bit lost. What is the connection property and where should I be placing it?

cmd.connection = con

Thanks szlamany. Now I am able to restore the database from a backup. Question though, whenever I try to re-connect to another database in the server through other forms (the Backup Data sub is found in the MDI form), I get this error:

A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 1 - I/O Error detected in read/write operation)

And it points out to the fill method of that form (in the form's Load sub):

Code:

---

        dbConn.ConnectionString = "Integrated Security=SSPI; Initial Catalog=CBIS; Persist Security Info=False;"
        dbConn.Open()

        'initially populate the dataset
        dbAdpPatronType = New SqlClient.SqlDataAdapter("SELECT * FROM PatronType", dbConn)
        dbDsetPatronType = New DataSet
        dbAdpPatronType.Fill(dbDsetPatronType, "PatronType")

---

Quote:

---

Originally Posted by szlamany

So connect to MASTER - if you feel that security is no issue here.

---

And what did you mean by 'if you feel that security is no issue here'? How can I reinforce security measures here?

Not sure about your form load problem...

But for your other question. You are giving the users of a application level database access to the MASTER DB which controls many server functions across any and all database.

If you think that having someone browse with EXCEL to your MASTER DB using the SSPI security context that you give them in the application is not a problem then you can live with this.

If you are concerned about users doing this then you should create a special DB - called something like RestorePortal. Give this DB the proper security rights to only do the restore work.

At least you will be minimizing admin access to the server a bit more.

I'm thinking of having the application shut down after the restore process.

So then I should just redirect the connection to another user-created database instead of the MASTER database?

I'm starting to think that using SQL Server Management Object (SQL SMO - the .NET replacement for SQLDMO) might be easier to use.

-tg

@tg - I've never used SQLDMO (nor needed to do what the OP is doing anyway!) - so I would have to defer to you.

I kind of feel that doing a db restore as part of application life cycle is not so good architecture anyway - auditors would hate this idea!

Quote:

---

Originally Posted by szlamany

I kind of feel that doing a db restore as part of application life cycle is not so good architecture anyway - auditors would hate this idea!

---

Why is that?

Quote:

---

Originally Posted by riechan

Why is that?

---

If you were to run the application and print some vendor checks, lets say - then you restore the DB and the audit trail of those checks is lost...

Or you enroll 100 students and get federal compensation in your lunch program based on that enrollment and then you restore the DB - no audit trail...

I never asked what type of application you had here - or what your reasons for restore are...

Well, it's actually a library management system. Would there be a need for a backup & restore feature for this kind of system? I just thought that there is a need for it since it handles huge amounts of data.

My experience with real database disasters over the past 30 years makes me think that most failures are "table" related - CHECKDB comes back and tells you that some disk blocks are lost and that your CustomerAddress table is dead. Or the client calls and says - oops - I just changed all the South America contacts - meant to do the North America contacts - help!

With that kind of loss I've always been part of the surgical recovery team - restore the DB someplace else - find the data I want - move it back into database to fix the problem.

Even the one or two total failures resulted in lots of analysis of when the last image was done - how many transaction backups we had since then - how to recover the DB without a tremendous loss to the departments involved.

Then we have the bigger headache of logins and security and how that can become messed up during recovery operations.

So then, you mean to say that the backup process of the systems that you've worked on are automatically executed and cannot be accessed by the users, and that recovery of the system data is done by you guys?

backups and restores should be part of a Data Recovery plan, but that plan should be for the server itself, and not part of the application. Your best bet would be to use the builtin features of SQL Server, create a Maintenance Plan that does automatic backups of your system. At my last job, the application had high volume at times, so each hour we back up the transation log (so if the power goes out, at most only an hour is lost) and then the database did an incremental backup each night, followed by a full back up once a week. That weekly backup then goes off site into storage for 6 months.

It was a high volume, big money system. Odds are, you don't need something that rigourous. But still, you can create a weekly SQL Job that will automatically backup your database and the users would never know. Have it keep the last 4-6 backup files. then in the event of a failure, you have the backups from which you can restore your database. But backups are something that should be transparent to the users... especially in a multi-user environment. You need to have some kind of control in place so you know which one is the one you should be restoring from.

-tg

Quote:

---

Originally Posted by riechan

So then, you mean to say that the backup process of the systems that you've worked on are automatically executed and cannot be accessed by the users, and that recovery of the system data is done by you guys?

---

Yes.

If the user is invested in loading data - and you said there was a lot of data - wouldn't they want to minimize data loss in the event of failure?

I cannot imagine one of my users getting into my app one day - getting a "disk i/o failure" or "some evil sql error about checksums" and then simply saying "Let's restore from yesterday - see if that fixes the problem".

Quote:

---

Originally Posted by riechan

So then, you mean to say that the backup process of the systems that you've worked on are automatically executed and cannot be accessed by the users, and that recovery of the system data is done by you guys?

---

Exactly. Which is as it should be. the DBAs/IT should have full control over that. Not the users.

-tg

@tg: Okay, I get your point. Question though, would I need a specific version of SQL Server for me to be able to create a maintenance plan, or can I simply use SQL Server Express (or Management Studio Express)? And if by any chance, know any site which explains how to create a maintenance plan in SQL Server?

Quote:

---

Originally Posted by szlamany

I cannot imagine one of my users getting into my app one day - getting a "disk i/o failure" or "some evil sql error about checksums" and then simply saying "Let's restore from yesterday - see if that fixes the problem".

---

@szlamany: That's a huge concern of mine as well. But, since this is my first time incorporating backing-up of data into a system I made, I thought that it would be necessary to have the backup feature accessible via the program itself. I'm still new to this, and I didn't really know about SQL Server having automated backups. Thanks to both of you for the heads-up!

Quote:

---

Originally Posted by riechan

@tg: Okay, I get your point. Question though, would I need a specific version of SQL Server for me to be able to create a maintenance plan, or can I simply use SQL Server Express (or Management Studio Express)? And if by any chance, know any site which explains how to create a maintenance plan in SQL Server?

---

I have both EXPRESS and full-blown SQL running on this laptop here.

EXPRESS does not have any of the maintenance plan options that you need - here's a screen shot showing the differences in the object browsers...

@szlamany: Okay... so that means I'm required to get a licensed version of SQL for the Maintenance feature, huh? Makes you want to switch to MySQL. :D

All the maintenance plan does is automate some scripts and stored procedures - you could do all that with the WINDOWS SCHEDULER on the server itself.

Full blown SQL gets you the AGENT -running as a service - that's just a SQL-specific scheduler.

But again - it's just automating simple scripts.

Okay, so then that sort of solves the automated backup part. And I guess restoring the data should be done by us as well. What if, let's say, the database server unit is located in the office that will use the units themselves and we aren't (and can't be) physically be in there to do the maintenance (restoring of data, etc.). How will the users be able to restore the data from the automatic backups then?

Another thing, is it possible to restore from backups using SQL Management Studio Express?

We use BACKUP and RESTORE to move databases around all the time - from our production customer servers to our development servers and workstations here at my office.

SQL scripts look like this

Code:

---

/*
BACKUP DATABASE Stufiles
 TO DISK = 'C:\acs desktop\Stufiles_Feb24_Lap.bak'*/
RESTORE FILELISTONLY
  FROM DISK = 'd:\steve.bak'
RESTORE DATABASE Stufiles
  FROM DISK = 'd:\steve.bak'
  WITH MOVE 'Stufiles_data' TO 'D:\SQL data\Stufiles.mdf',
  MOVE 'Stufiles_log' TO 'D:\SQL Logs\Stufiles.ldf'
GO

---

The BACKUP is commented out above.

RESTORE FILELISTONLY dumps the contents of the BACKUP saveset.

The RESTORE DATABASE actually does the job.

This script can be run manually in MANAGEMENT STUDIO EXPRESS - yes...

But you already know this stuff - but did you look deeply into how RESTORE works when the DATABASE already exists on the server? MSDN BOOKS ONLINE has lots on info on BACKUP and RESTORE best practices - no reasons to discuss them here one topic at a time...

At any rate - make one of these .SQL scripts when you do your automated backups from your code. Make the script smart enough to do the job of restoring the database. Save it as a text file ending in .SQL - just like you would from a query window in Management studio.

But add to that the creation of a .BAT file that uses this file.

Here's a RUN_SPROCS.BAT file I have laying around that does this type of stuff

Code:

---

osql /E /S xxx.xx.xx.130 /i frmAddDrop_Inquire.sql /o log_frmAddDrop_Inquire.sql.txt
osql /E /S xxx.xx.xx.130 /i frmAppt_Inquire.sql /o log_frmAppt_Inquire.sql.txt
osql /E /S xxx.xx.xx.130 /i frmAppt_View_Student.sql /o log_frmAppt_View_Student.sql.txt

---

@szlamany: Thanks a lot for the informative heads-up, and for the script as well!