Backing up Microsoft SQL databases

Support Wissensdatenbank

Du bist hier:
Print

Backing up Microsoft SQL databases

We have created a small script that automatically backs up all databases. In principle, we automatically back up the databases on our own server, but sometimes it can be advantageous to have your own backups.

Please adjust the backup folder with your own folder path. Afterwards, contact our support so that we can adjust the rights for the folder.

sr00.firestorm.ch => Please replace with your own server name.
USERNAME => Username for Microsoft SQL Server
PASSWORD => Password for MS SQL

sqlcmd -S sr00.firestorm.ch -U USERNAME -P PASSWORD -i MSSQL-Backup.sql

 

MSSQL-Backup.sql

DECLARE @BackupFile varchar(255), @DB varchar(30), @Description varchar(255), @LogFile varchar(50)
DECLARE @Name varchar(30), @MediaName varchar(30), @BackupDirectory nvarchar(200) 
SET @BackupDirectory = 'C:\Inetpub\vhosts\musterseite.ch\meineDBbackups\'
DECLARE Database_CURSOR CURSOR FOR SELECT name FROM sysdatabases WHERE name <> 'tempdb' AND name <> 'model' AND name <> 'master' AND name <> 'msdb' AND name not like 'ReportServer%'
OPEN Database_Cursor
FETCH next FROM Database_CURSOR INTO @DB
WHILE @@fetch_status = 0

    BEGIN
    	SET @Name = @DB + '(Daily Bck)'
    	SET @MediaName = @DB + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP , 112)
    	SET @BackupFile = @BackupDirectory + + @DB + '.bak'
    	SET @Description = 'Normal' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' 

    	IF (SELECT COUNT(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 OR @DB = 'master'
    		BEGIN
    			SET @BackupFile = @BackupDirectory + @DB + '.bak'
    			SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' 
    		END	
    	ELSE
    		BEGIN
    			SET @BackupFile = @BackupDirectory + @DB + '.bak'
    			SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' 
    		END
    		BACKUP DATABASE @DB TO DISK = @BackupFile 
    		WITH NAME = @Name, DESCRIPTION = @Description , 
    		MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description , 
    		STATS = 10
    	FETCH next FROM Database_CURSOR INTO @DB
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor

 

War dieser Artikel hilfreich?
0 out Of 5 Stars
5 Stars 0%
4 Stars 0%
3 Stars 0%
2 Stars 0%
1 Stars 0%
Wie können wir diesen Artikel verbessern?
Please submit the reason for your vote so that we can improve the article.
Brauchst du Hilfe?
Related Post