Bookmark and Share
Backup tips
(Publish Date: 2014-5-28 11:28am, Total Visits: 350, Today: 1, This Week: 1, This Month: 4)

· --  Review databases and their state

SELECT database_id, name, state_desc FROM sys.databases;

Go

· --  Review files that are used by a dev DB

USE wwdev01

go

SELECT file_id,

      name,

      size as SizeInPages,

      FILEPROPERTY (name, 'SpaceUsed') as SpaceUsedInPages,

      physical_name

FROM sys.database_files;

GO

·--   Set the instance to use backup compression by default.

EXEC sy_configure ‘backup compression default’, 1

GO

RECONFIGURE;

GO

--    Query sp_configure view to see all configuration settings, including the setting for ‘backup compression default’.

SELECT * FROM sys.configurations ORDER BY name;

GO

--    Set recovery model

ALTER DATABASE Market SET RECOVERY SIMPLE;

GO

ALTER DATABASE Market SET RECOVERY FULL;

GO

--    Perform a full backup

BACKUP DATABASE LogTest

                TO DISK = ‘D:\Market\LogTest_Full.bak’

WITH INIT;

GO

· --   Use RESTORE HEADERONLY to get backup information from a backup device

RESTORE HEADERONLY

FROM DISK = ‘D:\MSSQLSERVER\ADV.BAK’;

GO

· --   Use RESTORE FILELISTONLY to get a list of files that are contained in the backup

RESTORE FILELISTONLY

FROM DISK = ‘D:\MSSQLSERVER\Dev.bak’;

GO

--    A mirrored backup

BACKUP DATABASE Dev2014

TO DISK = 'D:\SQLBackups\Dev2014.bak'

MIRROR TO DISK = 'L:\SQLBackups\Dev2014_M.bak'

WITH FORMAT, INIT;

·--   A backup with CHECKSUM

BACKUP DATABASE Dev2014

TO DISK = 'D:\SQLBackups\Dev2014.bak'

WITH FORMAT, INIT, CHECKSUM;

--    Examine the state of the database

(Note that the status shows RECOVERY_PENDING which means that recovery could not be run)

SELECT name, state_desc

FROM sys.databasesI

WHERE name=’dblog’;

GO

--     Perform a trail-log backup

BACKUP LOG dblog

  TO DISK = ‘D:\..\dblog.trn’

  WITH INIT, CONTINUE_AFTER_ERROR;

GO

--     Delete old backups

USE msdb;

GO

EXEC sp_delete_backuphistory @oldest_date = '20140201';

GO