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

[riechan]

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!

[Negative0]

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.

[riechan]

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...

[techgnome]

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

[riechan]

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

[GaryMazzone]

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)

[riechan]

It goes like this right?

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:

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?

[GaryMazzone]

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.

[riechan]

Just the MDF right?

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?

[GaryMazzone]

It's really not that hard to look up:

--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'

[si_the_geek]

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.

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.

[dee-u]

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.

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

[leinad31]

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.

[riechan]

@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...

[dee-u]

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

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

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

[riechan]

Okay, so I was able to backup my database using this 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:

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?

[GaryMazzone]

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

[riechan]

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

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:

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

[GaryMazzone]

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

[riechan]

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?

[riechan]

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

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

[szlamany]

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

Did that work for you?

[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:

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).

[szlamany]

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:

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?

[riechan]

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)

[szlamany]

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.

[riechan]

According to GaryMazzone,

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?

[szlamany]

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)?

[riechan]

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):

...
        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?

[szlamany]

cmd.connection = con

[riechan]

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):

        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")

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?

[szlamany]

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.

[riechan]

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?

[techgnome]

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

-tg

[szlamany]

@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!

[riechan]

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?

[szlamany]

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...

[riechan]

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.

[szlamany]

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.

[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?