sp to backup all databases - SQL Server Forums

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

sp to backup all databases

Author

Topic

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-01-23 : 15:23:27

I have this sp that backsup all our db and the master. I want the backup files to be put in D:\sqlbackups. I get a return message stating that all the files were successfully backed up but only the master.bak shows and the other db files are no where to be found?!?(Processed 2000 pages for database 'master', file 'master' on file 1.
Processed 1 pages for database 'master', file 'mastlog' on file 1.
BACKUP DATABASE successfully processed 2001 pages in 0.121 seconds (135.413 MB/sec).
Processed 828272 pages for database 'MARS_SYS', file 'MARS_SYS_Data' on file 1.
Processed 1 pages for database 'MARS_SYS', file 'MARS_SYS_Log' on file 1.
BACKUP DATABASE successfully processed 828273 pages in 84.552 seconds (80.248 MB/sec).
Processed 148736 pages for database 'MSystem', file 'MSystem_Data' on file 1.
Processed 1 pages for database 'MSystem', file 'MSystem_Log' on file 1.)

Here is the sp:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER Procedure dbo.sp_BackupAllDatabases

AS

Declare @strDatabaseName nvarchar(254)
Declare @strDatabaseLoc nvarchar(254)
Declare @Path nvarchar(255)

Declare BackupList Cursor For
Select name from sysdatabases
where dbid > 6 or dbid = 1

Open BackupList
Fetch Next From BackupList into @strDatabaseName

SET @Path = 'D:\SQLBACKUPS\' + @strDatabaseName + '.bak'

While @@Fetch_Status = 0
Begin
BACKUP DATABASE @strDatabaseName
to disk = @Path with Init, Skip;

Fetch Next From BackupList Into @strDatabaseName
End

CLOSE BackupList
DEALLOCATE BackupList

return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-01-23 : 16:21:08

I got it working. I had the set @path in the wrong location so it was only putting the first db the cursor came to in the folder. Here's the working sp for anyone who might need it:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER Procedure dbo.sp_BackupAllDatabases

AS

Declare @strDatabaseName nvarchar(254)
Declare @strDatabaseLoc nvarchar(254)
Declare @Path nvarchar(255)

Declare BackupList Cursor For
Select name from sysdatabases
where dbid > 6 or dbid = 1

Open BackupList
Fetch Next From BackupList into @strDatabaseName

While @@Fetch_Status = 0
Begin
SET @Path = 'D:\SQLBACKUPS\' + @strDatabaseName + '.bak'
BACKUP DATABASE @strDatabaseName
to disk = @Path with Init, Skip;

Fetch Next From BackupList Into @strDatabaseName
End

CLOSE BackupList
DEALLOCATE BackupList

return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-23 : 21:39:31

Does maintenance plan do that?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources