Backup SQL Server Database and Maintenance of Backup Files

[Curtsy: Pinal Dev http://blog.sqlauthority.com] at: http://blog.sqlauthority.com/2013/02/08/sql-server-backup-and-restore-database-using-command-prompt-sqlcmd/

PowerShell + SQL Job is better option in this case.
Here are two scripts. One for backup, second for maintenance of backup files (for sure you don’t want to run out of storage space)

#————————————————-
#Backup Job script

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null
$s = new-object (“Microsoft.SqlServer.Management.Smo.Server”) $instance

#Define the folder path as a variable
$bkdir = “\\BackupServer\Share”
$dbs = $s.Databases
foreach ($db in $dbs)
{
$status=$db.Status

#Don’t want to backup the tempdb database
if(($db.Name -ne “tempdb”) -and ($status -eq “Normal”))
{
$dbname = $db.Name

#Create a file name based on the timestamp
$dt = get-date -format yyyy-MM-dd_HHmm

$dbBackup = new-object (“Microsoft.SqlServer.Management.Smo.Backup”)
$dbBackup.Action = “Database”
$dbBackup.Database = $dbname
$dbBackup.Devices.AddDevice($bkdir + “\” + $dbname + “_db_” + $dt + “.bak”, “File”)
$dbBackup.SqlBackup($s)
}
}

#—————————————————–
#Backup directory maintenance job script

# Backup destination path
$Path = “\\BackupServer\Share”

# Days for retention (-7 = 7 days kept)
$Daysback = “-7″

$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($Daysback)
Get-ChildItem $Path | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | Remove-Item

 

SQL SERVER – Backup and Restore Database Using Command Prompt – SQLCMD

Backup and Restore is one of the core tasks for DBAs. They often do this task more times than they would have ideally loved to do so. One thing I noticed in my career that every successful DBA knows how to automate their tasks and spend their time either playing games on a computer or learning something new!

Let us see how DBA can automate their task about Backup and Restore using SQLCMD. I am demonstrating a very simple example in this blog post. Please adapt the script based on your environment and needs.

Here is the basic script for Backup and Restore

Note: name of my database server is touch and I am connecting it with windows authentication.

Backup

BACKUP DATABASE AdventureWorks2012 TO DISK='d:\adw.bak'

Restore

RESTORE DATABASE AdventureWorks2012 FROM DISK='d:\adw.bak'

Here is the basic script for Backup and Restore using SQLCMD

Backup

C:\Users\pinaldave>SQLCMD -E -S touch -Q "BACKUP DATABASE AdventureWorks2012 TO DISK='d:\adw.bak'"

Restore

C:\Users\pinaldave>SQLCMD -E -S touch -Q "RESTORE DATABASE AdventureWorks2012 FROM DISK='d:\adw.bak'"

Please leave a comment if you are facing any issue. As mentioned earlier the scripts are very basic scripts, you may have to adapt them based on your environment. For example, if you are facing error there are chances that database files are already open or exists on the drive. You you should also have necessary permissions to do backup and restore as well file operations in your server.

 
declare @DBString varchar(max),
@DelString varchar(400),
@FinalBackup varchar(max),
@dbnAME VARCHAR (max),
@MonthlyFolde varchar(500),
@DailyFolderName varchar(500),
@CreateFolder varchar(2000),
@CopyCommand varchar(500),
@destination varchar(200),
@DatabaseName varchar(50)

–exec sp_dbBackup
declare CursorName cursor for
SELECT name FROM sys.databases WHERE name NOT IN (‘master’, ‘tempdb’, ‘model’, ‘msdb’,’ReportServerTempDB’,’ReportServer’,’distribution’,’POS’,’KSInventory’,’AdmissionTest’)
open CursorName

Fetch NEXT FROM CursorName into @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
set @MonthlyFolde = DATENAME(MONTH,GETDATE()) + DATENAME(YEAR,GETDATE())
set @DailyFolderName = DATENAME(DAY,GETDATE()) + DATENAME(MONTH,GETDATE()) + DATENAME(YEAR,GETDATE())

set @CreateFolder = ‘IF exist D:\BackupToday\’ + @MonthlyFolde +  ‘ (mkdir D:\BackupToday\’ + @MonthlyFolde + ‘\’ + @DailyFolderName +  ‘) ELSE (MKDIR D:\BackupToday\’ + @MonthlyFolde + ‘\’ + @DailyFolderName +  ‘)’
EXEC XP_CMDSHELL @CreateFolder

set @dbnAME = @DatabaseName + ‘_’ + CONVERT(varchar(12),GETDATE(),112) + ‘_’ +  SUBSTRING( CONVERT(varchar(12),GETDATE(),108) ,1,2) + SUBSTRING( CONVERT(varchar(12),GETDATE(),108) ,4,2) +
SUBSTRING( CONVERT(varchar(12),GETDATE(),108) ,7,2)

set @DBString = ‘D:\BackupToday\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘\’ + @dbnAME
set @FinalBackup = @DBString + ‘.bak’
set @DelString = ‘DEL ‘ + @FinalBackup

BACKUP DATABASE @DatabaseName TO  DISK = @FinalBackup
— COMPAC RAR
DECLARE @SERVER VARCHAR(100), @COMMAND VARCHAR(300), @FILE VARCHAR(100)
SET @SERVER = @@SERVERNAME
SET @COMMAND = ‘”D:\BackupToday\Rar” a D:\BackupToday\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘\’ + @dbnAME + ‘.rar D:\BackupToday\’ + @MonthlyFolde + ‘\’ + @DailyFolderName + ‘\’ + @dbnAME + ‘.bak’

–select @COMMAND
EXEC XP_CMDSHELL @COMMAND
—- EXCLUIR FILE BACKUP
EXEC XP_CMDSHELL @DelString

Fetch NEXT FROM CursorName into @DatabaseName
END
CLOSE CursorName
DEALLOCATE CursorName

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s