How to use a single .bak file to create 3 DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

How to use a single .bak file to create 3 DB

Author

Topic

ppatel112
Starting Member

35 Posts

Posted - 2014-11-27 : 19:49:15

Hi peeps,

i have a single .bak file where i want to create 3 databases in sql server with all different names.

so a single source with three databases with script.

please advise.

regards,
parth

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-27 : 20:49:56

Step 1: Retrieve the Logical file name of the database from backup.
RESTORE FILELISTONLY
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
GO

CREATE DATABASE YourDB1;
GO
RESTORE DATABASE YourDB1
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.ldf'
;

CREATE DATABASE YourDB2;
GO
RESTORE DATABASE YourDB2
FROM DISK = 'D:BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile2.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile2.ldf'
;

etc.

ppatel112
Starting Member

35 Posts

Posted - 2014-11-27 : 23:24:14

Hi,
i created the script below

DECLARE @databasename VARCHAR(50) -- database name
DECLARE @SUPPdatabasename VARCHAR(50) -- Support database name
DECLARE @VTdatabasename VARCHAR(50) -- Vendor Testing database name
DECLARE @disk VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @mdffile VARCHAR(256) -- mdffilename for backup
DECLARE @suppmdffile VARCHAR(256) -- suppmdffilename for backup
DECLARE @vtmdffile VARCHAR(256) -- vtmdffilename for backup
DECLARE @ldffile VARCHAR(256) -- ldffilename for backup
DECLARE @suppldffile VARCHAR(256) -- suppldffilename for backup
DECLARE @vtldffile VARCHAR(256) -- vtldffilename for backup
DECLARE @logfile VARCHAR(256) -- log for backup

SET @disk = 'C:\temp\'
SET @databasename = 'CONFIG'
SET @SUPPdatabasename = 'CONFIG_SUPP'
SET @VTdatabasename = 'CONFIG_VT'
SET @fileName = @disk + @databasename + '.BAK'
SET @mdffile = @disk + @databasename + '.mdf'
SET @vtmdffile = @disk + @VTdatabasename + '.mdf'
SET @suppmdffile = @disk + @SUPPdatabasename + '.mdf'
SET @ldffile = @disk + @databasename + '.ldf'
SET @vtldffile = @disk + @VTdatabasename + '.ldf'
SET @suppldffile = @disk + @SUPPdatabasename + @logfile + '.ldf'

RESTORE DATABASE @databasename
FROM DISK = @fileName
WITH
REPLACE -- Overwrite DB - if one exists
, RECOVERY -- Use if NO more files to recover, database will be set ready to use
, STATS = 10 -- Show progress (every 10%)
, MOVE @databasename TO @mdffile
, MOVE @databasename TO @ldffile

RESTORE DATABASE @SUPPdatabasename
FROM DISK = @fileName
WITH
REPLACE -- Overwrite DB - if one exists
, RECOVERY -- Use if NO more files to recover, database will be set ready to use
, STATS = 10 -- Show progress (every 10%)
, MOVE @SUPPdatabasename TO @suppmdffile
, MOVE @SUPPdatabasename TO @suppldffile

RESTORE DATABASE @VTdatabasename
FROM DISK = @fileName
WITH
REPLACE -- Overwrite DB - if one exists
, RECOVERY -- Use if NO more files to recover, database will be set ready to use
, STATS = 10 -- Show progress (every 10%)
, MOVE @VTdatabasename TO @vtmdffile
, MOVE @VTdatabasename TO @vtldffile
GO

this works for the first database but not for other two - it ends up in error
Msg 3234, Level 16, State 2, Line 37
Logical file 'CONFIG_SUPP' is not part of database 'CONFIG_SUPP'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 37
RESTORE DATABASE is terminating abnormally.
Msg 3234, Level 16, State 2, Line 46
Logical file 'CONFIG_VT' is not part of database 'CONFIG_VT'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 46
RESTORE DATABASE is terminating abnormally.

it is conflicting with the use of _log for the log file - how can i tweak for it to work and also to reduce the number of variables?

thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-28 : 09:01:17

To test this, I created a database called teststuff, then restored it to teststuff2 and teststuff3. I used the wizard to generate the restore scripts. This is what I got:


USE [master]
RESTORE DATABASE [teststuff2] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014EXPRESS\MSSQL\Backup\teststuff.bak' WITH  FILE = 1,  MOVE N'teststuff' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014EXPRESS\MSSQL\DATA\teststuff2.mdf',  MOVE N'teststuff_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014EXPRESS\MSSQL\DATA\teststuff2_log.ldf',  NOUNLOAD,  STATS = 5

GO

USE [master]
RESTORE DATABASE [teststuff3] 
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014EXPRESS\MSSQL\Backup\teststuff.bak' 
WITH  FILE = 1,  
MOVE N'teststuff' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014EXPRESS\MSSQL\DATA\teststuff3.mdf',  
MOVE N'teststuff_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014EXPRESS\MSSQL\DATA\teststuff3_log.ldf',  
NOUNLOAD,  STATS = 5

GO


USE [master]
RESTORE DATABASE [teststuff3] 
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014EXPRESS\MSSQL\Backup\teststuff.bak' 
WITH  FILE = 1,  
MOVE N'teststuff' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014EXPRESS\MSSQL\DATA\teststuff3.mdf',  
MOVE N'teststuff_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014EXPRESS\MSSQL\DATA\teststuff3_log.ldf',  
NOUNLOAD,  STATS = 5

GO

So, if you model your script after these examples, it will work. I parameterized it like this:


USE [master];

declare @db nvarchar = N'teststuff4';
declare @file nvarchar(512) = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014EXPRESS\MSSQL\Backup\teststuff.bak';
declare @movemdf nvarchar(512) = N'teststuff';
declare @movemdfto nvarchar(512) =  N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014EXPRESS\MSSQL\DATA\teststuff4.mdf';
declare @moveldf nvarchar(512) = N'teststuff_log';
declare @moveldfto nvarchar(512) =  N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014EXPRESS\MSSQL\DATA\teststuff4_log.ldf';


RESTORE DATABASE @db 
FROM DISK = @file WITH FILE = 1, 
MOVE @movemdf TO @movemdfto, 
MOVE @moveldf TO @moveldfto, 
NOUNLOAD, STATS = 5;

GO

which works just fine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources