| Author |
 Topic  |
|
|
JaybeeSQL
Yak Posting Veteran
77 Posts |
 Posted - 07/23/2012 : 07:02:53
|
Hi all,
I use a couple of statements to run ad-hoc backups to the local drive with the greatest amount of free space
Exec xp_fixeddrives
Output:
C 5014
D 42398
H 679099
J 355112
K 232431
L 114248
M 4067
Q 2026
Gives me the drives and their free space, then
Backup database YourDB_Name_Here
To
Disk = 'H:YourDB_Name_Here.bak'
Exec xp_cmdshell 'del H:YourDB_Name_Here.bak'
Allows me to perform the backup (don't worry about why I need the delete, I just do.. :) )
Now instead of me changing the drive letter/db name manually, I'd like to create a couple of variables for the task - am I on the right lines? Not sure how to finish it off...
Declare @DBName (varchar)
Declare @DriveLetter (varchar)
Not sure how to finish it off...
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/23/2012 : 08:26:12
|
declare @sql varchar(8000),@DBName varchar(100), @DriveLetter char(1)
select @DBName='YourDB_Name_Here.bak', @DriveLetter ='H'
set @sql='Del '+@DriveLetter +':\'+@DBName
Exec xp_cmdshell @sql
Madhivanan
Failing to plan is Planning to fail |
 |
|
|
robvolk
Most Valuable Yak
USA
15558 Posts |
Posted - 07/23/2012 : 09:34:39
|
quote:
don't worry about why I need the delete, I just do..
No, you don't. You can back up to the same path and specify WITH INIT, that will overwrite the existing file.
Also, you should use a specific drive for backups instead of choosing the one with the most free space. Be consistent. If you don't have enough space on a single drive, get larger drives. If that's also not an option, dedicate a drive for backup files and another for archive storage. Use backup compression or Winzip/WinRar to compress backups afterward, and move them to the archive drive(s). |
|
|
|
JaybeeSQL
Yak Posting Veteran
77 Posts |
Posted - 07/23/2012 : 10:40:30
|
quote:
Originally posted by madhivanan
declare @sql varchar(8000),@DBName varchar(100), @DriveLetter char(1)
select @DBName='YourDB_Name_Here.bak', @DriveLetter ='H'
set @sql='Del '+@DriveLetter +':\'+@DBName
Exec xp_cmdshell @sql
Madhivanan
Failing to plan is Planning to fail
Hi Madhi,
Your code was a start, here's what I've expanded it to, but I still need a way to set @DriveLetter to the drive letter within the output of Exec xp_fixeddrives with the greatest free space....
--REMEMBER TO INSERT DATABASE NAME FIRST BELOW
--Declare all variables;
Declare
@sqlbackup varchar(8000),
@sqldeletebackup varchar(8000),
@DBName varchar(100),
@DriveLetter char(1)
--Obtain drive sizes;
Use Master
Exec XP_Fixeddrives
Go
--Begin backup, INSERT DATABASE NAME HERE BEFORE RUNNING CODE;
select @DBName='YourDB_Name_Here.bak', @DriveLetter ='?????'
set @sqlbackup ='Backup Database' +@DBName + 'to Disk =', +@DriveLetter +':\'+@DBName +'.bak'
Exec xp_cmdshell @sqlbackup
--Begin deletion of backup;
set @sqldeletebackup ='Del '+@DriveLetter +':\'+@DBName
Exec xp_cmdshell @sqldeletebackup
|
|
|
|
jeffw8713
Aged Yak Warrior
USA
693 Posts |
Posted - 07/23/2012 : 14:21:51
|
quote:
Originally posted by robvolk
quote:
don't worry about why I need the delete, I just do..
No, you don't. You can back up to the same path and specify WITH INIT, that will overwrite the existing file.
Also, you should use a specific drive for backups instead of choosing the one with the most free space. Be consistent. If you don't have enough space on a single drive, get larger drives. If that's also not an option, dedicate a drive for backup files and another for archive storage. Use backup compression or Winzip/WinRar to compress backups afterward, and move them to the archive drive(s).
I was going to respond with the same - but wanted to include the fact that by backing up to drives with the most free space you are going to cause performance issues.
Not to mention the fact that recovery options become much harder because you don't know which drive contains the current backup - and if you lost that drive you could lose the ability to restore the system without data loss. |
|
|
|
JaybeeSQL
Yak Posting Veteran
77 Posts |
Posted - 07/24/2012 : 04:37:04
|
Fellas, this backup is not for the usual purpose of data preservation, hence the subsequent delete. But Jeff I take your point on performance and you're right, I'll pick a drive. Sadly on one of our clusters our only choice is between the Data, Log and Log shipping drives. I would pick data as ours is relatively static, whereas we ship out every 15 mins, and of course data is written to/from the logs before being committed to disk, so presumably it's more sensible to choose data.
However, I'll need a loop wherein, if there is insufficient space for the backup, another drive is selected. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/24/2012 : 04:38:46
|
This will get you drive with more sapce available
declare @t table(drive char(1),free_space int)
insert into @t
Exec XP_Fixeddrives
select @DriveLetter = max(free_space) from @t
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
|
|
Dynamically changing drive letter/db name
