Well, although I've had some trouble actually getting this out the door, I think I have a script that will copy a database to a new database and be completely intact (minus uncommited transactions in process during time of backup). There are additional options to enable that using NORECOVERY or STANDBY states then applying additional transaction log entries (but that is not the problem I am trying to solve).
I found the basics for this script from Michael Schwarz. I believe his script worked under a different version of SQL Server, but I had to make some adjustments in order to make it work for SQL Server 2005 (beta 2). The primary changes related to the columns returned from the RESTORE FILELISTONLY method, selection of the FILE value of the RESTORE DATABASE command, and adding the RECOVERY option to the RESTORE DATABASE command (otherwise the error "Database 'name' cannot be opened. It is in the middle of a restore" reared its ugly head).
In a nutshell, here is the script:
USE master
GO
DECLARE @DB varchar(200)
SET @DB = 'SourceDB'
-- the backup filename
DECLARE @BackupFile varchar(2000)
SET @BackupFile = 'c:\testing\SourceDBbackup.dat'
-- the new database name
DECLARE @TestDB varchar(200)
SET @TestDB = 'DestinationDB'
-- the new database files without .mdf/.ldf
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = 'c:\testing\destination'
-- ********************************************
-- no change below this line
-- ********************************************
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 varchar(500),
PhysicalName varchar(500),
Type varchar(10),
FilegroupName varchar(200),
Size int,
MaxSize bigint,
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)
GO
6 comments:
Hi Chad,
I am finding similar solution for my requirement.
Infact, i have to sql server instances running on 2 different ip addresses (ie., remote locations).
Now, what i need is to programatically (in C# program, may be console application) get all the databases available in first instance of sql server, copy all those databases to second instance of sql server.
Is this possible, to achieve my goal with this scripting sample (given in your page)... http://chadtc.blogspot.com/2005/03/sql-server-2005-copy-database-script.html
Looking a reply from you..
Regards
Sharad Sharma (Kaps)
Dear Chad...
Thanks.... you have solved most of my problem...
Best of luck.... :)
Take Care
Sharad Sharad (Kaps)
(a.cool.indian@gmail.com)
Dear Chad,
I am able to create duplicate database with this scripting....
Can i perform the same task across the different instances available at different location (ip addresses) remotly, with some changes ?
ex:
First Hosting Server -> sharadHS1
Sql Server Instance -> sharadSQL1
Database Name -> sharadDB1
Second Hosting Server - > sharadHS2
Sql Server Instance -> sharadSQL2
Database Name -> it should be created same as (sharadSQL1)
both the servers (sharadHS1 and sharadHS2) are in a common domain.
ex:
Domain name -> sharadDNS.net
User name -> sharadUN
password -> sharadPWD
now, in the light of above available information, please suggest me what changes should i made in the above script to get my task done..
I hope to be favored with a reply.
Regards
Sharad Sharma (KAPS)
(a.cool.indian@gmail.com)
Hi Sharad,
You can execute some scripts inside of SQL Server as long as there is a link established between the two and the user which is operating the queries has appropriate permissions.
If the two DB's do not have a relationship (via a linked server) you will have to query the meta data of a database, build up a script to execute, then apply them to the new database. Let me know if you have more specific questions.
Regards,
Chad
Hi Chad,
Thanks for the quick responce.
I will try with your suggestions and will get back to you incase i have some more querries.
Regards,
Sharad
Absolutely brilliant - this has really helped me - thanks
Post a Comment