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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -