Home / server / Questions / 311343
Accepted
Mikael Grönfelt
Asked: 2011-09-15 06:40:47 +0800 CST

Backup MS SQL 2000 db from SQL Management Studio 2005 on another server?

  • 772

The situation: I have an instance of MS SQL 2000 running on one server. On another server I've installed SQL Management Studio Express 2005. I can access the SQL 2000 server from Management Studio. I try to run a backup of a database, but when I try to add a backup destination I get the following error:

TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Property BackupDirectory is not available for Settings        
'Microsoft.SqlServer.Management.Smo.Settings'. This property may not exist for this             
object, or may not be retrievable due to insufficient access rights.  
(Microsoft.SqlServer.Express.Smo)

Any ideas on how to fix this?

sql-server ssms

2 Answers

  1. Best Answer

    You'll want to do this either through SQL 2000 Enterprise Manager or through TSQL. SSMS uses a newer version of the SMO classes to communicate with SQL. SQL 2000 has different backup requirements that SSMS 2005 knows about.

    Here's the TSQL reference for the SQL 2000 BACKUP statement.

    Remember in SQL 2000 that you need to create a dump device (explained in the article) for the backup to write to.

    The example from the article:

    -- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1', 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat'

-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1
    • 2

  2. I also got the same error while restoring a local copy of SQL Server database.To get rid of this error you need to create a string value and key field in the registry.Name the string field as BackupDirectory and in the value field write the path of the backup directory. You need to create this registry entry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server. For detailed information with screenshots refer this guide.

    • 0