Backup SQL 7.0 - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Backup SQL 7.0

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2003-10-30 : 17:58:15

I have about 85 DB's that need updating. They are all instances of the same DB. (bad design, but company is onboard). Anyway, I run a DTS pkg read a source script file and pass in vars into {} then open a connection and run script. Everything works but the backup line fails any ideas.

Thanks

-----------------------------------
USE MASTER
BEGIN
--HANDLE DATA DEVICE
IF EXISTS (SELECT * FROM dbo.sysdevices WHERE name = '{dbdatadevice}') BEGIN
EXEC sp_dropdevice '{dbdatadevice}'
END
EXEC sp_addumpdevice 'disk', '{dbdatadevice}', '{dbfullbackuppathdata}'

--HANDLE LOG DEVICE
IF EXISTS (SELECT * FROM dbo.sysdevices WHERE name = '{dblogdevice}' ) BEGIN
EXEC sp_dropdevice '{dblogdevice}'
END
EXEC sp_addumpdevice 'disk', '{dblogdevice}', '{dbfullbackuppathlog}'

END
USE MASTER

BACKUP DATABASE {dbname} TO {dbdatadevice}

-------------------------------------------------

slow down to move faster...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-31 : 11:38:27

Instead of what you are doing, use this:



CREATE PROC isp_Backup

AS
SET NOCOUNT ON
DECLARE @dbname char(30), @backupdb char (30)
DECLARE @dbid smallint, @dbstatus int ,@number smallint
DECLARE @dbdescrib char(30)

SELECT @number = 1
SELECT @backupdb = 'backupdb'

DECLARE backupdbs  CURSOR FOR
SELECT DBID, Cast(NAME as char(30)), STATUS FROM sysdatabases
WHERE (DBID = 1 or DBID = 4)
OR (DBID > 6)
ORDER BY 1

OPEN backupdbs 

FETCH NEXT FROM backupdbs 
INTO @dbid, @dbname, @dbstatus 
WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @dbdescrib = CAST(@number AS CHAR(1)) + ' ' + @backupdb
   IF @dbname = 'master'
         BACKUP DATABASE @dbname TO @backupdb WITH INIT,NAME = @dbdescrib
   ELSE
      BEGIN
         IF @dbname = 'msdb'
               BACKUP DATABASE @dbname TO @backupdb WITH NOINIT,NAME = @dbdescrib
         ELSE
               BACKUP DATABASE @dbname TO @backupdb WITH NOINIT, NAME = @dbdescrib
      END

FETCH NEXT FROM backupdbs
INTO @dbid, @dbname, @dbstatus 
SELECT @number = @number + 1

END

CLOSE backupdbs 
DEALLOCATE backupdbs 

RETURN

Just change the select statement to what fits your needs. You might also the individual backup commands as well.

Once you have this stored procedure, you can then just schedule isp_Backup to run nightly. You really don't need to involved DTS for things like this.

Tara

skillile
Posting Yak Master

208 Posts

Posted - 2003-10-31 : 22:08:46

I like the script, thanks.

However, I have multiple servers and I do not have the rights to put SPROCS on these servers. I thought I could build a catalog DB of:
server.catalog, create text files as scripts pass in the variables with a replace function and then execute the newly created script from a worker server that connects to each DB. I don't know, I am actually running into timeout issues and a lot more time is being spent then it is probably worth.

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources