Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
joanne
Starting Member
46 Posts |
 Posted - 2004-10-25 : 08:38:12
|
| Hi, I have a maintenance plan and implicit a daily backup scheduled at 22 PM.At 22:30 I want to take this backup (the last backup from device), to zip and to send the file to a second server, unzip and restore the file.My problem is that I don’t know how to put the last backup from the device into my variable.Any idea?Thanks for the help |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-10-25 : 08:47:57
|
| If you're using a single backup device that appends backups, you won't be able to extract an individual backup from it. The only way to do what you want is to back up to individual files and zip them. You can do it now with your current setup, but you'd have to:1) Restore the last backup to a separate database;2) Backup that database to a separate file;3) Zip that fileIt will add quite a bit of time to the process.If you REALLY want to do this the right way, get SQL LiteSpeed:http://www.imceda.com/It backs up and compresses at the same time, and is MUCH faster than regular SQL Server backups. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-10-25 : 08:54:18
|
| Get rid of the maintenance plan, schedukle the backup yourself - each one to a different file with a datetime stamp.Get a registered copy of winzip and download the command line utility and use it to zip the backup.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-25 : 08:58:02
|
| [code]SELECT TOP 1 BS.backup_finish_date, BMF.physical_device_nameFROM msdb.dbo.backupmediafamily BMF JOIN msdb.dbo.backupmediaset BMS ON BMF.media_set_id = BMS.media_set_id JOIN msdb.dbo.backupset BS ON BS.media_set_id = BMS.media_set_idWHERE BS.database_name = 'MyDatabaseName'ORDER BY BS.backup_finish_date DESC[/code]Kristen |
|
|
|
joanne
Starting Member
46 Posts |
Posted - 2004-10-26 : 07:33:36
|
| Hi, Thanks very much for your help.I will use robocopy command and I will not zip the file.But I have another question: how can I delete the backup files older then 7 days.Thanks again. |
|
|
|
Kristen
Test
22859 Posts |
|
|
|
|
|
how to zip the last backup file from a device