2010-12-09, 11:29
Back up the transaction log file to make most of the active virtual log files inactive. Therefore, the inactive virtual log files can be removed in a later step. To do this, run a Transact-SQL statement that is similar to the following Transact-SQL statement.
BACKUP LOG <DatabaseName> TO DISK = '<BackupFile>'
Shrink the logfile with this statement.
DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS
In this statement, “TargetSize” is a placeholder for the target size that you want the transaction log file to be. The target size must be reasonable. For example, you cannot shrink the transaction log file to a size that is less than 2 virtual log files.
2010-12-03, 10:20
MsSQL query for backup up all databases excluding master, model, msdb and tempdb.
Don’t forget to change your path, C:\Backup might not be preferred …
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
