2

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?

squillman
  • 37,883
  • 12
  • 92
  • 146
Mikael Grönfelt
  • 677
  • 3
  • 7
  • 14

2 Answers2

2

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
squillman
  • 37,883
  • 12
  • 92
  • 146
  • Thanks for your answer, I'm not very skilled in SQL, but managed to find out how to do it with TSQL just after I posted the question. However, I didn't create a logical device, I used DISK = "C:\Backup_location" instead and that seemed to work? Perhaps that's just another way of creating a logical device? Is SSMS able to restore from a backup created with TSQL in SQL 2000? – Mikael Grönfelt Sep 14 '11 at 17:08
  • Yes, `DISK=` should work just fine. SQL Server will restore backups made from previous versions of SQL Server, but not the other way around. That is, restoring your SQL 2000 backup to SQL 2005 will work just fine, but SQL 2000 will not restore a SQL 2005 backup. – squillman Sep 14 '11 at 17:16
0

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.

Amar
  • 1