MS SQL Server Management Studio Express Question...

MrDarcy

Hi Folks,

I'm currently building an MS SQL 2005 DB, some core product data is built into the DB using a script on MS Server Management Studio Express.

However the plan is that the records in the DB can be updated on a continual basis by several staff members using an SQL UPDATE facility via a webpage on the site in an secure Admin area.

So obviously I should be backing up my server data but the thing is I don't have a script that will be up to date as many changes will be made on the fly by different users after the initial core DB, tables, SP's, Constraints, and some core data is created after the initial SQL script is run. Onbviously the SQL script is not much use after the thing is up and running and being edited/updated by several users all day long.

I'm wondering is it possible to "back up" the actual database somehow without needing this SQL script, so that if there is an issue down the road I have a fall back position at the end of each business day??? Maybe via MS Server Management Studio??? I've Googled and all I seem to be able to find is an reindexing approach which is running an SQL script that deletes the table and recreates it which isn't much use to be as per above???

Thanks in advance for any help...

fasty

From Management Studio Express can you right click on a database, go to tasks and select 'backup'?

NeverSayDie

To back up a database manually using SQL Server Management Studio, just right-click on the database in the left-hand pane, and select Tasks->Back up.

There's many different tools and techniques for dealing with automated backups of databases, the SQL Server Agent service would be one way to set that up;
http://msdn.microsoft.com/en-us/library/ms189237.aspx

Edit; beaten to the punch...

MrDarcy

Thanks a mil folks, will give it a shot...

amen

There are a few things you should do.
1: Look at MS SQL Server Database backup. This lets you back up the entire database including the data, tables, sps etc. This can then be restored if required. The problem with this though is if you only back up at the end of the day then you could loose a days worth of data i.e backup Monday night, users enter data tuesday morning and the database is lost tuesday afternoon you have to restore Mondays backup thus loosing the data enter Tuesday morning.

2:Backup up the transaction logs on a regular basis. This could be as frequent as every minute depending on the volume of transactions. These logs could then be backed up on a regular basis (every hour?) That way you only loose an hours worth of transactions.

If this is a work project see if they will send you on a SQL Server admin course.

If you have any more questions post away.
You should look at moving to SQL 2008.

amen

Should also say you should a regular restore off site to ensure the backups work.

While backing up the DB is simple you should have a proper back/restore strategy in place.

You may also need to backup the master DB depending on your setup.

MrDarcy

amen wrote: »

Should also say you should a regular restore off site to ensure the backups work.

While backing up the DB is simple you should have a proper back/restore strategy in place.

You may also need to backup the master DB depending on your setup.

Thank's amen for the advice. Is there a MS Server Management Studio Express available for MS SQL 2008???

I might check out a course but very close to launching this and time might not allow at the mo, (I know, these words will probably come back to haunt me!)..

ted1

save the following as a batch file, then use windows scheduler to schedule back ups. (paste it into notepad and save it as **.bat)
*********************************************
@echo off

ECHO Y | del c:\eft_*.*

REM ******************************************************************

REM The below uses sqlcmd to create an sql backup with a time stamp

REM ******************************************************************



echo

echo "Creating a backup file for a database"

echo





REM ******************************************************************

REM Sets the date_var to the date and time for adding to the filename

REM e.g. 20080623-1540300

REM ******************************************************************





set hh=%time:~0,2%



REM Since there is no leading zero for times before 10 am, have to put in

REM a zero when this is run before 10 am.



if "%time:~0,1%"==" " set hh=0%hh:~1,1%



set date_var=%date:~-4,4%%date:~-10,2%%date:~-7,2%-%hh%%time:~-8,2%%time:~-5,2%







REM **************************************************************************

REM This is the sqlcmd command

REM you must set the server name -S; and the backup path \ in the line below

REM **************************************************************************





sqlcmd -S InstanceNameHere -Q "BACKUP DATABASE databaseNameHere TO DISK = 'c:\DataBaseNameHere%date_var%.BAK'"



echo

echo "Completed backup file."

echo



REM PAUSE

REM *********************************************

REM you can set the log file name and path below

REM *********************************************



echo Completed backup file dbname%date_var%.bak >>d:\log.log

exit
*****************************************************



where it says c:/ you can change this to any file path, probably best replacing it with a mapped drive, that way if your HD goes you have back up stored in a different location

NeverSayDie

MrDarcy wrote: »

Thank's amen for the advice. Is there a MS Server Management Studio Express available for MS SQL 2008???

There is. It's worth using in any case, as it also works with SQL 2005 DBs, and it's a decent improvement over the 2005 one - better performance, UI improvements, new tools, etc. (Some of the heavier new tools will need SQL 2008 though - Intellisense, SQL debugging, etc).
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b&displaylang=en

ted1

Word of warning, if you open a 2005 database in 2008 you wil not be able to use it with 2005 ever again.

so if you move databases around, between machines make sure that you are ready to upgrade all machines to 2008.

theres free downloads available for 2008 studio manger, it requires you to uninstall 2005 studio manager first.

NeverSayDie

ted1 wrote: »

Word of warning, if you open a 2005 database in 2008 you wil not be able to use it with 2005 ever again.

so if you move databases around, between machines make sure that you are ready to upgrade all machines to 2008.

Hmm, I haven't come across that problem - we have people in here for quite a while using a mixture of Management Studio 2005 and 2008 to work on SQL 2005 DBs, with no problems. Does that only happen with the Express editions, or in particular situations?

Dr.Silly

I've never had that issue either .. anyway..

Remember,
for transaction log backups, the database needs to be in Full Recovery mode.
Also try and have your transaction logs on a separate drive to your data files.

This will improve performance, and also give you more redundancy in the case that your disk drive holding your data files crashes.

You should look up
Full Backups
Differential Backups
Transaction Log Backups

Backups are the easier part. It's been able to restore the database as fast as possible which is important, with as little downtime as possible, and with the most up to date data as possible.

have a look at log-shipping and database mirroring also.

Lots of people on here will be able to advise you if you've any questions.

amen

Word of warning, if you open a 2005 database in 2008 you wil not be able to use it with 2005 ever again

not true

if you are using the client tools to view and connect to a database then the database will not be converted.

On the other hand if you install SQL 2008 and restore a SQL2005 database then you can convert the SQL 2005 database to SQL 2008 format. Once this is done you can no longer load the converted database back into SQL 2005.

If you are converting from SQL 2005 to SQL 2008 you should be aware the some commands/syntx is not supported or marked for depreciation (which means it will not be available in some future versions.)

There is a free upgrade wizard you can run on a SQL2005 database which tells you what you should change before moving to SQL 2008.

If you are new to databases then you should look at indexs, locking, query plans etc to ensure your database is running effeciently.