How Can I Save Space While Doing SQL Backups

In SQL Server there are options of Compressed and Uncompressed Database Backups. Compressed backup are good option with newer versions of SQL (SQL 2008 onward) especially when there is spacing issue. As these are smaller in size than the uncompressed ones of the same data and typically require less Input/Output on device and it increases backup speed significantly.

As everything now a days about saving and making a difference so question arises how much one could save by changing backup type to compressed. Although it depends on various variables but below query will give you an estimate of savings you would make by changing to compressed backups. 

SELECT backup_size/compressed_backup_size FROM msdb.dbo.backupset;

If a compression ratio is around 4:1, that would indicate that you are saving about 75% of disk space.

Obviously it does not come without restrictions as it’s introduced in recent versions of SQL below are some limitations of using compressed backups.

•  Compressed and uncompressed backups cannot co-exist in a media set.
•  Previous versions of SQL Server cannot read compressed backups 
•  NT backups cannot share a tape with compressed SQL Server backups.

  So how do I go about changing the backup compression on my server?

To enabling backup compression at the server level, right-click on the instance name inside of server explorer in SSMS to bring up the Server Properties screen. Go to the 'Database Settings' and you should see the following: 

 

Secondly the same setting could be done on Server level through below  T-SQL Query

EXEC sys.sp_configure 'backup compression default', '1'

GO

RECONFIGURE WITH OVERRIDE

GO

If you want to implement this to a single database it could be done as below 

BACKUP DATABASE [AdventureWorks]

TO  DISK = N'E:\Backup\AWS_compress.bak'

WITH COMPRESSION

GO

I am a big fan of the best practices below I have collated a list of best practices when comes to dealing with SQL backups. 


 Best Practices when dealing with SQL Backups 

• Backups should not be on the same physical storage as your database files
• Automate your backups using scheduled jobs
• Use a test environment to verify the restore procedure of backups
• Test Recovery Options and Recovery Strategy
• Use Verification options (Use Checksum/Verify backups when finished)
• Backup System Databases daily (Master/Model/MSDB)
• Backup User Databases with Full/Differential and Log backups depending on business needs
• Highlight the importance of valid backups to your organisation
• Monitor your Backups to make sure no surprises

Database Users and Logins after Restoring Back up from different SQL SERVER instance

I came across this after migrating some databases from Test Environment to Production that there are orphan accounts when restored Database to Production and some are missing ties between users and logins .So in short if you run the below store procedure in context of your database it will take care of your users and logins

---SCRIPT ALL USERS AND PASSWORDS　
SELECT 'IF(SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL)BEGIN CREATE LOGIN '+QUOTENAME(SP.name)+CASE WHEN SP.type_desc = 'SQL_LOGIN'THEN ' WITH PASSWORD = '+CONVERT(NVARCHAR(MAX),SL.password_hash,1)+' HASHED'ELSE ' FROM WINDOWS'END + ';/*'+SP.type_desc+'*/ END;'COLLATE SQL_Latin1_General_CP1_CI_ASFROM sys.server_principals AS SPLEFT JOIN sys.sql_logins AS SLON SP.principal_id = SL.principal_id
WHERE SP.type_desc IN ('SQL_LOGIN','WINDOWS_GROUP','WINDOWS_LOGIN')AND SP.name NOT LIKE '##%##'AND SP.name NOT IN ('SA');
　　
--TIE USERS SPIDS TO AVOID ORPHANED ACCOUNTS

EXEC sp_MSForEachDb '
USE [@DataBaseName]

DECLARE @cmd VARCHAR(1000)

DECLARE @users TABLE (Name VARCHAR(100), sid UNIQUEIDENTIFIER)

DECLARE @logins TABLE (Name VARCHAR(100), sid UNIQUEIDENTIFIER)
DECLARE @cmd VARCHAR(1000)
DECLARE @users TABLE (Name VARCHAR(100), sid UNIQUEIDENTIFIER)
DECLARE @logins TABLE (Name VARCHAR(100), sid UNIQUEIDENTIFIER)
INSERT INTO @users SELECT name, sid FROM sysusers
INSERT INTO @logins SELECT name, sid FROM master.dbo.syslogins SELECT @cmd = COALESCE(
@cmd + CHAR(10) + ''EXEC sp_change_users_login ''''UPDATE_ONE'''', '''''' + u.name + '''''', '''''' + u.name + '''''''',
''USE '' + DB_NAME() + CHAR(10) + ''EXEC sp_change_users_login ''''UPDATE_ONE'''', '''''' + u.name + '''''', '''''' + u.name + ''''''''
)
FROM @users u JOIN @logins l

ON u.name = l.name AND u.sid <> l.sid

IF ISNULL(@cmd, '''') <> '''' BEGIN

PRINT @cmd

EXEC(@cmd)

END ELSE BEGIN

PRINT ''-- '' + ''?'' + '' All users resolved''

END
IF ISNULL(@cmd, '''') <> '''' BEGIN
PRINT @cmd
EXEC(@cmd)
END ELSE BEGIN
PRINT ''-- '' + ''?'' + '' All users resolved''
END
'

Database Backup and How long it took

A Quick way to find out when was the last backup done and how long it took to complete

SELECT
  bup.user_name AS [User],
  bup.database_name AS [Database],
  bup.server_name AS [Server],
  bup.backup_start_date AS [Backup Started],
  bup.backup_finish_date AS [Backup Finished]
  ,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
  AS [Total Time]
FROM msdb.dbo.backupset bup
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
WHERE bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
ORDER BY bup.backup_finish_date desc