This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing one T-SQL Script to take full backup for all the databases of SQL Server.
There are different types of backups and techniques available in the SQL Server, but I always believe in DBA Scripts which you can modify as per your requirements.
Apart from scheduled backups, sometimes DBA requires ad-hoc full backups of databases which they can do using this script.
Script to take full backup of databases:
You can change the path name (‘C:\TempBackup\’) and replace with your path name.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DECLARE @dbName NVARCHAR (255); DECLARE @SQL NVARCHAR(4000); DECLARE DBBackupCur CURSOR FOR SELECT name FROM sys.databases WITH (NOLOCK) WHERE name NOT IN ('master','model','msdb','tempdb') OPTION (RECOMPILE); OPEN DBBackupCur; FETCH NEXT FROM DBBackupCur INTO @dbName WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN SET @SQL = N'BACKUP DATABASE [' + @dbName + N'] TO DISK = ''C:\TempBackup\' + @dbName + N'.bak'''; EXECUTE sp_executesql @SQL PRINT N'Backup completed: ' + @dbName END; FETCH NEXT FROM DBBackupCur INTO @dbName; END; CLOSE DBBackupCur; DEALLOCATE DBBackupCur; GO |
Leave a Reply