Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I need to make a backup of a client's SQL 2000 database, however there are a few issues preventing me from doing so using the traditional methods. I've tried using SQL Management Studio Express, but the host doesn't give sufficient privileges to create a backup and I'm getting some strange error messages. I've also tried doing the "Generate Scripts" to recreate the schema, then using the DTS Wizard to migrate the data, but the IDs set up with the identity specification property are not consistent with the live database once copied over. This results in some foreign key breakage...

If I remember correctly, I was able to use Microsoft SQL 2000 Enterprise Manager to perform the task before, but I can't find this anywhere... it seems Microsoft has pulled most SQL Server 2000 stuff from their site.

Does anyone know where I can find a copy of Enterprise Manager (or a trial of SQL Server 2000, which I believe comes with the component)? Or conversely, does anyone know of any other tools (preferably non-commercial) that are capable of mirroring remote SQL Server 2000 DBs?

Thanks!

share|improve this question

2 Answers 2

The Microsoft download center has service packs available, not sure if they will allow you to install the client tools directly from the SP installer though.

share|improve this answer

Find and use the command-line client "osql.exe" to connect to the instance. This will allow you to run Transact SQL commands and came with sql2000 (and sql server 7.0 and sql server 6.5, IIRC). More recent versions of sql server provide a similar tool named "sqlcmd.exe". Either osql.exe or sqlcmd.exe should be there, whether or not the other 'client tools' were installed. At worst, you can search through the drive that the sql server binaries are installed on, using explorer or something like:

dir c:\osql.exe /s

Once you are connected to the instance using the CLI, just run a backup command. There are many examples given online. Microsoft no longer provides documentation for SQL Server 2000 online, but the documentation for SQL Server 2005 is here. As long as you keep the command simple and avoid new features (like backup compression), the syntax for the backup command (and the restore command) hasn't changed much between 2000 and 2005 (or 2012, for that matter). The simplest backup for a sql2000 instance would look something like this:

 backup database my_databasename to disk = 'c:\backups\my_databasename.bak' with init
go

You need the "go" or the command won't run. Make sure that the path exists and that the login that the SQL Server service has permission to write there. You can use a UNC path for the backup file, but sometimes it's easier to backup locally, due to permissions/security/etc.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.