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

[techgnome]

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

[szlamany]

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

[techgnome]

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

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

[szlamany]

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

[riechan]

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

[szlamany]

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.

[riechan]

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?

[szlamany]

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

/*
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

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

[riechan]

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