how to backup a MSSQL database?

[lordsmurf]

What do I need on my computer?
How do I connect and download a backup of the MS SQL database?

[crowmaine]

What do I need on my computer?

What are trying to do?

How do I connect and download a backup of the MS SQL database?

If you are trying to connect to a DB, then you usually need to provide a link (URL or file path) to the server to establish a path to the DB. You will also need a user name and password to log in. Most connections are programmed and use SQL to access the data.

If you are looking for a MS SQL program, SQL Server Express, a toned down version of MS SQL 2005, can be downloaded for FREE until Nov 6, 2006. It can handle up to 4GB of data.

MS even provides an eight video series,"SQL Server 2005 Express Edition for Beginners", to get you started.

SQL Express does not come with management and reporting tools, but you can find them here.

If you want to code, other VS2005 Studio Express products, it's free (until November 6, 2006) and it uses the new and improved .NET 2.0! It will take a little time to download, or get one of the MS 'Build a Program NOW!' series and the appropriate Express flavor will be on the CD. (A great way to learn some code!)

[lordsmurf]

One of the sites I work on moved to SQL a while back for the forum. Part of my "job" on the site is to maintain backups. It's hosted. In the past, I was able to grab Access files. This is clearly not that easy.

I really do not need to know anything beyond how to back it up. The code admin does backups too anytime he makes changes, but the day-to-day posts are not backed up regularly, not how I'd like them. Hence the questions.

I've asked questions of him already, but he is busy this time of year, so if I could get a head start on learning, it would help matters. Hence this post.

[TJohns]

A quick & dirty backup (taking the database offline temporarily) can be done from a command line:

1) Stop the database [check the service name...no SQL server here at home, sorry]
NET STOP MSSQLSERVER

2) copy the database elsewhere [check database folder location]
COPY C:\MSSQL\DATA\*.* C:\BACKUP

3) Restart the database
NET START MSSQLSERVER

If you use the Server Agent, stopping the database shuts down both. You then need to restart the MSSQLAGENT service [again, check exact name], which will auto-start the server first!

Trev

[RLT69]

lordsmurf,

This is probably the worst place to look for help on this topic. I would suggest an MS SQL forum.

If I understand your problem you want to perform periodic backup of an MS SQL Server database and you need to be able to do them remotely.

There are three things you will need:

1. Some version of the SQL Server Enterprise Manager program. crowmaine suggested this:

SQL Server Management Studio Express
http://msdn.microsoft.com/vstudio/express/sql/default.aspx

You want the management tool.

2. The IP address of the DBMS and the name of the database.

3. Authorization, a MS SQL Server user account that has access to the appropriate database with rights to backup that database.

The Management software allows you to perform backups of the database. Be warned: Unless you will have access to the location of the backup files they will remain on the server. Thus you will be able to perform a backup but will not have access to the files. If you require to have copies of the files, then you will need to work the Admin to obtain copies.

The management software should come with help files that give detailed information about performing a backup. They are very useful.

[ChrissyBoy]

It is unlikly that you will have permissions (in a hosted environment) to do a normal backup:



So what i do in such instances is have a DTS package, that is scheduled, which pulls the data every hour or two.



This data goes into a local SQL Server but it could also go into Access or any datastore.... Anyway it is a set & forget kind of approach...

[lordsmurf]

I think I have what I need at this point.

[RLT69]

TJohns wrote:

A quick & dirty backup (taking the database offline temporarily) can be done from a command line:

You're kidding right? This is an on-line forum he's backing up, unless the forum can be shutdown temporarily for backup purposes, this is a VERY bad idea. This also assume that lordsmurf has remote access to the database server via Terminal Server or RDP. If he doesn't then he cannot perform command line actions.

Using SQL Enterprise Manager is the better way to go. If you can install the software on your machine you can then access the database server remotely using SQL Enterprise Manager. The backups performed using SQL Enterprise Manager DO NOT take the database off-line. Thus the site can remain functional.

Lordsmurf I would suggest reading this article:

SQL Server 2000 Backup and Restore
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

It's very thorough and provides the information you require. If they are using SQL Server 2005, try this:

http://msdn.microsoft.com/sql/

And download the SQL Server 2005 Books Online. Apperantly it's a standalone copy and it will have information you require.


Is anybody here really a DBA or is it just your lunch hour?

[ofbarea]

Check this link: http://support.microsoft.com/kb/q241397/

With it you can automate the process using scripts.

But I would ISQL instead of OSQL.

[INFRATOM]

Make sure SQL service is not running when you backup. Also many SQL databases are password protected or encrypted so make sure you also backup registry and if it has a local data (inside the program's folder) backup that too.