Backup MSDE databse

[ManicCW]

Hi, does anyone knows how to backup databse from .net app? I need to have form where user will enter some parameters and click to backup database. It would be also good if the user could set job that automaticaly backup database at specific time.

Thank you

 

[kulrom]

MSDE, like sql server, has all the tools to backup build into it, using the Backup T-sql statement. In MSDE, that means adding a job to MSDE from your program, or from osql, or calling osql with the backup statement directly.
The scheduled job would most likely suit you best.
Both method are described in this MS article

Cheers

zapravo, bok

 

[ManicCW]

yes but i want to create everything using stored procedure. i have created sp for backup but how do i add job here.


SP:

CREATE PROCEDURE [dbo].[BackupBaze]
@putanja nvarchar(255) = 'C:\PZO.bak'
AS
BACKUP DATABASE [PZO] TO DISK = @putanja
WITH INIT , NOUNLOAD ,
NAME = N'BackupBaze',
NOSKIP , STATS = 10, NOFORMAT
GO

 

[kulrom]

Well, what' wrong with this SP? it seems fine to me on first sight.

have you tried to execute certain SP through OSQL. Run the SP from osql and confirm that the SP works: exec your_SP_NAME

if you ran that sql by itself and it's okay it should run from the SP as long as you give it the same parameters etc. for path

btw, you also might have to set permission for the SP: grant execute on MY_SP to public

If you find some bug/s there the steps to debugging that are:

1. run the sql directly from osql or some other tool - replace the parameter with a hardcoded path

2. run the SP by itself directly - as I said you can do it with osql by saying "exec my_Sp_bane myarg" using the same path you used when you ran the sql directly
also note that you can run the stored procedure directly from VS.NET using Server Explorer

3. Then make sure that you have granted execute to public, which I told you how to do and that it's not just some stupid permissions problem

4. Then make sure it runs from the application

If you are pretty sure your application is connecting to the DB okay and it still doesn't work and you don't see an exception displayed for some reason put an try/catch around the execution of the SP and then look at the exception in the debugger ... then post the message here

Cheers

 

[ManicCW]

This sp is working fine. I just need to incorporate job in this code.

 

[kulrom]

"sp_add_job"; "sp_add_jobstep"; "sp_add_jobserver"; "sp_start_job"; "sp_add_jobschedule";

Cheers


Preuzmi ovu online knjigu koja se nalazi na dnu ove strane
ili
http://download.microsoft.com/download/SQL70/File/2/Win98/En-US/sqlbol.exe

 

[ManicCW]

Thank you everybody but I found solution. It's nice. I will post it after detailed testing.

 

[uniquegodwin]

Hey Kurlom...
On that MSDN link you gave above..
I got this..

OSQL -Usa -PmyPasword -n -Q "BACKUP DATABASE msdb TO DISK = 'c:\msdb.dat_bak'"

How do I restore database in the SAME way? What would be the Syntax? Please help...Thank you

 

[ManicCW]

Create backup job

Here is how you create backup job depending on user input:

First create stored procedure:

