March 14, 2012 at 9:27 pm
Hi All,
I am currently going through the internet and finding some solutions on backing up while using SQL Express. As Express doesn't have agent/maintenance plan I'm trying to create a similar process.
I've found some solutions and modified it to work in our environment, so full backup gets created and does a check if older than 3 days it will delete it therefore keeping only latest 3 backups at a time.
The above works successfully, but what im aiming for now is when backup is successful to insert the results into another Database/Table something as simple as the filename & date but i am having trouble with this and i can't get the results to be inserted.
Script to create the stored proc is below followed by the executing of the script.
ALTER PROCEDURE [dbo].[sp_BackupDatabase]
@databaseName sysname, @backupType CHAR(1)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','')
IF @backupType = 'F'
SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
' TO DISK = ''C:\Backup\' + @databaseName + '_Full_' + @dateTime + '.BAK'''
IF @backupType = 'D'
SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
' TO DISK = ''C:\Backup\' + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL'
IF @backupType = 'L'
SET @sqlCommand = 'BACKUP LOG ' + @databaseName +
' TO DISK = ''C:\Backup\' + @databaseName + '_Log_' + @dateTime + '.TRN'''
EXECUTE sp_executesql @sqlCommand
END
to execute the script im using the command
exec sp_BackupDatabase 'master', 'F'
exec sp_BackupDatabase 'model', 'F'
Now i want to insert the results into my new database "BACKUP" which has table called "EventLog" with 3 columns "ID, Backup & Logged"
Thats where im stuck and i can't get the results in there?
Any ideas please help???
Thanks
Tava
March 14, 2012 at 11:22 pm
to add the insert you just need to change each block of code like the one below. Also i dont think you need the dynamic SQL as i believe you can use variables for the file name and database name. just make sure to declare them when you start your SP
IF @backupType = 'F'
BEGIN -- Change to your original code
SET @File = '''C:\Backup\' + @databaseName + '_Full_' + @dateTime + '.BAK'''
BACKUP DATABASE @databaseName
TO DISK = @File
---the first if checks if there were errors. you could use an insert similar to the one below
--but insert the error into your backup table
IF @@Error <> 0
SELECT 'Some Error Text' AS ERROR
ELSE
INSERT INTO dbname.dbo.TableNAME (Col1,Col2) VALUES (Col1Value, Col2Value)
END
just copy the above for each of your if statements for the type of backup and change the insert and backup database command to match your needs. you may not need the lead and trail ' on the file name though. im not at my desktop to test 100%
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 15, 2012 at 1:35 am
Why are you not using msdb database. It will have all your backup information stored in the system tables.
select * from backupfile
select * from backupset
Check out these tables.
May 16, 2012 at 7:17 pm
Hi All,
Just realised i havent answered/closed this topic.
I ended up getting it working by using a Try/Catch statement and inserted it into the relevant table depending on the result. ended up being pretty simple.
Why i dont use the msdb backup information thats available - not really sure information i need is pretty much all there.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply