Home > Backup > Backup All MSSQL Databases

Backup All MSSQL Databases

The following sql code takes backup of all MSSQL databases to “D:\database\”, and all these backup files will be having a “*.BAK” extension. Save this file as “D:\database\Backup.sql”.

    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 = 'D:\database\'

    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

Note down the system name(from msinfo32). In my case the system name was “WINDOWS”, replace this with your system name 😉 . Save the following code as a batch file named “D:\database\ftpbackup.bat”. The script will upload all these database backup to a directory named “backup/windows1_bd” in the remote ftp server.

    sqlcmd -S WINDOWS -E -i D:\database\Backup.sql  > backup.log
    dir /a /b /-p /o:gen *.bak > file.txt
    echo open 67.20.99.43 > ftp.bat
    echo servhost  >> ftp.bat
    echo password >> ftp.bat
    echo cd backup/windows1_bd >> ftp.bat
    echo bin  >> ftp.bat
    echo hash >> ftp.bat
    for /F "eol=; delims=," %%i in (file.txt) do (
    @echo put %%i >> ftp.bat
    )
    echo bye >> ftp.bat
    C:\Windows\System32\ftp -s:ftp.bat >> backup.log
    del file.txt file.bat
Advertisements
Categories: Backup
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: