Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

SQL Server - Automated All Databases Backups Script

Aug 20 2011 12:00AM by Paresh Prajapati   

I have published my posts related to database backup history, restore history and also cleanup for the same recently. For the database backup we are performing using the maintenance plans or wizard. We cal also performing this database activity by scripts automatically same as maintenance plans. Visit full database backup from SSMS for reference. 

How can?
I have created a scripts to perform FULL, Differential and Transactional backups of user databases. I would like to share the scrips with you.

#1. Perform Full Backups:
 Here i have predefined the backup folder and the it will be taken in DBName_FullBackup_Date_time format for the user databases. You can see here i have written a script for database backups which are in ONLINE state and have database_id > 4 means it will not consider system databases. 
DECLARE @Baksql VARCHAR(8000)
DECLARE @BackupFolder VARCHAR(100)
DECLARE @BackupFile VARCHAR(100)
DECLARE @BAK_PATH VARCHAR(4000)
DEclare @BackupDate varchar(100)

-- Setting value of  backup date and folder of the backup
SET @BackupDate =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') -- 20110517_182551
SET @BackupFolder = 'D:\DBBackups\'
SET @Baksql = ''

-- Declaring cursor
DECLARE c_bakup CURSOR FAST_FORWARD READ_ONLY FOR  
SELECT NAME FROM SYS.DATABASES 
WHERE state_desc = 'ONLINE' -- Consider databases which are online
AND database_id > 4  -- Exluding system databases


-- Opening and fetching next values from sursor
OPEN c_bakup 
FETCH NEXT FROM c_bakup INTO @BackupFile 

WHILE @@FETCH_STATUS = 0
BEGIN

SET @BAK_PATH = @BackupFolder + @BackupFile

-- Creating dynamic script for every databases backup
SET @Baksql = 'BACKUP DATABASE ['+@BackupFile+'] TO DISK = '''+@BAK_PATH+'_FullBackup_'+@BackupDate+'.bak'' WITH INIT;'

-- Executing dynamic query
PRINT (@Baksql)
EXEC(@Baksql)

-- Opening and fetching next values from sursor
FETCH NEXT FROM c_bakup INTO @BackupFile 
END 

-- Closing and Deallocating cursor
CLOSE c_bakup
DEALLOCATE c_bakup














#2. Perform Differential Backups:
 Here i have predefined the backup folder and the it will be taken in DBName_DiffBackup_Date_time format for the user databases. You can see here i have written the script for backups which are in ONLINE state and have database_id > 4 means it will not consider system databases. 
DECLARE @Baksql VARCHAR(8000)
DECLARE @BackupFolder VARCHAR(100)
DECLARE @BackupFile VARCHAR(100)
DECLARE @BAK_PATH VARCHAR(4000)
DEclare @BackupDate varchar(100)

-- Setting value of  backup date and folder of the backup
SET @BackupDate =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') -- 20110517_182551
SET @BackupFolder = 'D:\DBBackups\'
SET @Baksql = ''

-- Declaring cursor
DECLARE c_bakup CURSOR FAST_FORWARD READ_ONLY FOR  
SELECT NAME FROM SYS.DATABASES 
WHERE state_desc = 'ONLINE' -- Consider databases which are online
AND database_id > 4  -- Exluding system databases

-- Opening and fetching next values from sursor
OPEN c_bakup 
FETCH NEXT FROM c_bakup INTO @BackupFile 

WHILE @@FETCH_STATUS = 0
BEGIN

SET @BAK_PATH = @BackupFolder + @BackupFile

-- Creating dynamic script for every databases backup
SET @Baksql = 'BACKUP DATABASE ['+@BackupFile+'] TO DISK = '''+@BAK_PATH+'_DiffBackup_'+@BackupDate+'.bak'' WITH DIFFERENTIAL;'

-- Executing dynamic query
PRINT (@Baksql)
EXEC(@Baksql)

-- Opening and fetching next values from sursor
FETCH NEXT FROM c_bakup INTO @BackupFile 
END 

-- Closing and Deallocating cursor
CLOSE c_bakup
DEALLOCATE c_bakup












#3. Perform Transactional Backups:
 Here i have predefined the backup folder and the it will be taken in DBName_TrnBackup_Date_Time.trn format for the user databases. you can see here i have written the script for backups which are in ONLINE state and have database_id > 4 means it will not consider system databases even databases are in FULL recovery mode otherwise it will be failed.
DECLARE @Baksql VARCHAR(8000)
DECLARE @BackupFolder VARCHAR(100)
DECLARE @BackupFile VARCHAR(100)
DECLARE @BAK_PATH VARCHAR(4000)
DEclare @BackupDate varchar(100)

-- Setting value of  backup date and folder of the backup
SET @BackupDate =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') -- 20110517_182551
SET @BackupFolder = 'D:\DBBackups\'
SET @Baksql = ''

-- Declaring cursor
DECLARE c_bakup CURSOR FAST_FORWARD READ_ONLY FOR  
SELECT NAME FROM SYS.DATABASES 
WHERE state_desc = 'ONLINE' -- Consider databases which are online
AND database_id > 4  -- Exluding system databases
AND recovery_model_desc = 'FULL'  -- Including database which are only have FULL recovery mode

-- Opening and fetching next values from sursor
OPEN c_bakup 
FETCH NEXT FROM c_bakup INTO @BackupFile 

WHILE @@FETCH_STATUS = 0
BEGIN

SET @BAK_PATH = @BackupFolder + @BackupFile

-- Creating dynamic script for every databases backup
SET @Baksql = 'BACKUP Log ['+@BackupFile+'] TO DISK = '''+@BAK_PATH+'_TrnBackup_'+@BackupDate+'.trn'' ;'

-- Executing dynamic query
PRINT (@Baksql)
EXEC(@Baksql)

-- Opening and fetching next values from sursor
FETCH NEXT FROM c_bakup INTO @BackupFile 
END 

-- Closing and Deallocating cursor
CLOSE c_bakup
DEALLOCATE c_bakup











These are the scripts which will perform full, differential and transaction log backup with time stamps extent for each backup file. You just need to schedule these script only. We can also use MSFOREACHDB if we don't need to filter any databases to backup. 

I hope you liked this scripts for automated backups. You can share the way you are using for the backup plans here.

Tags: sql, tsql, sql server, ms sql, ms sql server, t-sql, #SQL Server, #sql, database, sql server general, SQL Scripts, backup,


Paresh Prajapati
6 · 23% · 7485
5
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"SQL Server - Automated All Databases Backups Script " rated 5 out of 5 by 5 readers
SQL Server - Automated All Databases Backups Script , 5.0 out of 5 based on 5 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]