Monday 14 April 2014

By far my most used script...

The one for an ad hoc backup of a database where I'm using Ola Hallengren's excellent solution, and I've ensured that I'm using the default backup location.

DECLARE @dbname VARCHAR(50)

SET @dbname = 'dbname'

DECLARE @servername VARCHAR(50)

SET @servername = @@SERVERNAME
SET @servername = Replace(@servername, '\', '$')
SET @SERVERNAME = LEFT(@servername, 30)

/*Specify backup location*/
DECLARE @filefolder VARCHAR(50)

--SET @filefolder = 'W:\SQL_Backups_FULL\'
/* Or, to use default location*/
EXEC master.dbo.xp_instance_regread
  N'HKEY_LOCAL_MACHINE',
  N'Software\Microsoft\MSSQLServer\MSSQLServer',
  N'BackupDirectory',
  @filefolder output;

DECLARE @filelocation VARCHAR(150)

SET @filelocation = @filefolder + '\' + @servername + '\' + @dbname
                    + '\FULL\' + @dbname
                    + Replace(CONVERT(VARCHAR(20), Getdate(), 101), '/', '')
                    + Replace(CONVERT(VARCHAR(5), Getdate(), 108), ':', '')
                    + '.bak'

DECLARE @backupname VARCHAR(50)

SET @backupname = @dbname + '-Full Database Backup'

BACKUP DATABASE @dbname TO DISK = @filelocation  

WITH NOFORMAT, NOINIT, name = @backupname, SKIP, NOREWIND, NOUNLOAD, stats = 10, COPY_ONLY
 
GO