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:

http://msdn.microsoft.com/en-us/library/aa238420%28v=sql.80%29.aspx

For SQL Server 2008/2008 R2

http://msdn.microsoft.com/en-us/library/ms173778.aspx

Related: How to Check MySQL version in Windows? 5 Instructive methods