[RELEASE] Simple script to backup all SQL Server databases

[janvier123]

Ive been using this script for a very long time, and its time to share it!
Credits at the bottom.


Problem
Sometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks. One of these tasks may be the need to backup all databases on your server. This is not a big deal if you have a handful of databases, but I have seen several servers where there are 100+ databases on the same instance of SQL Server. You could use SQL Server Management Studio to backup the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.


Solution
With the use of T-SQL you can generate your backup commands and with the use of cursors you can cursor through all of your databases to back them up one by one. This is a very straight forward process and you only need a handful of commands to do this.

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 @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

 
-- specify database backup directory
SET @path = 'C:\Backup\'  

 
-- specify filename format
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')  -- exclude these databases

 
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

Notes
In this script we are bypassing the system databases, but these could easily be included as well. You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases. Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.

Also, if you wanted to bypass some of your user databases you can include them in the NOT IN section as well.


Source:

[Farius~]

A simple but very important contribution..
Much easier to do one by one.

[janvier123]

Thats true

[kevinlucy]

thanks

[jvczxc]

Make Your Auto BackUp Make new Folder in data whit name Mante input archive of name BackUp.sql

USE PS_Billing
GO
BACKUP DATABASE PS_Billing
TO DISK = 'C:BackUp\Database\PS_Billing.bak';
GO

USE PS_ChatLog
GO
BACKUP DATABASE PS_ChatLog
TO DISK = 'C:BackUp\Database\PS_ChatLog.bak';
GO

USE PS_GMTool
GO
BACKUP DATABASE PS_GMTool
TO DISK = 'C:BackUp\Database\PS_GMTool.bak';
GO

USE PS_GameData
GO
BACKUP DATABASE PS_GameData
TO DISK = 'C:BackUp\Database\PS_GameData.bak';
GO

USE PS_GameDefs
GO
BACKUP DATABASE PS_GameDefs
TO DISK = 'C:BackUp\Database\PS_GameDefs.bak';
GO

USE PS_GameLog
GO
BACKUP DATABASE PS_GameLog
TO DISK = 'C:BackUp\Database\PS_GameLog.bak';
GO

USE PS_StatData
GO
BACKUP DATABASE PS_StatData
TO DISK = 'C:BackUp\Database\PS_StatData.bak';
GO

USE PS_Statics
GO
BACKUP DATABASE PS_Statics
TO DISK = 'C:BackUp\Database\PS_Statics.bak';
GO

USE PS_UserData
GO
BACKUP DATABASE PS_UserData
TO DISK = 'C:BackUp\Database\PS_UserData.bak';
GO

Make You Archive and Save in Bat Your BackUP is save in 14400 Second reconfig If necesary

@echo off
: Espera
title AutoBackUp en 14400 Segundos
color c1
echo.
echo Faltan segundos para iniciar proxima copia de Seguridad
echo.
timeout /t 43200
mkdir C:\xampp\htdocs\BackUp
mkdir C:\BackUp\DataBase
REM changue IP in Your dedicate IP
SQLCMD -E -S 127.0.0.1 -i C:\ShaiyaServer\SERVER\PSM_Client\Bin\Data\Mante\B ackUp.sql
cls
timeout /t 5
echo.
echo Comprimiendo Archivos.
echo.
"C:\Program Files\WinRAR\WinRAR.exe" A -R C:\xampp\htdocs\BackUp\BackUp-Imperial%date%.RAR "C:\BackUp\DataBase"
"C:\Program Files (x86)\WinRAR\WinRAR.exe" A -R C:\xampp\htdocs\BackUp\BackUp-Imperial%date%.RAR "C:\BackUp\DataBase"
timeout /t 5
rd /s /q "C:\BackUp\DataBase"
mkdir C:\BackUp\DataBase
move C:\xampp\htdocs\BackUp\BackUp-Imperial%date%.RAR C:\BackUp\Database
cls
echo.
echo Carpeta a sido renombrada.
echo.
timeout /t 50
cls
rd /s /q "C:\xampp\htdocs\BacKUp"
echo.
echo Procedimiento realizado con Exito...
echo.
echo reiiciando
timeout /t 50
cls
goto espera

zorry my bag english

the poster is origanaly of JVCZXC