Is this backup stored procedure okay? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

Is this backup stored procedure okay?

Author

Topic

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-09-16 : 21:53:25

Hi, I have the following sp for backing up my databases

CREATE Procedure s_Backup
@Path varchar(256) ,

as

DECLARE @pathToFile as varchar(256)
select @pathToFile = @Path + '\MyDataBase.bak'

BACKUP DATABASE MyDataBase TO DISK = @pathToFile WITH INIT ,
NOUNLOAD ,
NAME = 'MyDataBase BackUp',
NOSKIP ,
STATS = 10,
NOFORMAT

select @pathToFile = @Path + '\master.bak'

BACKUP DATABASE master TO DISK = @pathToFile WITH INIT ,
NOUNLOAD ,
NAME = 'Master BackUp',
NOSKIP ,
STATS = 10,
NOFORMAT

select @pathToFile = @Path + '\msdb.bak'

BACKUP DATABASE msdb TO DISK = @pathToFile WITH INIT ,
NOUNLOAD ,
NAME = 'Msdb BackUp',
NOSKIP ,
STATS = 10,
NOFORMAT

select @pathToFile = @Path + '\pubs.bak'

BACKUP DATABASE pubs TO DISK = @pathToFile WITH INIT ,
NOUNLOAD ,
NAME = 'Pubs BackUp',
NOSKIP ,
STATS = 10,
NOFORMAT

GO

Does anyone see anything wrong here?

Also, is there a way to show the current progress while this
procedure is run?

thanks

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-09-16 : 23:36:46

Have a look at Tara's backup procedures here:

http://weblogs.sqlteam.com/tarad

You can find the progress of a RESTORE by doing this:


DECLARE @spid INT

SELECT @spid = (SELECT TOP 1 spid FROM sysprocesses WHERE cmd LIKE 'RESTORE%')

DBCC OUTPUTBUFFER(@spid)

If you have used STATS in your proc, it will tell you what percent you're at. It will also print the percent out so you can look at in in QA, or capture it to an output file if running from a job.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-09-18 : 14:00:17

Hi, my backup procedure is small compared to tara's.
Am I doing something wrong here?

Also, when I ran hers, I get a timeout error. Does anyone
know why?

thanks

Kristen
Test

22859 Posts

Posted - 2004-09-19 : 01:34:10

"Am I doing something wrong here?"

IIRC Tara's is making a filename containing the date/time, so that each backup file is unique. Also, Tara's is deleting files older than, say, 2 days. And it is backing up every database on the server - you routine will need modification each time you create a new database.

Dunno about the timeout ... perhaps it is trying to write to a disk drive that you don;t have? I assume you've changed the stuff that defines the path where backups should be stored?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources