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
Monday, 14 April 2014
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment