Hi guys,
Since my day to day job is a DBA, I figured I will share stuff that others might find useful.
For the first time I will share a small script which will help you generate T-SQL commands in order to script the backup/restore process of your SQL Server.
This was tested on Microsoft SQL Server 2012 (SP1) – Express Edition (64-bit). Feel free to send your comments.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
/* This script will list all the databases on the server and generate backup and restore commands for each database, based on the recovery model. The script will also determine if your SQL Server version supports compressed backups and will generate the commands accordingly. (C) viorel.ciucu@gmail.com, 28 Aug 2014 */ DECLARE @backup_location VARCHAR(254) = 'C:\TEMP\' -- full, UNC path DECLARE @compresion VARCHAR(13) = '' DECLARE @a nvarchar(max) = '' DECLARE @CR AS CHAR(1) -- Carriage Return (CR) DECLARE @LF AS CHAR(1) -- Line Feed (LF) DECLARE @CrLf AS CHAR(2) -- Carriage Return / Line Feed SET @CR = CHAR(10) SET @LF = CHAR(13) SET @CrLf = @CR + @LF IF (SERVERPROPERTY('EngineEdition') = 3) BEGIN PRINT 'Enterprise Edition detected, backup compression IS SUPPORTED!'; SET @compresion = ' ,COMPRESSION'; END ELSE BEGIN PRINT 'Enterprise Edition NOT detected, backup compression IS NOT SUPPORTED!'; SET @compresion = ''; END SELECT @@SERVERNAME as ServerName, database_id, name, --recovery_model, recovery_model_desc, --CONCAT('ALTER DATABASE [', name, '] SET READ_ONLY') AS read_only_command, CONCAT('BACKUP DATABASE [', name, '] TO DISK = N''', @backup_location+name, '.bak''', ' WITH NOFORMAT, NOINIT', @compresion, ', STATS = 10;') + @CR + CASE WHEN recovery_model in (1, 2) -- the recovery model is either FULL or BULK_LOGGED THEN CONCAT('BACKUP LOG [', name, '] TO DISK = N''', @backup_location+name, '.trn'' WITH NOFORMAT, NOINIT, STATS = 10') + @LF ELSE '' END AS backup_command, --CONCAT('ALTER DATABASE [', name, '] SET READ_WRITE WITH NO_WAIT') AS read_write_command, CONCAT('RESTORE DATABASE [', name, '] FROM DISK = N''', @backup_location+name, '.bak''', ' WITH REPLACE, NORECOVERY;') + @CR + CASE WHEN recovery_model in (1, 2) -- the recovery model is either FULL or BULK_LOGGED THEN CONCAT('RESTORE LOG [', name, '] FROM DISK = N''', @backup_location+name, '.trn''', ' WITH NORECOVERY;') + @LF + CONCAT('RESTORE DATABASE [', name, '] WITH RECOVERY;') + @LF ELSE CONCAT('RESTORE DATABASE [', name, '] WITH RECOVERY;') + @LF END AS restore_command FROM sys.databases WITH (NOLOCK) WHERE database_id > 4 ORDER BY name |