Dynamically changing drive letter/db name

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamically changing drive letter/db name

Author

Topic

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2012-07-23 : 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

22864 Posts

Posted - 2012-07-23 : 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

15732 Posts

Posted - 2012-07-23 : 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
Posting Yak Master

112 Posts

Posted - 2012-07-23 : 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

819 Posts

Posted - 2012-07-23 : 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
Posting Yak Master

112 Posts

Posted - 2012-07-24 : 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

22864 Posts

Posted - 2012-07-24 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources