ninel
Active member
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...
"[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. "
I use the follwoing code to backup and restore:
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
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...
"[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. "
I use the follwoing code to backup and restore:
VB.NET:
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
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