[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2216
  • Last Modified:

How do i change the Backup Folder Destination (SQL Server 2005)

Hi Guys,

I am doing an automatic backup of my databases, and they are being saved on C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup. Since i dont have enough space on the C drive, i want that they automatically go to a specified destination on another Hard Drive.

This is the code im using:

<--- Start--->

declare @cmd nvarchar(2000)
declare bcur cursor for

SELECT N'BACKUP DATABASE ' + name + ' TO DISK = '''+name+'-Data''
WITH NAME = N'''+name+'-Full Database Backup'', DESCRIPTION = N''Full Production Instance'', MEDIANAME = N'''+name+'Media'', MEDIADESCRIPTION = N'''+name+' Backup Media Set'', FORMAT, INIT'
FROM master..sysdatabases
WHERE name NOT LIKE 'TempDB'

open bcur

fetch next from bcur into @cmd

WHILE @@FETCH_STATUS = 0
begin
   exec sp_executesql @cmd
   fetch next from bcur into @cmd
end

close bcur
deallocate bcur

<--- End of Code--->
0
KenshiroM
Asked:
KenshiroM
  • 2
1 Solution
 
imran_fastCommented:
change your query to be
SELECT N'BACKUP DATABASE ' + name + ' TO DISK = ''d:\backup\'+name+'-Data''
WITH NAME = N'''+name+'-Full Database Backup'', DESCRIPTION = N''Full Production Instance'', MEDIANAME = N'''+name+'Media'', MEDIADESCRIPTION = N'''+name+' Backup Media Set'', FORMAT, INIT'
FROM master..sysdatabases
WHERE name NOT LIKE 'TempDB'
0
 
imran_fastCommented:
or you can change default sql backup location from the reqistry

Here's a registry key you can modify, however:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\BackupDirectory

0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now