December 2, 2007 at 12:09 pm
I need to display a DataGridView of the existing backups that have been created for an SQL2005 database using SMO.
I can use T-SQL to get a dataset of this information, but I want to use SMO if possible.
Not sure, but I thought I could get a list of devices, but it always returns a count of 0.
In Studio, it displays the backups that can be restored, but I want to display the same info in my c# application using SMO.
Anyone know what I need to do ?
Thanks,
Jerry
December 2, 2007 at 12:37 pm
I don't and a few searches haven't turned up anything. Have you tried the backupdevicecollection.
Or maybe one of the restore classes? That might grab the data from the backuphistory tables.
December 2, 2007 at 2:01 pm
Yep, Tried both Backup and Restore classes, and no Devices are returned.
Certainly SMO should be able to do this without resorting to executing a T-SQL statement...
Jerry
December 3, 2007 at 7:37 am
I agree. I'll look around a bit more.
December 4, 2007 at 4:22 am
If you back up to disk, a rather simplistic way to do this is to use C# script to list the files in a specific directory that end with your backup extension (default is .bak and .trn). Store those names in an array and then list them in your viewer.
I don't know C#, so I can't give you the code to do it, but my opinion is if it can be done in DOS, then it can be done in a programming language. @=)
December 4, 2007 at 7:05 am
Thanks for the reply,
There are a number of problems with your approach in that backups can be named anything, and even the extension can be defaulted to something other than BAK. Also to look at the SQL server's drive from a remote pc takes a little SQL magic to get the files.
The purpose of viewing the files, is to get their LSN values so that you can do a restore to a point in time. I want the grid to look exactly like it does in the restore option in Studio.
private const string mediaSets = "select convert(bit,1) as [Restore], bs.Name,'Database' as Component,"
+ "case when bs.[type] = 'D' then 'Full' when bs.[type] = 'L' then 'Log' when bs.[type] = 'I' then 'Differential' "
+ "end as [Type], bs.server_name, bs.database_name,bs.Position,bs.first_lsn,bs.last_lsn,bs.Checkpoint_lsn,"
+ "case when bs.database_backup_lsn=0 then bs.first_lsn else bs.database_backup_lsn end as Full_lsn,"
+ "bs.backup_start_date as Start_Date,bs.backup_finish_date as Finish_Date,bs.backup_size as [Size],"
+ "bs.[user_name],bs.expiration_date from msdb.dbo.backupset bs where database_name='{0}'";
Above is a constant string that is later formatted to include the database name. This Query will
produce the same result as found in Studio. I had hoped that the SMO authors would have provided methods to pull this data instead of resorting to the use of T-SQL.
Thanks,
Jerry
December 4, 2007 at 7:26 am
Given 3rd party tools like Litespeed can do P.I.T. restores and they do it by accessing Microsoft .dlls, I would think that you can do it.
I'm not sure how to go about getting the LSNs, but when I used an SMO object to do backups & restores (using VB .Net), I was able to access the databases using the following objects:
Server
ServerConnection (the above two for the links to the servers)
Database (with an emumeration for the databases)
You could potentially use these objects to get into the proper database and then use the string you listed to get the LSNs. I know it's bulky, but at least you're doing it in your C# code.
December 4, 2007 at 7:28 am
Actually, how far have you gotten in your coding? Do you already have the connections set up? Or are you starting from scratch right now?
December 4, 2007 at 10:02 pm
Currently I have the application doing Full restores, and synchronizing the User SID, etc when it comes from a different server.
I am now going after partial (PIT) restores. I have the Grid looking just like that in Studio including colors and fonts, and oh yes.. the data including LSNs. But... I had to use SMO Execute with Query Results to get it. I hoped that SMO had features that would allow me to do it without running a query.
It has a ton of everything else...
Next I am going after Tape and Pipe devices.
This project was originally built in Delphi using SQLDMO, and I have converted it to C# using SMO. I am extending it to handle all of the normal stuff done in Studio, but will extend it further with scheduled batching, command line parameters, and restrict the restore to one or more objects (like specified tables, sprocs, etc).
If I get time, I will have it perform a discovery operation on a directory (local or remote) to determine If all the files with sequencial LSNs are present so that I and do a PIT from a set of files unknown to the target SQL server.
Thanks,
Jerry
December 6, 2007 at 8:39 am
Try looking at the msdb.dbo.backup___ tables. They will have the name of every backup file written, the first & last LSNs.
The backup history in the tables should go back much farther than what is still available online unless you have infinite disk space, so you still have to check to see what hasn't been deleted.
This might help:
CREATE TABLE #Files (FileName varchar(500))
INSERT INTO #Files
EXEC xp_cmdshell 'dir "{backup root folder}" /s /b'
Then do a join between #Files.FileName and msdb.dbo.backupfile.physical_name to list all the available backup files.
December 6, 2007 at 11:03 am
Have you tried the BackupDeviceList object yet?
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.aspx
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply