Restoring database programatically.. error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Restoring database programatically.. error

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2006-08-07 : 11:49:28

I need to programmatically backup a "template" database and restore it under a different name. The template database just contains the structure with stored procs, but no data.

What I need to do is everytime we get a new project I need to backup the template db and restore it with the new project name. All this has to be done programmatically.

The backup of the template file works, but when I try to restore from the backup file I created I get the error...
[COLOR=Red]
"[Microsoft][ODBC SQL Server Driver][SQL Server]The file 'C:\Program Files\Microsoft SQL Server\MSSQL\data\VoiceNet_at0000.mdf' cannot be overwritten. It is being used by database 'Voicenet_Template'. [Microsoft][ODBC SQL Server Driver][SQL Server]File 'VoiceNet_Test' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\VoiceNet_at0000.mdf'. Use WITH MOVE to identify a valid location for the file. [Microsoft][ODBC SQL Server Driver][SQL Server]The file 'C:\Program Files\Microsoft SQL Server\MSSQL\data\VoiceNet_at0000_log.ldf' cannot be overwritten. It is being used by database 'Voicenet_Template'. [Microsoft][ODBC SQL Server Driver][SQL Server]File 'VoiceNet_Test_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL\data\VoiceNet_at0000_log.ldf'. Use WITH MOVE to identify a valid location for the file. [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE DATABASE is terminating abnormally. "[/COLOR]

I use the follwoing code to backup and restore:


Dim oSQLServer As New SQLDMO.SQLServer
Dim oBackup As New SQLDMO.Backup
Dim oRestore As New SQLDMO.Restore
Dim BACKUPFILE As String
Dim DATABASE As String

BACKUPFILE = "C:\VoicenetSQL\project\tampa\Politic\" & ProjectFolder & "\VoiceNet_TemplateBackup.bkp"
DATABASE = "VoiceNet_Template"

oSQLServer.Connect("NINEL-D246655F1", "timecontroluser", "timecontroluser")

oBackup.Files = BACKUPFILE
oBackup.Database = DATABASE
oBackup.BackupSetName = "VoiceNet"
oBackup.BackupSetDescription = "Backup from VB.NET application"
oBackup.SQLBackup(oSQLServer)

'Changing the name of the db to the new project
DATABASE = "VoiceNet_" & ProjectFolder
With oRestore
     .Files = BACKUPFILE
     .Database = DATABASE
     .ReplaceDatabase = True
     .SQLRestore(oSQLServer)
End With

I guess the log and ldf files are causing the problem.

Is there any way I can get around this issue and create the log and ldf files with the new project name instead trying to use the template files?

Am I using the wrong code to do this?
Any help would be greatly appreciated.

Thanks,
Ninel

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-08-07 : 14:13:06

You need to use the MOVE option to move the files to a new location / rename them. In T-SQL it looks like this:

RESTORE DATABASE MyDB
....
WITH MOVE 'MyLogicalFileName' TO 'C:\...\MyNewFileName.mdf'

I'm assuming it'll be something similar in DMO, but you'll have to interrogate the object model.
Clearly, in order to be able to do this programmatically , you need to know what logical files exist for a particular backup. You can use RESTORE FILELISTONLY to return this. The following link gives examples of doing this using DMO:
[url]http://www.sqlservercentral.com/columnists/awarren/restoringusingdmogettingfilelistandnorecovery.asp[/url]

Mark

ninel
Posting Yak Master

141 Posts

Posted - 2006-08-07 : 14:43:08

Ok..I figured out the following:

Logical name: VoiceNet_Test
Physical name:C:\Program Files\Microsoft SQL Server\MSSQL\data\VoiceNet_at0000.mdf

Logical name: VoiceNet_Test_log
Physical name:C:\Program Files\Microsoft SQL Server\MSSQL\data\VoiceNet_at0000_log.ldf

Can you please help me with the what I need to do with these filenames.
You mentioned using the WITH MOVE.

How do I set it up using the above filenames?

Thank you,
Ninel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-07 : 14:55:13

RESTORE DATABASE SomeDatabase
FROM DISK = 'F:\MSSQL\Backup\SomeBackupFile.BAK'
WITH REPLACE, MOVE 'SomeDatabase_Data' TO 'F:\MSSQL\DATA\SomeDatabase_Data.MDF',
MOVE 'SomeDatbase_Log' TO 'G:\MSSQL\Data\SomeDatabase_Log.LDF'

See BOL for more details.

Tara Kizer

ninel
Posting Yak Master

141 Posts

Posted - 2006-08-07 : 15:22:37

Let me ask this question....

I am using a database that was created that contains no data, just a structure. My company aquires new projects all the time. Every time we have a new project they want a new database for each new project. I have to create this programmatically.

Every time we need a new database, a program is run that backs up this template database and restores the database with a new project name.

Template DB has log and mdf files (template_log.ldf & template.mdf)

When the restore is occuring its trying to copy the log and mdf files and they already exist which is what is causing the error.

If I create a new folder and have the restore process drop the log and mdf files in the new location it works.

Does that mean I'm going to have as many different folders as projects with the same log and mdf files?
Is there some other way of doing this?

Thanks,
Ninel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-07 : 15:31:01

Just use different file names in the same folder. You can do this with the MOVE option.

Tara Kizer

ninel
Posting Yak Master

141 Posts

Posted - 2006-08-07 : 15:39:05

Thank you so much. It never occured to me that I can just change the filename. I guess that's what happens when you stare at the same issue continuously all day long.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources