1.SQL : Create All databases Backup (Full//Diff/Log)
Part :A
--USE <databasename>
-- Ensure a USE <databasename> statement has been executed first. Replace <databasename> with the
-- name of your database of course
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
|
Part :B (T-SQL Backup SP):
This is my SQL Back-up function. Designed to run daily - saves files as DBName.bak to any path you provide. Automatically creates full backup if first backup of week, uses differential for each backup following. If running >2008 allows use of compression. Emails you with DB Name, Server name and error if it fails.
USE [master]
CREATE PROCEDURE [dbo].[BackupDB]
-- Add the parameters for the stored procedure here
@dbname varchar(255),
@Path varchar(255),
@compress bit,
@errorRecipient varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @cmd varchar(255);
DECLARE @Backup varchar(max);
DECLARE @output INT
DECLARE @backupType varchar(255)
DECLARE @setID varchar(7);
-- Set ID Suffix to YYWW (2-digit year, 2-digit week)
SET @setID= cast(year(getdate()) as varchar)+right('00'+cast(DATEPART(wk,getdate()) as varchar),2);
DECLARE @compression varchar(50);
DECLARE @result varchar(255);
DECLARE @sqlVer INT;SET @sqlVer=(SELECT CAST(SUBSTRING(CAST(SERVERPROPERTY('productversion') AS VARCHAR),1,CHARINDEX('.',CAST(SERVERPROPERTY('productversion') AS VARCHAR))-1) AS INTEGER));
DECLARE @dbMailProfile VARCHAR(60); SET @dbMailProfile='Default';
SET @compression='';
IF (@compress = 1 AND @sqlVer>9) SET @compression=', COMPRESSION'
DECLARE @FinalPath varchar(1024);
SET @FinalPath=@Path;
if (right(@FinalPath,1)<>'\') SET @FinalPath = @FinalPath + '\';
--PRINT @FinalPath
SET @cmd='mkdir '+@FinalPath;
EXEC xp_cmdshell @cmd, no_output
--PRINT @cmd
SET @cmd='DIR "'+@FinalPath+@dbname+@setID+'.bak" /B';
EXEC @output = XP_CMDSHELL @cmd, NO_OUTPUT;
SET @backupType='DIFFERENTIAL';
IF @output = 1 SET @backupType='FORMAT';
PRINT 'Creating Backup of '+@dbname+' using '+@backupType+@compression;
SET @Backup='BACKUP DATABASE ['+@dbname+'] TO DISK = '''+@FinalPath+@dbname+@setID+'.bak'' WITH '+@backupType+' '+@compression+' , NAME = N'''+@dbname+@setID+''', SKIP, REWIND, NOUNLOAD, STATS = 10';
begin try
exec (@Backup);
end try
begin catch
DECLARE @emailBody nvarchar(max);
SET @emailBody='';
SET @emailBody=@emailBody+'Server Name: '+@@SERVERNAME+char(13)+char(10);
SET @emailBody=@emailBody+'Datbase Name: '+@dbName+char(13)+char(10);
SET @emailBody=@emailBody+'Target Path: '+@FinalPath+char(13)+char(10);
SET @emailBody=@emailBody+'Filename: '+@dbname+@setID+'.bak'+char(13)+char(10);
SET @emailBody=@emailBody+'Backup Type: '+@backupType+char(13)+char(10);
SET @emailBody=@emailBody+'Error: ['+isNull(cast(ERROR_NUMBER() as varchar),'')+'] '+isNull(ERROR_MESSAGE(),'')+char(13)+char(10);
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @dbMailProfile,
@recipients = @errorRecipient,
@subject = 'SQL Backup Error',
@body = @emailBody,
@importance = 'HIGH'
end catch
END
|
Part :C (SQL Server Backup Script):
SQL Server Backup Script to backup SQL Server databases. It loops through all the databases dynamically using a select statement to the master database where all the names of the databases are located on the instance. It backs up each database, except those specified in the WHERE clause which you can add your unwanted databases to be backed up. Example below.
WHERE name NOT IN ('tempdb', 'master', 'other')
The script outputs all files with the name of the name of the database_date.bak - Example Below.
master_20110613.BAK
Also added code to setup a batch file that you can set to run using scheduled tasks.
/* SQL Backup Script All Databases.sql */
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Database Backups\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
/*
####################################
# Automatically Run using Scheduled Tasks #
####################################
# Create a batch file with the following code:
del "C:\Database Backups\*.*" /q
"C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -Slocalhost\SQLExpress -Usa -P<sa password> -i"C:\inetpub\SQL Backup Script All Databases.sql"
*/
|
Part :D (SQL Server Backup By Bat file):
This is a 2 part script set. The first set of code is to be created into a *.BAT file. This is the root of the script that you would then call in your task scheduler to run. This is my first time doing this so here goes. ...
==========================
code for the BAT file
==========================
@ECHO OFF
REM *** SQLExpress Backup written by: ChrisW of Spiceworks 3/13/2012 ****
ECHO *** Starting the SQLExpress Backup ****
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S <server>\SQLEXPRESS -U UserName -P UserPass -i "D:\scripts\SQL_Backup_SQL.sql"
ECHO Success!
REM **** Renames the backup file to the time and date it was created ****
REM ** This adds the date / time / creation time to the file name ******
REM ***************************************************************************
ren "D:\_backup_images\Backup_MyDataBase.bak" "Backup_%time:~0,2%%time:~3,2%-%DATE:/=%_MyDataBase.bak"
REM ***** Keeps track of files that were backed up. ****
REM ***** This keeps a running file of the files created in your backup folder **
REM *******************************************************************************
dir D:\_backup_images\*.* > D:\scripts\DB_Backup_Log.txt
REM pause < - put in for debugging
=============================
end of BAT code
=============================
The second part is the *.SQL file. This file is used for the SQL command that is run to backup your database. This file is called " SQL_Backup_SQL.sql " . If you want to change the name of this file. This must also be reflected in the *.BAT file above. Below is the code for the" SQL_Backup_SQL.sql " file.
=============================
SQL_Backup_SQL.sql code
=============================
BACKUP DATABASE <database name> TO DISK = 'D:\_backup_images\Backup_MyDataBase.bak'
go
============================
end of SQL_Backup_SQL.sql code
============================
** Note ***
Any " < " or " > " around a name is just to signify a name. Just replace it with the full name. Example: <database name> equals database name that is running within the SQLExpress SQL Server. If you had database named "MyParts" . Then your SQL_Backup_SQL.sql code file string would be as follows.....
BACKUP DATABASE MyParts TO DISK = 'D:\_backup_images\Backup_MyDataBase.bak'
=========================================================================
Next you'll need to configure your Server for TCP./IP and NAME pipes connections.
- To enable firewall settings
Click Start, click Control Panel, and then click Network Connections.
From the navigation bar on the left, click Change Windows Firewall settings.
On the Exceptions tab, in the Programs and Services box, you will probably see that SQL Server is listed, but not selected as an exception. If you select the checkbox, Windows will open the 1433 port to let in TCP requests. Alternatively, if you do not see SQL Server listed, do the following:
Click Add Program.
Click Browse.
Navigate to drive:/Program Files/Microsoft SQL Server/MSSQL.1\MSSQL\BINN
Add the file sqlservr.exe to the list of exceptions.
======================================================================
Next, go into your Configure Tools on your SQL Express install, and select "SQL Server Configuration Manager". From there, go to your "SQL Server Network Configuration". Then enable for "Protocols" of Name Pipes, TCP/IP. By default these are disabled.
Once that's all set, you should be rocking!
|
OR
---SQLbackup.bat---
@ECHO OFF
REM *** SQLExpress Backup written by: ChrisW of Spiceworks 3/13/2012 ****
ECHO *** Starting the SQLExpress Backup ****
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S
HP2\SQLExpress -U sysadm -P admin -i "E:\MSSQL\SPO_MFG\SQL_Backup_SQL.sql"
ECHO Success!
REM **** Renames the backup file to the time and date it was created ****
REM ** This adds the date / time / creation time to the file name ******
REM ***************************************************************************
ren "E:\MSSQL\SPO_MFG\SPO_MFG.bak" "Backup_%time:~0,2%%time:~3,2%-%DATE:/=%_SPO_MFG.bak"
REM ***** Keeps track of files that were backed up. ****
REM ***** This keeps a running file of the files created in your backup folder **
REM *******************************************************************************
dir E:\MSSQL\SPO_MFG\*.* > E:\MSSQL\SPO_MFG\DB_Backup_Log.txt
REM pause < - put in for debugging
---SQL_Backup_SQL.sql---
BACKUP DATABASE SPO_MFG TO DISK = 'E:\MSSQL\SPO_MFG\SPO_MFG.bak'
GO
|