tech@faiz
  SQL Server Scripts
 
SQL Server

SQL Server Scripts
  


Backup all SQL Server databases

Here is the script that will allow you to backup each database within your instance of SQL Server. You will need to change the @path to the appropriate backup directory.

File Naming Format DBname_YYYYDDMM.BAK

DECLARE @nameVARCHAR(50)

-- database name

DECLARE @pathVARCHAR(256)

-- path for backup files

DECLARE @fileNameVARCHAR(256)

-- filename for backup

DECLARE @fileDateVARCHAR(20)

-- used for file name

-- specify database backup directory

SET @path= 'C:Backup'

-- specify filename format

SELECT @fileDate= CONVERT(VARCHAR(20),GETDATE(), 112)

DECLARE db_cursorCURSOR

FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ( 'master', 'model','msdb', 'tempdb' ) -- exclude these databases

OPEN db_cursor

FETCHNEXT FROM db_cursorINTO @name

WHILE@@FETCH_STATUS = 0

BEGIN

SET @fileName = @path+ @name + '_' + @fileDate +'.BAK'

BACKUP DATABASE @nameTO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

File Naming Format DBname_YYYDDMM_HHMMSS.TRN

 DECLARE @nameVARCHAR(50)-- database name

DECLARE @pathVARCHAR(256)-- path for backup files

DECLARE @fileNameVARCHAR(256)-- filename for backup

DECLARE @fileDateVARCHAR(20)-- used for file name

SET @path= 'C:Backup'

SELECT @fileDate= CONVERT(VARCHAR(20),GETDATE(),112)

+ '_'

+ REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursorCURSOR FOR

SELECT name

FROMmaster.dbo.sysdatabases

WHERE nameNOT IN('master','model','msdb','tempdb')

AND DATABASEPROPERTYEX(name,'Recovery') IN ('FULL','BULK_LOGGED')

OPEN db_cursor

FETCHNEXT FROM db_cursorINTO @name

WHILE@@FETCH_STATUS = 0

BEGIN

SET @fileName = @path+ @name + '_' + @fileDate +'.TRN'

BACKUP LOG @nameTO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor 


Shrinking ldf

Here is the script that will allow you to shrink ldf.

--change the recovery mode from full to simple

ALTERDATABASE <DB_Name> SETRECOVERY SIMPLE

DBCC SHRINKDATABASE(<DB_Name> , 5)

DBCC SHRINKFILE

(

<DB_Log>,TRUNCATEONLY

)

--Switching back to full recovery

ALTERDATABASE <DB_Name> SETRECOVERY FULL

 


 
  Today, there have been 2 visitors (21 hits) on this page!  
 
Free Domain This site was last updated Monday, 23 January 2017
Copyright © 2006-2017 smfaizhaider. All rights reserved.