Friday, March 9, 2012

Microsoft.SqlServer.Management.Smo Backup and Restore classes

Hi,

Hopefully someone out there will be able to help me with this question. I'm trying to setup my C# program to use the Backup and Restore classes to perform backups and restores on an SQL Express 2005 database.

From the documentation I've read... it seems that when you go to restore the database, it's a good idea to backup the current transaction log. But when I do this using the Backup.SqlBackup method I get an error saying that no database backup is detected so a log backup can't occur. I'm not sure if this has something to do with the fact that I moved my backup files from the default SQL Express/Server Backup folder to a different location. Shouldn't it know I've already performed a backup though?

And does anyone know how to set a parameter to backup to a different file location than the default? I've been doing a backup then a file move. Works the same, but it would be nice to have everything together in the Backup object.

Thanks!

Without full backup transaction log backup is useless. After processing full backup database is marked, so server allows log backups. No any search of backup files, of course.

create database [dummy];
go

-- here will be an error because there is no full backup
backup log [dummy] to disk = 'delete.it.bak'
go

-- this works fine
backup database [dummy] to disk = 'delete.it.bak'
exec xp_cmdshell 'move d:\databases\mssql.1\mssql\backup\delete.it.bak d:\databases\mssql.1\mssql\backup\dont.delete.it.bak'
backup log [dummy] to disk = 'delete.it.bak'
go

drop database [dummy]

To backup file in nondefault directory you should simply specify full path in backup device name. But make sure that SQL Server service account have access to such dir.

WBR, Evergray
--
Words mean nothing...

|||I want to backup to nondefault directory, so I tried

USE master
EXEC sp_addumpdevice 'disk', 'testing_Backup',
'C:\testing.Bak'

BACKUP DATABASE testDB TO testing_Backup

and received the following error message:

Cannot open backup device 'testing_Backup(C:\testing.Bak)'. Operating system error 5(Access is denied.).

I suppose this is due to insufficient access right of SQL Server service account, but what shall I do with it?

|||Windows Vista doesn't allow you to write in root (c:\).
Try it on d:\

No comments:

Post a Comment