Server 2003 - SQL 2000 Question

DeaconFrost

DeaconFrost

[H]F Junkie
Joined
Sep 6, 2007
Messages
11,586
I'm redoing my backup processes at work, and I want to take one final, manual snapshot of an SQL Database before I make any changes. I realize, that simply copying the files in question isn't going to work, because they are in use. My question is this: Is there a quick set up steps, or commands to shut down the SQL services, make a simply manual copy of the files, and then restart the server or services?

The three files I need are of the types .ldf, .mdf, and .ndf. This is a standard 2003 Server, and SQL Server 2000 Standard, if that matters.

Any suggestions? I know I can right-click on the SQL Server icon in the system tray, and choose either STOP or PAUSE. I'm worried about this corrupting the database though. No one is using this system, so is it safe to just use those options to stop the server, copy my files, and then start up the server again?
 
First recommendation, as k1pp3r mentioned, create a backup within SQL Server. You can backup databases while they are in use, but obviously during low use times is much better.

A second option is to "detach" the databases, then copy the database files. This is not the best way to back them up, but rather this is how you may want to move or store the databases when they are in a specific state. You should remove any SQL Server logins from a particular database before detaching it and it cannot be in use when you do this.

Stopping the SQL Server services and copying the database files will not necessarily produce "backups" you can use. In fact, I think it would be very problematic if not almost impossible to use database files that were backed up in this manner.
 
k1pp3r said:
Right click DB, All Tasks, Backup
Click to expand...

Just like the man said, make sure you do a Complete backup, you'll end up with one .bak file and that's all you should need to restore the database on another SQL 2000 server. You might think about upgrading to at least SQL 2005 while you're at it if that's feasible.
 
I know nothing about SQL 2000, so where would I do the "right-clicking"? Given that answer, once I choose backup, will I be given an option to select a complete backup?
 
In SQL Enterprise Manager expand the explorer down to the database you want then right click on it.
 
You must log in or register to reply here.
Back
Top