[SIZE=2][COLOR=#0000ff]ALTER PROCEDURE [/COLOR][/SIZE][SIZE=2]dbo.sp_backup_posao
@Putanja [/SIZE][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][SIZE=2](250),
@Baza [/SIZE][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][SIZE=2](50),
@Server [/SIZE][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][SIZE=2](50),
@Vrijeme [/SIZE][SIZE=2][COLOR=#0000ff]int[/COLOR][/SIZE][SIZE=2],
@Interval [/SIZE][SIZE=2][COLOR=#0000ff]int[/COLOR][/SIZE][SIZE=2],
@Tip [/SIZE][SIZE=2][COLOR=#0000ff]int
AS
DECLARE [/COLOR][/SIZE][SIZE=2]@Ponavljanje [/SIZE][SIZE=2][COLOR=#0000ff]int
IF [/COLOR][/SIZE][SIZE=2](@Tip = 4) [/SIZE][SIZE=2][COLOR=#0000ff]SET [/COLOR][/SIZE][SIZE=2]@Ponavljanje = 0
[/SIZE][SIZE=2][COLOR=#0000ff]IF [/COLOR][/SIZE][SIZE=2](@Tip = 8) [/SIZE][SIZE=2][COLOR=#0000ff]SET [/COLOR][/SIZE][SIZE=2]@Ponavljanje = 1
[/SIZE][SIZE=2][COLOR=#0000ff]BEGIN TRANSACTION [/COLOR][/SIZE][SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]DECLARE [/COLOR][/SIZE][SIZE=2]@cmdBackup [/SIZE][SIZE=2][COLOR=#0000ff]nvarchar[/COLOR][/SIZE][SIZE=2](500)
[/SIZE][SIZE=2][COLOR=#0000ff]DECLARE [/COLOR][/SIZE][SIZE=2]@JobID [/SIZE][SIZE=2][COLOR=#0000ff]BINARY[/COLOR][/SIZE][SIZE=2](16) 
[/SIZE][SIZE=2][COLOR=#0000ff]DECLARE [/COLOR][/SIZE][SIZE=2]@ReturnCode [/SIZE][SIZE=2][COLOR=#0000ff]INT [/COLOR][/SIZE][SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][SIZE=2]@ReturnCode = 0 
[/SIZE][SIZE=2][COLOR=#0000ff]IF [/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff]SELECT COUNT[/COLOR][/SIZE][SIZE=2](*) [/SIZE][SIZE=2][COLOR=#0000ff]FROM [/COLOR][/SIZE][SIZE=2]msdb.dbo.syscategories [/SIZE][SIZE=2][COLOR=#0000ff]WHERE name [/COLOR][/SIZE][SIZE=2]= N'Database Maintenance') < 1 
[/SIZE][SIZE=2][COLOR=#0000ff]EXECUTE [/COLOR][/SIZE][SIZE=2]msdb.dbo.sp_add_category @name = N'Database Maintenance'
[/SIZE][SIZE=2][COLOR=#008000]-- Delete the job with the same name (if it exists)
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][SIZE=2]@JobID = job_id 
[/SIZE][SIZE=2][COLOR=#0000ff]FROM [/COLOR][/SIZE][SIZE=2]msdb.dbo.sysjobs 
[/SIZE][SIZE=2][COLOR=#0000ff]WHERE [/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff]name [/COLOR][/SIZE][SIZE=2]= N'Backup') 
[/SIZE][SIZE=2][COLOR=#0000ff]IF [/COLOR][/SIZE][SIZE=2](@JobID [/SIZE][SIZE=2][COLOR=#0000ff]IS NOT NULL[/COLOR][/SIZE][SIZE=2]) 
[/SIZE][SIZE=2][COLOR=#0000ff]BEGIN [/COLOR][/SIZE][SIZE=2]
[/SIZE][SIZE=2][COLOR=#008000]-- Check if the job is a multi-server job 
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]IF [/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff]EXISTS [/COLOR][/SIZE][SIZE=2]([/SIZE][SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][SIZE=2]* 
[/SIZE][SIZE=2][COLOR=#0000ff]FROM [/COLOR][/SIZE][SIZE=2]msdb.dbo.sysjobservers 
[/SIZE][SIZE=2][COLOR=#0000ff]WHERE [/COLOR][/SIZE][SIZE=2](job_id = @JobID) [/SIZE][SIZE=2][COLOR=#0000ff]AND [/COLOR][/SIZE][SIZE=2](server_id <> 0))) 
[/SIZE][SIZE=2][COLOR=#0000ff]BEGIN 
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]-- There is, so abort the script 
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]RAISERROR [/COLOR][/SIZE][SIZE=2](N'Unable to import job ''Backup'' since there is already a multi-server job with this name.', 16, 1) 
[/SIZE][SIZE=2][COLOR=#0000ff]IF [/COLOR][/SIZE][SIZE=2](@@TRANCOUNT > 0) [/SIZE][SIZE=2][COLOR=#0000ff]ROLLBACK TRANSACTION 
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]END 
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]ELSE 
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]-- Delete the [local] job 
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]EXECUTE [/COLOR][/SIZE][SIZE=2]msdb.dbo.sp_delete_job @job_name = N'Backup' 
[/SIZE][SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][SIZE=2]@JobID = [/SIZE][SIZE=2][COLOR=#0000ff]NULL
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]END 
BEGIN 
SET [/COLOR][/SIZE][SIZE=2]@cmdBackup=N'BACKUP DATABASE [' + @Baza + '] TO DISK = N''' + @Putanja +
''' WITH INIT , NOUNLOAD , NAME = N''BackupBaze'', 
NOSKIP , STATS = 10, NOFORMAT'
[/SIZE][SIZE=2][COLOR=#008000]-- Add the job
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]EXECUTE [/COLOR][/SIZE][SIZE=2]@ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID [/SIZE][SIZE=2][COLOR=#0000ff]OUTPUT [/COLOR][/SIZE][SIZE=2], @job_name = N'Backup', @owner_login_name = N'sa', @description = N'Vrsi backup baze u odredjeno vrijeme.', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
[/SIZE][SIZE=2][COLOR=#0000ff]IF [/COLOR][/SIZE][SIZE=2](@@ERROR <> 0 [/SIZE][SIZE=2][COLOR=#0000ff]OR [/COLOR][/SIZE][SIZE=2]@ReturnCode <> 0) [/SIZE][SIZE=2][COLOR=#0000ff]IF [/COLOR][/SIZE][SIZE=2](@@TRANCOUNT > 0) [/SIZE][SIZE=2][COLOR=#0000ff]ROLLBACK TRANSACTION
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]-- Add the job steps
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]EXECUTE [/COLOR][/SIZE][SIZE=2]@ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Backup', @command = @cmdBackup, 
@database_name = @Baza, @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
[/SIZE][SIZE=2][COLOR=#0000ff]IF [/COLOR][/SIZE][SIZE=2](@@ERROR <> 0 [/SIZE][SIZE=2][COLOR=#0000ff]OR [/COLOR][/SIZE][SIZE=2]@ReturnCode <> 0) [/SIZE][SIZE=2][COLOR=#0000ff]IF [/COLOR][/SIZE][SIZE=2](@@TRANCOUNT > 0) [/SIZE][SIZE=2][COLOR=#0000ff]ROLLBACK TRANSACTION
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]EXECUTE [/COLOR][/SIZE][SIZE=2]@ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 
[/SIZE][SIZE=2][COLOR=#0000ff]IF [/COLOR][/SIZE][SIZE=2](@@ERROR <> 0 [/SIZE][SIZE=2][COLOR=#0000ff]OR [/COLOR][/SIZE][SIZE=2]@ReturnCode <> 0) [/SIZE][SIZE=2][COLOR=#0000ff]IF [/COLOR][/SIZE][SIZE=2](@@TRANCOUNT > 0) [/SIZE][SIZE=2][COLOR=#0000ff]ROLLBACK TRANSACTION
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]-- Add the job schedules
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]EXECUTE [/COLOR][/SIZE][SIZE=2]@ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'BackupSchedule', @enabled = 1, @freq_type = @Tip, @active_start_date = 20050101, @active_start_time = @Vrijeme, @freq_interval = @Interval, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = @Ponavljanje, @active_end_date = 99991231, @active_end_time = 235959
[/SIZE][SIZE=2][COLOR=#0000ff]IF [/COLOR][/SIZE][SIZE=2](@@ERROR <> 0 [/SIZE][SIZE=2][COLOR=#0000ff]OR [/COLOR][/SIZE][SIZE=2]@ReturnCode <> 0) [/SIZE][SIZE=2][COLOR=#0000ff]IF [/COLOR][/SIZE][SIZE=2](@@TRANCOUNT > 0) [/SIZE][SIZE=2][COLOR=#0000ff]ROLLBACK TRANSACTION
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]-- Add the Target Servers
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]EXECUTE [/COLOR][/SIZE][SIZE=2]@ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = @Server
[/SIZE][SIZE=2][COLOR=#0000ff]IF [/COLOR][/SIZE][SIZE=2](@@ERROR <> 0 [/SIZE][SIZE=2][COLOR=#0000ff]OR [/COLOR][/SIZE][SIZE=2]@ReturnCode <> 0) [/SIZE][SIZE=2][COLOR=#0000ff]IF [/COLOR][/SIZE][SIZE=2](@@TRANCOUNT > 0) [/SIZE][SIZE=2][COLOR=#0000ff]ROLLBACK TRANSACTION
END
COMMIT TRANSACTION
[/COLOR][/SIZE]

Then use this code in form (in form there are input fields):

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Putanja [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = Application.StartupPath & "\Backup\Baza.bak"
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cm [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand("sp_backup_posao", cnPodaci)
cm.CommandType = CommandType.StoredProcedure
cm.Parameters.Add("@Putanja", Putanja)[/SIZE][SIZE=2]
cm.Parameters.Add("@Baza", DATABASENAME)
cm.Parameters.Add("@Server", SERVERNAME)
cm.Parameters.Add("@Vrijeme", [/SIZE][COLOR=black][SIZE=2]HOURS [/SIZE][SIZE=2]MINUTES[/SIZE][/COLOR][SIZE=2][COLOR=black] SECONDS)[/COLOR]
[/SIZE][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2]cm.Parameters.Add("@Interval", INTERVAL)
cm.Parameters.Add("@Tip[COLOR=black]", [/COLOR][/SIZE][SIZE=2][COLOR=black]TYPE(DAILY WEEKLY ...[/COLOR][/SIZE][SIZE=2])
cnPodaci.Open()
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]cm.ExecuteNonQuery()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]cnPodaci.Close()
[/SIZE]