Restoring a database and maintaning filename
-
Hello,
I'm trying to restore a database from a live server to a test server. The problem is with the filenames.
As you can see in the image below, the "Restore As" filename follows the first file's name. Is there a way to set it to follow the "Original File Name" ? The database has over 500 ndf files, so clicking the browse button and keying in a new name is very tedious.
Is there a way to fix this?
-
I can't see your picture unfortunately, so I might not be answering the question you're asking, but can you script out the restore and do a find/replace?
-
Beatrix Kiddo (4/6/2016)
I can't see your picture unfortunately, so I might not be answering the question you're asking, but can you script out the restore and do a find/replace?Fixed the image.
I tried scripting, but it still a long tedious task to change each name.
I've restored this on the live server before, and it automatically changes all names to"MDB_Live1.ndf" and the next will be "MDB_Live2.ndf" and the sequence continues.
-
and it doesn't matter if the filename is not the original name, as long as I can get different names, so that the restore can continue.
-
are you overwriting an existing database? If so and the logical filenames are the same you can just use the 'with replace' clause and the physical filenames of the destination database will be maintained.
Also, 500 ndf's!
---------------------------------------------------------------------
-
Rather than using a gui, use a SQL RESTORE command to do the restore. Then the new files can be specified using the "WITH" clause.
And here's a function I created to generate the "MOVE" clauses from an existing db.
The general format of the final RESTORE command will look like this:
RESTORE DATABASE <db_name> FROM DISK = '...' WITH REPLACE, <MOVE '...' TO '...' commands as gen'd below>
Edit: Just hit me, with 500 files, you'll need to change the "varchar(8000)" to "varchar(max)", and make any other changes necessary to keep the result as varchar(max) .
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GenWithMoveClauses] (
@db sysname, --db_name *OR* db_number
@fileSuffix varchar(40) = NULL, --adds a suffix to every file name in the original db
@newPaths varchar(500) = NULL --MOVEs to a different drive/path: specify just "x[:]" to change only the drive;
--to specify a different log drive/path from data drive/path, use semicolon inbetween, like 'datapath;logpath'
)
--SELECT dbo.GenWithMoveClauses('DbA', '', 'M:\Data\')
--SELECT dbo.GenWithMoveClauses('DbB', '_Test_Restore', 'M:;N:\Logs\')
RETURNS varchar(8000)
--Author: Scott Pletcher; Original date: Aug 2002.
BEGIN
DECLARE @dbIsDbNumber bit
DECLARE @newPathsSpecified tinyint
DECLARE @newLogPath varchar(250)
DECLARE @moveClauses varchar(8000)
IF @fileSuffix IS NULL
SET @fileSuffix = ''
IF @newPaths IS NULL
SET @newPaths = ''
IF LEN(@newPaths) = 1
SET @newPaths = @newPaths + ':'
IF CHARINDEX(';', @newPaths) > 0
BEGIN
SET @newLogPath = SUBSTRING(@newPaths, CHARINDEX(';', @newPaths) + 1, 500)
SET @newPaths = LEFT(@newPaths, CHARINDEX(';', @newPaths) - 1)
END --IF
ELSE
SET @newLogPath = @newPaths
SET @newPaths = UPPER(LEFT(@newPaths, 1)) + SUBSTRING(@newPaths, 2, 260)
IF LEN(@newPaths) > 2 AND RIGHT(@newPaths, 1) <> '\'
SET @newPaths = @newPaths + '\'
SET @newLogPath = UPPER(LEFT(@newLogPath, 1)) + SUBSTRING(@newLogPath, 2, 260)
IF LEN(@newLogPath) > 2 AND RIGHT(@newLogPath, 1) <> '\'
SET @newLogPath = @newLogPath + '\'
IF @db LIKE N'%[^0-9]%'
SET @dbIsDbNumber = 0
ELSE
SET @dbIsDbNumber = 1
;WITH CTE ( concatString, concatString_length, file_id, name, physical_name )
AS ( SELECT CAST( '' AS VARCHAR(8000) ), 0, CAST(0 AS bigint), CAST('' AS varchar(8000)), CAST('' AS varchar(8000)) --file_id, name, physical_name
UNION ALL
SELECT CAST(concatString + CASE WHEN concatString_length = 0 THEN '' ELSE ', ' END +
'MOVE ' + '''' + CAST(df.name AS varchar(128)) + ''' TO ' + '''' + CAST(
CASE WHEN type_desc = N'LOG' THEN
CASE WHEN @newLogPath = '' THEN LEFT(df.physical_name, 2) ELSE LEFT(@newLogPath, 2) END +
CASE WHEN LEN(@newLogPath) < 3 THEN SUBSTRING(df.physical_name, 3, LEN(df.physical_name) - CHARINDEX('\', REVERSE(df.physical_name)) - 1)
ELSE SUBSTRING(@newLogPath, 3, 260) END +
CASE WHEN @fileSuffix = '' THEN RIGHT(df.physical_name, CHARINDEX('\', REVERSE(df.physical_name)) - 1)
ELSE STUFF(RIGHT(df.physical_name, CHARINDEX('\', REVERSE(df.physical_name)) - 1),
LEN(RIGHT(df.physical_name, CHARINDEX('\', REVERSE(df.physical_name)) - 1)) - CHARINDEX('.', REVERSE(df.physical_name)) + 1, 0, @fileSuffix) END
ELSE
CASE WHEN @newPaths = '' THEN LEFT(df.physical_name, 2) ELSE LEFT(@newPaths, 2) END +
CASE WHEN LEN(@newPaths) < 3 THEN SUBSTRING(df.physical_name, 3, LEN(df.physical_name) - CHARINDEX('\', REVERSE(df.physical_name)) - 1)
ELSE SUBSTRING(@newPaths, 3, 260) END +
CASE WHEN @fileSuffix = '' THEN RIGHT(df.physical_name, CHARINDEX('\', REVERSE(df.physical_name)) - 1)
ELSE STUFF(RIGHT(df.physical_name, CHARINDEX('\', REVERSE(df.physical_name)) - 1),
LEN(RIGHT(df.physical_name, CHARINDEX('\', REVERSE(df.physical_name)) - 1)) - CHARINDEX('.', REVERSE(df.physical_name)) + 1, 0, @fileSuffix) END
END
AS varchar(200)) + '''' AS varchar(8000)),
1, CAST(df.file_id AS bigint), CAST(df.name AS varchar(8000)), CAST(df.physical_name AS varchar(8000))
-- file_ids are not guaranteed to be sequential and w/o gaps, so gen a guaranteed sequential, nongap number to simplify concat processing
FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY FILE_ID) AS file_id,
name, physical_name, type_desc
FROM master.sys.master_files df WITH (NOLOCK)
WHERE database_id = CASE WHEN @dbIsDbNumber = 1 THEN @db ELSE DB_ID(@db) END
--select * from master.sys.master_files where name like '%vanguard%' --$$
) AS df
INNER JOIN CTE ON CTE.file_id = df.file_id - 1
)
SELECT TOP 1 @moveClauses = concatString
FROM CTE
ORDER BY file_id DESC
--SELECT @moveClauses
RETURN @moveClauses
END --FUNCTION
GO
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Having no SQL knowledge at all, I could not figure out the codes posted here.
However I managed to get the script from the Live server after selecting the same restoration selections using GUI. The script is just a one liner as below. Then I pasted this script in the Test server. It worked perfectly.
You just have to make sure the directory/folder name structure for the "output" are the same as in the Live server.
RESTORE DATABASE [DB NAME] FROM DISK = N'D:\Backup\backup_2016_04_0312.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO
-
Ah, the joys of simplicity....:-)
as I said above, use the replace clause, your logical file names just have to match and files will be restored to their current location
---------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply