Tag: Database Backup

SQL Server 2008 – Database Backup Compression

SQL, SQL-Advanced, SQL-Backup & Restore, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Backup compression was introduced in SQL Server 2008 Enterprise. At installation, backup compression default is set to 0, which makes backup compression off by default. To change the default to COMPRESSION, set backup compression default to 1. To revert the default to NO_COMPRESSION, set backup compression default back to 0. Backup Compress at Server Level (Thru Script) USE master; GO EXEC sp_configure 'show advanced option', '1'; RECONFIGURE GO EXEC sp_configure 'backup compression default', '1'; RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure

» Read more
, ,

Restore SQL 2008 full database backup to SQL Server 2005

SQL, SQL-Advanced, SQL-Backup & Restore, SQL-General, Varinder Sandhu

My friend wants to restore the SQL 2008 full backup to SQL Server 2005. He faced an issue while restore the SQL 2008 full backup to SQL Server 2005. He has a question from me as: Can we restore the SQL 2008 full database backup to SQL Server 2005? Answer: No, we can’t restore a backup from a higher version of SQL Server on a lower version of SQL Server. Alternative: If you are not using any features specific to

» Read more
, ,

SQL Server – Mirror Backup of databases

SQL, SQL-Advanced, SQL-Backup & Restore, SQL-General, SQL-Tips and Tricks, Varinder Sandhu

It is very common practice to create an exact copy of the database backup and store it to different places so that in case any emergency or misfortune, DBA should have the alternative location to find the backup of databases.  Once a full backup is accomplished DBAs generally copy the database to another location in their network using utility tools like R-sync or native DOS commands like xcopy etc. Mirroring a media set increases backup reliability by reducing the impact

» Read more
, , ,

SQL Server – Validation of Backup

SQL, SQL-Advanced, SQL-Backup & Restore, SQL-Tips and Tricks, Varinder Sandhu

Database backup are insurance policy for a database, we need to ensure that backups created are valid and useful. To validate the backup, we can use following command Restore verifyonly from <backup device> When a backups is validated, SQL Server performs the following steps Calculated a checksum for the backup and compare to checksum stored in the backup files Verify that the header of backup is correctly written and valid Transits the page chain to ensure that all pages are

» Read more
, , , ,

SQL Server – Backup all the sql server databases

SQL, SQL-Advanced, SQL-Backup & Restore, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Sharing with you script to backup all the SQL server databases Script: DECLARE @name VARCHAR(50) — database name DECLARE @path VARCHAR(256) — path for backup files DECLARE @fileName NVARCHAR(256) — filename for backup DECLARE @fileDate VARCHAR(20) — used for file name SET @path = ‘D:Backup’ SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),113) SELECT name,flag=0 INTO #temp FROM master.dbo.sysdatabases WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’) –according to requirement we can filter databases in where clause SET ROWCOUNT 1 WHILE (exists(SELECT * FROM #temp WHERE flag=0))

» Read more
, ,

SQL Backup Plan with Dynamic file Name

SQL, SQL-Backup & Restore, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

DECLARE @day VARCHAR(5) DECLARE @month VARCHAR(15) DECLARE @year VARCHAR(5) DECLARE @hour VARCHAR(5) DECLARE @filename VARCHAR(500) SET @day = DATENAME(DAY, GETDATE()) SET @month = DATENAME(MONTH, GETDATE()) SET @year = DATENAME(YEAR, GETDATE()) SET @hour = DATENAME(HOUR, GETDATE()) SET @filename = ‘D:DATABASE Backupdb_name_’ + @day + @month + @year + @hour + ‘.bak’ Backup DATABASE db_name TO Disk = @filename WITH format BACKUP log db_name TO disk = @filename WITH format

» Read more
, ,