Howto automate hot backups of SQL Server 2005 databases

0 Comment(s)

There is often a need for a hot backup of a live SQL server database as a standby. To accomplish this a stored procedure can be created in the master database and called periodically using SQL server Jobs with proper arguments. The script provided below is for MSSQL server 2005 database but the same can be done for SQL server 2000 and SQL server 2008 databases too. I will explain the changes to be done for the same as well. Run the script below in the Query window on the “Master” database to create the DB copy stored procedure.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO USE master GO IF object_id(‘dbo.Dbcopy_sp’,’P’) IS NOT NULL BEGIN PRINT ‘Procedure already exists. So, dropping it’ DROP PROC dbo.Dbcopy_sp END GO CREATE PROCEDURE Dbcopy_sp ( @DB varchar(200), @BackupFile varchar(2000), @TestDB varchar(200), @RestoreFile varchar(2000) ) AS BEGIN DECLARE @query varchar(2000) DECLARE @DataFile varchar(2000) SET @DataFile = @RestoreFile + ‘.mdf’ DECLARE @LogFile varchar(2000) SET @LogFile = @RestoreFile + ‘.ldf’ IF @DB IS NOT NULL BEGIN SET @query = ‘BACKUP DATABASE ‘ + @DB + ‘ TO DISK = ‘ + QUOTENAME(@BackupFile, ””) EXEC (@query) END IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB) BEGIN SET @query = ‘DROP DATABASE ‘ + @TestDB EXEC (@query) END RESTORE HEADERONLY FROM DISK = @BackupFile DECLARE @File int SET @File = @@ROWCOUNT — This always returned 0 for me but the — RESTORE call returned the number — of rows associated with the backup. Strange… DECLARE @Data varchar(500) DECLARE @Log varchar(500) SET @query = ‘RESTORE FILELISTONLY FROM DISK = ‘ + QUOTENAME(@BackupFile , ””) CREATE TABLE #restoretemp ( LogicalName nvarchar(500), PhysicalName nvarchar(500), Type varchar(10), FilegroupName nvarchar(200), Size numeric(30,0), MaxSize numeric(30,0), FileID bigint, CreateLSN numeric(25,0), DropLSN numeric(25,0), UniqueId uniqueidentifier, ReadOnlyLSN numeric(25,0), ReadWriteLSN numeric(25,0), BackupSizeInBytes bigint, SourceBlockSize int, FileGroupId int, LogGroupGUID uniqueidentifier, DifferentialBaseLSN numeric(25,0), DifferentialBaseGUID uniqueidentifier, IsReadOnly bit, IsPresent bit ) INSERT #restoretemp EXEC (@query) SELECT @Data = LogicalName FROM #restoretemp WHERE type = ‘D’ SELECT @Log = LogicalName FROM #restoretemp WHERE type = ‘L’ PRINT @Data PRINT @Log TRUNCATE TABLE #restoretemp DROP TABLE #restoretemp SET @query = ‘RESTORE DATABASE ‘ + @TestDB + ‘ FROM DISK = ‘ + QUOTENAME(@BackupFile, ””) + ‘ WITH MOVE ‘ + QUOTENAME(@Data, ””) + ‘ TO ‘ + QUOTENAME(@DataFile, ””) + ‘, MOVE ‘ + QUOTENAME(@Log, ””) + ‘ TO ‘ + QUOTENAME(@LogFile, ””) + ‘, FILE = 1, RECOVERY’ EXEC (@query) END GO After you run the above script you can find a Stored procedure created in the name of  “Dbcopy_sp” in the master database. You can run the stored procedure manually or call it from scheduled jobs provided by SQL server agent for creating the database copy. The  variables that needs to be passed to the stored procedure are as below:
  • @DB = The original database name which you want to copy.
  • @BackupFile = The database backup file name that will be created along with the path( e.g : C:DatabasesdbcopySourceddbackup.bak )
  • @TestDB = The backup database name that will be created
  • @RestoreFile = The new database file name without .mdf/.ldf (e.g  C:Databasesdestinationdb)
For scheduling the database copy creation create a job with below command: EXEC   Dbcopy_sp    <sourcedatabase> ,  ‘C:DatabasesdbcopySourceddbackup.bak’ ,  <destinationdatabase> ,  ‘C:Databasesdestinationdatabase’ This will create a database named destinationdatabase and the associated .mdf and .ldf files will be placed under the directory “C:Databases”. You will need to alter the database name and backup/restore location according to your need. NOTE: The procedure mentioned above uses the file level restore and hence the fields defined in the table “restoretemp” in the stored procedure has to be altered for SQL Server 2000 and SQL Server 2008/2008 R2. Please visit the below links for the exact fields and their data types. For SQL Server 2000: For SQL Server 2008/2008 R2 [ratings]

Leave a Reply

Your email address will not be published. Required fields are marked *

+ 70 = 74

Looking for any specific topic / article?

Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages

Most popular searches: Technology, Website, AI

Enjoy this blog?

Your may interested in our latest blogs

Stay Updated

Please enter your details below to get
A Free Trial
x + x* =