Backing sql db's to a shared Drive 1

[kg00se]

I have several SQL Server that do not have enough room for creating a maintenance plan for backing them up.

I have a 1tb hard drive on another server and would like to back the databases to that drive. are there any scripts out there that would allow me to backup all databases on my sql server to another server?

thanks
kg00se

 

[NoCalAdmin]

not sure about scripts, but you can definately perform a flat file back up and direct it to a different server.

Tom

 

[JohnBates]

kgOOse,

Here's one. I didn't write the original version (I wish I could give them credit; don't remember where I got this)

I use this but we don't have to backup log files, so that may or may not work for you.

Remember to first create the table (the code that has been inactivated in this proc)

I hope it helps you. John

CREATE Procedure dbo.usp_BackupAllDatabases
@Path varchar(128) ,
@Type varchar(4) -- Full or Log (Backing up Logs has not been tested !)
as
/*
For each SQL Server database (except tempdb)......
Deletes old .BAK files and backs-up the database (only once)
NOTE: This procedure is designed to be ran only once per day -
if ran more than once, it will destroy the previous backup file for
that day and rebuild it - you will lose the earlier backup data
Backup file format:
DatabaseName_yyyymmdd.bak
DatabaseName__Log_yyyymmdd.bak
Usage example:
exec usp_BackupAllDatabases '\\Snap3\Share1\SQL1Backups\', 'Full'
*/

Truncate Table dbo.DatabaseBackup

/*Create table dbo.DatabaseBackup
(
Name varchar(128) primary key nonclustered ,
BackupFlagFull varchar(1) not null check (BackupFlagFull in ('Y','N')) ,
BackupFlagLog varchar(1) not null check (BackupFlagLog in ('Y','N')) ,
RetentionPeriodFull datetime not null ,
RetentionPeriodLog datetime not null
)
*/

set nocount on
declare @sql varchar(1000)

-- Get all database names from system tables
create table #DBName
(
ID int identity (1,1) ,
Name varchar(128) not null ,
RetentionPeriod datetime null
)

insert #DBName
(Name)
select name
from master..sysdatabases
Order by name

-- Include any new databases in the backup
insert DatabaseBackup
(
Name ,
BackupFlagFull ,
BackupFlagLog ,
RetentionPeriodFull ,
RetentionPeriodLog
)
select #DBName.Name ,
'Y' ,
'N' ,
--'3 jan 1900' , -- This is where you specify how many days to keep - this is 2 days **
'4 jan 1900' , -- This is where you specify how many days to keep - this is 3 days **
'1 jan 1900'
from #DBName
Where lower(#DBName.Name) <> 'tempdb'

create table #ExistingBackups
(
Name varchar(128) ,
ID int identity (1,1)
)

Truncate Table #DBName
-- loop through databases
declare @Name varchar(128) ,
@RetentionPeriod datetime ,
@LastBackupToKeep varchar(8) ,
@ID int ,
@MaxID int

insert #DBName
(Name, RetentionPeriod)
select Name, case when @Type = 'Full' then RetentionPeriodFull else RetentionPeriodLog end
from DatabaseBackup
where (@Type = 'Full' and BackupFlagFull = 'Y')
or (@Type = 'Log' and BackupFlagLog = 'Y')

select @MaxID = max(ID) ,
@ID = 0
from #DBName

--

while @ID < @MaxID
begin
-- get next database to backup
select @ID = min(ID) from #DBName where ID > @ID

select @Name = Name ,
@RetentionPeriod = RetentionPeriod
from #DBName
where ID = @ID

-- Delete old backup files
delete #ExistingBackups
select @sql = 'dir /B ' + @Path
select @sql = @sql + '"' + @Name + '_' + '*.*"'
insert #ExistingBackups exec master..xp_cmdshell @sql

if exists (select * from #ExistingBackups where Name like '%File Not Found%')
delete #ExistingBackups

select @LastBackupToKeep = convert(varchar(8),getdate() - @RetentionPeriod,112)

delete #ExistingBackups where Name > @Name + '_' + @LastBackupToKeep

declare @eID int ,
@eMaxID int ,
@eName varchar(128)

-- loop thru all the expired backups, deleting each one

select @eID = 0 ,
@eMaxID = coalesce(max(ID), 0)
from #ExistingBackups

while @eID < @eMaxID
begin
select @eID = min(ID) from #ExistingBackups where ID > @eID
select @eName = Name from #ExistingBackups where ID = @eID

select @sql = 'del ' + @Path + @eName
--PRINT @sql
exec master..xp_cmdshell @sql, no_output
end
delete #ExistingBackups

-- now backup the db
select @sql = @Path + @Name + '_' + convert(varchar(8),getdate(),112) + '.bak'
--PRINT 'Backup '
--PRINT @sql
if @Type = 'Full'
BEGIN
-- added WITH INIT to prevent data from appending to an existing file.
-- If a .BAK file already exists with that name, it will overwrite existing data
Backup database @Name to disk = @sql WITH INIT

END
else
Backup log @Name to disk = @sql
end
GO

 

[kg00se]

Thanks,
I think that will work great.

 

[kg00se]

ok I ran the stored proc and this is what I am getting now.

Msg 3201, Level 16, State 1, Procedure usp_BackupAllDatabases, Line 146
Cannot open backup device '\\igcnprodsql1\temp\Anthem_2006_20070126.bak'. Device error or device off-line. See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Procedure usp_BackupAllDatabases, Line 146
BACKUP DATABASE is terminating abnormally.

any thoughts on why this would be happening????

Thanks
kg00se

 

[JohnBates]

Based on the error you got, looks like the proc made it all the way to the Backup Database command near the end of proc.

- does this path exist ? \\igcnprodsql1\temp\

- is the igcnprodsql1 server or device on-line?


(Oh, I failed to mention that - because it uses xp_cmdshell, you will need to run this as 'sa' or as a user that has Sys Admin SQL Server permissions.)

Post back if you find the server is on-line but the proc still fails.

John

 

[kg00se]

the path does exist which is strange.
just so you know that is the local maching of the stored proc.
I am just sending it to a shared directory on the same server using unc paths.

I verified that this is running as sa.

any other thoughts?

 

[kg00se]

Also here is the event code that I receive every time that this fails. maybe this will help.
I am still trying to find anything that microsoft may have on this issue as well.

EVENT # 785
EVENT LOG Application
EVENT TYPE Error
SOURCE MSSQLSERVER
CATEGORY Server
EVENT ID 17055
COMPUTERNAME IGCN-DEVSQL1
TIME 1/26/2007 10:53:30 AM
MESSAGE 18204 :
BackupDiskFile::CreateMedia: Backup device '\\igcnprodsql1\temp\Berk_20070126.bak' failed to create. Operating system error = 5(Access is denied.).


BINARY DATA 0000: 1C 47 00 00 10 00 00 00 0D 00 00 00 49 00 47 00
0010: 43 00 4E 00 2D 00 44 00 45 00 56 00 53 00 51 00
0020: 4C 00 31 00 00 00 00 00 00 00

 

[kg00se]

John,
I figured it out.
I found this article on microsoft.

http://support.microsoft.com/kb/207187

worked wonders.

thanks for all your help.
kg00se

 

[JohnBates]

Thanks for the feedback kg00se. I'm happy that it worked for you.

'Twould be a good idea to test your ability to restore one of the databases from your new backup set. Just to be sure that, if you ever need to, it will work.

Happy Friday!

John