I have been tasked with creating a sql table that contains database backups.
The idea is to have all databases listed in rows that can be read by a script that will execute a backup for each row. I know SQL Server agent can do this but there are reasons that this needs to be in this format. In the following script I have created a table and populated it with an adventureworks database. The table is built so that the fields can be concatenated to form a Backupstatement(With some text added in).
If object_ID('Adventureworks.dbo.Db_Backups') is not null
Drop table Adventureworks.dbo.DB_Backups
Create Table Adventureworks.dbo.DB_Backups
database_Name varchar(30) Not null,
Backup_Freq varchar(10) Not null,
Backup_Type varChar(10) Not null,
Backup_To Varchar(300) Not null,
Format char(10) Not null,
Init_NoInit char(30) Not Null,
Backup_name Varchar(50) Not null,
Skip_NoSkip Char(6) Not Null,
rewind_Norewind Char(8) Not Null,
Nounload_unload Char(8) Not Null,
Stat_report Char(3) Not null)
Insert into Adventureworks.dbo.DB_Backups
'''C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Adventureworks_backup_2012_08_27_093633_3380858.bak''',
'''Adventureworks-Full Database Backup''',
Declare @StrSql varchar(1000)
Set @strsql =
'Backup Database' + ' ' + (Select Database_Name From Adventureworks.dbo.Db_Backups)
+ ' ' + 'To Disk' + ' ' + '=' + ' ' + (Select Backup_to From Adventureworks.dbo.Db_Backups)
+ ' ' + (Select Format From Adventureworks.dbo.Db_Backups) + ','
+ ' ' + (Select Init_NoInit From Adventureworks.dbo.db_Backups) + ','
+ ' ' + 'Name' + ' ' + '=' + ' ' + (Select Backup_name From Adventureworks.dbo.db_Backups) + ','
+ ' ' + (Select Skip_Noskip From Adventureworks.dbo.db_backups) + ','
+ ' ' + (Select rewind_Norewind From Adventureworks.dbo.db_Backups) + ','
+ ' ' + (Select Nounload_unload From Adventureworks.dbo.db_Backups) + ','
+ ' ' + 'Stats =' + ' ' + ' ' + (Select Stat_report From Adventureworks.dbo.db_backups)
I am not too experienced with stored procedures and don't know it that is the way to go. I also tried making one column that contained a complete backup statement, but when I tried to execute the command on that column I got the same results.
Any help is appreciated or alternative suggestions,