backup database [modified]

[farokhian]

Hi all I use SQLEXPRESS and this is my connectionstring: @"Data Source=.\SQLEXPRESS;AttachDbFilename=" + Application.StartupPath + "\\Data\\mydatabase.mdf;" + "Integrated Security=True;User Instance=True"; and i use sqlconnection to connect to my database. and i execute my backup command like below: string backup = string.Format("backup database mydatabase TO DISK= '{0}'", addr); backup.ExecuteNonQuery(); but errormessage is : Could not locate entry in sysdatabases for database 'mydatabase'. No entry found with that name. Make sure that the name is entered correctly. BACKUP DATABASE is terminating abnormally. Changed database context to 'master'. please somebody help me.

modified on Monday, December 14, 2009 4:15 AM

[dan sh]

To backup and restore restore sql server, I would say make use of classes under SQLDMO namespace. Take a look at SQLServerClass class and the BackupClass class in that namespace. They offer required methods and properties.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

[farokhian]

thx. I want to know what is the required command for backup and restore? I want to add a utility to my application that when user need a backup from database or restore to one of his backup databases , my application enable to do it.

[Vimalsoft Pty Ltd]

There were previously a lot of Questions ask about this http://www.codeproject.com/Messages/3119251/Re-back-up-sql-data-programmetically.aspx[^] There are more just search the Forum for messages posted

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/

[farokhian]

I use SQLEXPRESS and this is my connectionstring: @"Data Source=.\SQLEXPRESS;AttachDbFilename=" + Application.StartupPath + "\\Data\\mydatabase.mdf;" + "Integrated Security=True;User Instance=True"; and i use sqlconnection to connect to my database. and i execute my backup command like below: string backup = string.Format("backup database mydatabase TO DISK= '{0}'", addr); backup.ExecuteNonQuery(); but errormessage is : Could not locate entry in sysdatabases for database 'mydatabase'. No entry found with that name. Make sure that the name is entered correctly. BACKUP DATABASE is terminating abnormally. Changed database context to 'master'. please somebody help me.

[J4amieC]

Repeating the same question as if you did not hear the response will not get you further response. except this one, of course!

[farokhian]

If you pay more attention i modified my question ! I want to know that can i backup from mydatabase in SQLEXPRESS by the code that i notice?

[Vimalsoft Pty Ltd]

Because there is no database with the name 'mydatabase' the syntax of the backup database is incorrect. Here are two Sp's use them For Backup

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- backup a database
ALTER PROCEDURE [dbo].[sp_BackupDatabase]

@databasename varchar(32),
@path varchar(256),
@filename varchar(64)

AS

set nocount on

declare @sql varchar(4000)

select @sql = 'BACKUP DATABASE ' + ltrim(rtrim( @databasename ))
select @sql = @sql + ' TO DISK = ''' + ltrim(rtrim(@path)) + ltrim(rtrim(@filename)) + ''' '
select @sql = @sql + ' WITH INIT'
--print @sql
execute ( @sql )

select 'Database successfully backed up!' [Result]

and later you might want to Restore and you can use this

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_RestoreDatabase]

@dbname char(32), 				-- the database name to restore as
@filename char(64), @path char(256)		-- the location of the backuped up database file	(on the SQL Server)

AS

set nocount on

declare @sql nvarchar(3000)

-- Restore the database
select @sql = ' RESTORE DATABASE ' + ltrim(rtrim( @dbname )) + ' FROM DISK = ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @filename )) + ''' '
select @sql = ltrim(rtrim(@sql)) + ' WITH '
select @sql = ltrim(rtrim(@sql)) + ' MOVE ''' + 'TNGoedit_Data' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '.mdf' + ''' , ' -- logical file name to physical name
select @sql = ltrim(rtrim(@sql)) + ' MOVE ''' + 'TNGoedit_Log' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '_log.ldf' + ''' ' -- logical file name to physical name
select @sql = ltrim(rtrim(@sql)) + ' ,REPLACE,RECOVERY;'
--select @sql = ltrim(rtrim(@sql)) + ' MOVE ''' + ltrim(rtrim(@dbname)) + '_Data' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '.mdf' + ''' , ' -- logical file name to physical name
--select @sql = ltrim(rtrim(@sql)) + ' MOVE ''' + ltrim(rtrim(@dbname)) + '_Log' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '_log.ldf' + ''' ' -- logical file name to physical name
print @sql
execute ( @sql )

-- Was the command successful or was there a problem
if ( (select @@Error) = 0 ) begin
-- Put an entry into oDirect.dbo.tbl_dbRef
-- execute ( 'sp_DataSet_Save ''' + @xml + ''' ' )

-- TODO: restore the users

select 'Restore Successful' [Result]
end
else begin
select 'Restore Unsuccessful' [Result]
end

Vuyi