Tag Archives: backup

MS SQL – method to backup all instances/databases through script

10 Feb

Sometimes you need to backup all of the instances and databases in a particular server. One way is to script everything and create a schedule task in Windows to run it.

 

1: Create a sql-file with:

DECLARE @name VARCHAR (100) — database name
DECLARE @path VARCHAR (256) — path for backup files
DECLARE @fileName VARCHAR (256) — filename for backup
DECLARE @fileDate VARCHAR (40) — used for file name

— specify database backup directory
SET @path = ‘E:\MSSQL\Backup\’

— specify filename format
–SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SELECT @fileDate =
CONVERT (VARCHAR (20), GETDATE (), 112)
+ REPLACE (CONVERT (VARCHAR (20), GETDATE (), 108), ‘:’, ”)

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
–PRINT @name
FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

2: Create a cmd-file with (backup instances default, instanceaa, instancebb):

sqlcmd -S servernameXX┬á-i”c:\temp\backup_all_databases.sql”
sqlcmd -S servernameXX\INSTANCEAA -i”c:\temp\backup_all_databases.sql”
sqlcmd -S servernameXX\INSTANCEBB-i”c:\temp\backup_all_databases.sql”

3: Create a schedule task to rund cmd-file e.g everyday

 

Read more

http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/