SQL backup procedure from ASP.NET

[Guest]

I have a MS SQL database process that is run from ASP.NET.
I would like to be able to backup the SQL database using either
a full database or transaction log backup immediately before the
backup is done. Is this possible with ASP.NET and
what kind of permissions or role would the connection SQL connection account
need to perfom the backup. I would like to give the account the minimum
permissions needed.

 

[Scott Allen]

You can backup a database by sending T-SQL commands [1], i.e:

-- 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

You could execute the above with a SqlConnection and a ExecuteNonQuery
on SqlCommand.

Once the devices are setup, a login need only be in the
db_backupoperator role to use BACKUP. The diskadmin role is needed for
sp_adddumpdevice.


[1]
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp


HTH,

 

[Guest]

Scott -

Thank you for your reply.
--
Ed

You can backup a database by sending T-SQL commands [1], i.e:

-- 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

You could execute the above with a SqlConnection and a ExecuteNonQuery
on SqlCommand.

Once the devices are setup, a login need only be in the
db_backupoperator role to use BACKUP. The diskadmin role is needed for
sp_adddumpdevice.


[1]
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp


HTH,
--
Scott
http://www.OdeToCode.com/blogs/scott/

I have a MS SQL database process that is run from ASP.NET.
I would like to be able to backup the SQL database using either
a full database or transaction log backup immediately before the
backup is done. Is this possible with ASP.NET and
what kind of permissions or role would the connection SQL connection account
need to perfom the backup. I would like to give the account the minimum
permissions needed.

 

[Guest]

Scott -

I have one more question. If I want to add dump devices (files) but not have
them appear in Enterprise Manager, is there any way to do that.

Thanks,

- Ed B.

--
Ed

You can backup a database by sending T-SQL commands [1], i.e:

-- 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

You could execute the above with a SqlConnection and a ExecuteNonQuery
on SqlCommand.

Once the devices are setup, a login need only be in the
db_backupoperator role to use BACKUP. The diskadmin role is needed for
sp_adddumpdevice.


[1]
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp


HTH,
--
Scott
http://www.OdeToCode.com/blogs/scott/

I have a MS SQL database process that is run from ASP.NET.
I would like to be able to backup the SQL database using either
a full database or transaction log backup immediately before the
backup is done. Is this possible with ASP.NET and
what kind of permissions or role would the connection SQL connection account
need to perfom the backup. I would like to give the account the minimum
permissions needed.

 

[Scott Allen]

No - I don't know of a way to do that (but that doesn't mean it can't
be done). You might check the sql server ng.