Loop which return - logical names (db, log)

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 2005 Forums

Transact-SQL (2005)

Loop which return - logical names (db, log)

Author

Topic

anxcomp
Starting Member

41 Posts

Posted - 2007-08-10 : 05:26:59

Could somebody tell me how at loop take:

1. name for all databases (without system dbs)
2. logical name of databases
2. logical name of transaction logs.

I need this information for this

BACKUP LOG @DB_NAME
DBCC SHRINKFILE (@DB_LOG_NAME)

I'd like this two lines will execute for all database.

Thanks

--
Regards,
anxcomp

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-08-10 : 05:39:20

Nigel (nr) has an example of something similar on his website.
have a look:
http://www.mindsdoor.net/SQLAdmin/BackupAllDatabases.html

Duane.

anxcomp
Starting Member

41 Posts

Posted - 2007-08-17 : 15:09:38

Thanks, I wrote my own scrip and I'd like share it:

CREATE TABLE #TDatabases(
DBName nvarchar(128),
DBLogicalName nvarchar(128)
)

INSERT INTO #TDatabases
SELECT db.name DBName, mf.name DBLogicalName
FROM sys.databases db join sys.master_files mf
on db.database_id = mf.database_id
WHERE db.name not in ('master', 'tempdb', 'model', 'msdb', 'distribution') AND type_desc LIKE 'log'

SET NOCOUNT ON
DECLARE @VarDBLogicalName nvarchar(128)
DECLARE @VarDBName nvarchar(128)
DECLARE @VarRowCount int

SELECT top 1 @VarDBName = DBName, @VarDBLogicalName = DBLogicalName
FROM #TDatabases

SET @VarRowCount = @@rowcount

WHILE @VarRowCount <> 0
BEGIN

EXEC(' use ' + @VarDBName + ' backup log '+ @VarDBName + ' with no_log dbcc shrinkfile(''' + @VarDBLogicalName + ''', TRUNCATEONLY) WITH NO_INFOMSGS')

DELETE
FROM #TDatabases
WHERE DBName = @VarDBName

SELECT top 1 @VarDBName = DBName, @VarDBLogicalName = DBLogicalName
FROM #TDatabases

SET @VarRowCount = @@ROWCOUNT

END

DROP TABLE #TDatabases

SET NOCOUNT OFF

It works but use something what Microsoft NOT recommend on SQL 2005 (backup log with no_log)

--
Regards,
anxcomp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources