I need a script that I can run in Query Analyzer that can take a database name and change it and the file names to something else. I would like the most efficient, least destructive approach, that preferably does not require the database server to be affected.
For example:
Database Name: MyDatabase
Files: c:\mydb.mdf
c:\mydb.ldf
to be changed to
Database Name: MyNewDatabase
Files: c:\myNewdb.mdf
c:\myNewdb.ldf
I will add another 200 points for a second script which will create a stored procedure that does the same thing.
Thanks!
Bg
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Detaching the database seems somewhat destructive.
Try this:
BACKUP DATABASE MyDatabase
TO DISK = 'c:\mydb.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\mydb.bak'
RESTORE DATABASE MyNewDatabase
FROM DISK = 'c:\mydb.bak'
WITH MOVE 'MyDatabase' TO 'c:\myNewdb.mdf',
MOVE 'MyDatabase_log' TO 'c:\myNewdb.ldf'
You can decide after copying the database whether you want to detach & delete the original.
Unfortunately guys, neither script was successful by simply plugging into the script the known values.
Ron's Solution:
EXEC sp_detach_db @dbname='PersonalLines'
--Rename Physical Names
EXEC xp_cmdShell 'rename C:\Work\WBMI\PersonalLines
EXEC xp_cmdShell 'rename C:\Work\WBMI\PersonalLines
--New Database Name
EXEC sp_attach_db @dbname = N'NewPersonalLines',
@filename1 = N'C:\Work\WBMI\PersonalLin
@filename2 = N'C:\Work\WBMI\PersonalLin
Ron's returned:
(2 row(s) affected)
(2 row(s) affected)
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name 'C:\Work\WBMI\PersonalLine
Russell's Solution:
BACKUP DATABASE PersonalLines
TO DISK = 'c:\backup.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\backup.bak'
RESTORE DATABASE NewPersonalLines
FROM DISK = 'c:\backup.bak'
WITH MOVE 'PersonalLines' TO 'C:\Work\WBMI\PersonalLine
MOVE 'PersonalLines' TO 'C:\Work\WBMI\PersonalLine
Russell's returned:
Processed 1888 pages for database 'PersonalLines', file 'PersonalLines_dat' on file 2.
Processed 1 pages for database 'PersonalLines', file 'PersonalLines_log' on file 2.
BACKUP DATABASE successfully processed 1889 pages in 3.774 seconds (4.098 MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 16
Logical file 'PersonalLines' is not part of database 'NewPersonalLines'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 16
RESTORE DATABASE is terminating abnormally.
--------------------------
Here are the assumptions. This need only execute on SQL Server 2000. The file names and the database name will be identical (excluding the file extensions). Provide me a script that changes the names of the files and database with a two parameters, the database name, and the name I want the database changed to. Assume I do not know where the files are. I am aware that you can retrieve this information from the master table. Do not leave any temporary files, or the old files, around.
For example:
create procedure sp_ChangeDBNames
(
@OldDBName VARCHAR(500),
@NewDBName VARCHAR(500)
)
AS
???
Please test your results, as I will not award this number of points to an incomplete solution. Thank you for your effort.
Bg
I have tested the code before submitting, What the error is stating is that the file you are trying to rename is incorrect.
What you have is:
--Rename Physical Names
EXEC xp_cmdShell 'rename C:\Work\WBMI\PersonalLines
EXEC xp_cmdShell 'rename C:\Work\WBMI\PersonalLines
What it should be is:
--Rename Physical Names
EXEC xp_cmdShell 'rename C:\Work\WBMI\PersonalLines
EXEC xp_cmdShell 'rename C:\Work\WBMI\PersonalLines
So just replace all the code in the -- Rename Physical Names
The XP_cmdShell stored proc. is executing standard DOS commands to rename your file.
You have the code typed in incorrectly. Just think DOS.
Actual DOS command:
rename c:\folder\PersonalLines.md
Good luck -
bgungor,
I know this must be frustrating, but hang in there, I know this code works.
The error is stating the path you have entered is invalid meaning:
You must verify the file path to make sure it exists.
The sample I have created for you works with a database named:
'MyDatabase'
Use Enterprise manager to do this:
So if you like, try creating the database 'MyDatabase'
On the General Tab in the name field:
Type: MyDatabase
On the Data files tab:
Rename the path of the database to C:\MyDB.mdf
On the Transaction Log tab:
Rename the path of the log file to C:\MyDB.ldf
Click the OK Button:-------------------
--------------------------
--In Query Analyzer then run this code
--Current Database name
EXEC sp_detach_db @dbname='MyDatabase'
--Rename Physical Names
EXEC xp_cmdShell 'rename c:\MyDB.mdf MyNewDB.mdf'
EXEC xp_cmdShell 'rename c:\MyDB.ldf MyNewDB.ldf'
--New Database Name
EXEC sp_attach_db @dbname = N'MyNewDB',
@filename1 = N'c:\MyNewDB.mdf',
@filename2 = N'c:\MyNewDB.ldf'
--------------------------
Above is how I ran this code step by step on my system.
Good Luck
Ron
Well, I did what you said, and it worked. I then put my variables in, and... it worked..?!? I don't know what I did wrong before.
There is just one other thing.
Could you package this as a stored proc, and not require the paths to the files to be input? I know the paths can be found in the master database, and all I want to pass to my stored proc is the old and new names. I would like the files to remain in the same directory, but have the new name. The procedure should not break if there are multiple files, but you may number them incrementally as you like.
If you need more information, let me know. And thanks!
Bg
I'm assuming that you realise that Ron's methodology requires that the database is able to run shell commands?
In most SQL Server installations (e.g. web hosting, LAN), this would be inappropriate because of the security implications. I am yet to see any instances where I thought that allowing SQL Server this ability was a good idea.
Russell, if you have a solution, please present it. I want the *best* solution to my problem. You assume wrong that I realize what this best solution might be, or that I understand all of the implications of any particular approach.
Even if Ron completes this task, that does not mean I would not provide points for a better solution. As you can see from my profile, I rarely ask questions, and my expertise is in .NET, not SQL Server. I assure you I have enough points to go around, but I NEED a working example that is easy for me to deploy (preferably a stored proc, as this function will be in an application I am building). If you read the above comments, you will get the entirety of my requirements.
Thanks for the effort.
Bg
In this case after reading the above comment, To create this store procedure takes time, and time is money. So Russelhome if you have a better solution please post it, I would rather not build something and waste my time or nor waste bqunqor's time.
I realize that in a microsoft world there many unsafe ways to do things, however the safe way to run a server on the net, is to shut it off.
In Short, my experience is this, XPcmdShell procedure can be changed to be executed as a public user and well it is obviously said, if you allow this, well enough said. So it is good practice to read the latest security postings, however you will need to hire another person for this. This is a full time position.
GOOD LUCK and no hard feelings, i hope.
Ron, my comment was to elicit useful conversation, rather than an ideological battle over an approach. If I thought this problem was easy, I would have done it myself. I believe that time is money, and if you don't have the time, don't waste it to propose an answer. I do find it plausible that, like me, you found this challenge to be too difficult. I personally have five years of SQL Server experience, but, as I have admitted, am no DBA.
The only thing missing in your answer seems to be a way to call the procedure and have it change all of the file names, which would be retrieved from the system tables of the database. If all I wanted was to change the files by hand, I could have done that far easier by using explorer. The security implications are not a concern for me right now, since your solution calls for an extended procedure call. These things can be secured, I assume, like any other proc. Additionally, I am not building a web application.
So, to clarify my position, I just want to say that I am open to multiple approaches, and do welcome commentary on each strength and weakness of the approach. If it works, I will do everything I can to award points for it. I have enough points so that I can award each individual's effort. Please do not leave me without alternatives, as I value the input of the entire community.
Thank you.
Bg
Looking back at your attempt, it looks like you typed
NewPersonalLines.ldf twice instead of typing NewPersonalLines.mdf. You also typed PersonalLines twice instead of PersonalLines_Log. These are likely to be the source of your errors.
Enhanced answer:
(I will leave the task of deleting the temp file up to you). The stored procedure should be created in the master database in which case you can omit the 'use master' clause.
create procedure changeDBNAme
@oldDBName sysname
, @oldDataName sysname
, @oldLogName sysname
, @tempFilePath varchar(4000)
, @newDBName sysname
, @newDataFilePath varchar(4000)
, @newLogFilePath sysname
as
declare @sql varchar(8000)
set @sql = '
use master
BACKUP DATABASE ' + @oldDBName +'
TO DISK = ''' + @tempFilePath + '''
RESTORE FILELISTONLY
FROM DISK = ''' + @tempFilePath + '''
RESTORE DATABASE ' + @newDBName + '
FROM DISK = ''' + @tempFilePath + '''
WITH MOVE ''' + @oldDataName + ''' TO ''' + @newDataFilePath + ''',
MOVE ''' + @oldLogName + ''' TO ''' + @newLogFilePath + '''
drop database ' + @oldDBName + '
'
exec(@sql)
======================
Example of use:
======================
exec changeDBNAme 'Test', 'Test_Data', 'Test_Log', 'C:\Test_Data.mdf', 'Test2', 'c:\Test2_Data.mdf', 'c:\Test2_Data.ldf'
This is a good start, russell. The challenge of this question, however, is to permit the database to have any number of files, and explicitly, to not require the user of the proc to know where the files are, or how they are partitioned. Your proc is limited to 2 files.
Additionally, I don't want any messes to clean up, since the sole purpose of this activity is to change the names of the files and the database, not explicitly to create or copy over the database to a new name. That leftover is simply a result of the approach you took. Please clean up any leftovers inside the procedure to make the activity encapsulated in one unit. I will be interested to see how you do this without using xp_cmdShell.
Thanks,
Bg
Here ya go bgungor, I saw this thread and couldn't help take up the challenge, plus 700 points would be great for my first answer :)
This will make a stored proc named sp_renamedbandfiles. If you have a db named Test and you want to name it NewTest, just do the following
Exec sp_renamedbandfiles 'Test', 'NewTest'
Make sure to run the bellow script in the Master database, this will not work while you are in the database that you want to rename, as it will give you a error if you try to detach the db while you are using it.
CREATE PROCEDURE sp_renamedbandfiles @DBName nvarchar(256), @NewDBName nvarchar(256)
WITH RECOMPILE
AS
Declare @SQL nvarchar(1000)
Declare @FileName nvarchar(260)
Declare @FileCount int
Declare @NewFileName nvarchar(260)
Declare @CmdLine nvarchar(500)
SET @SQL = 'DECLARE curFiles CURSOR STATIC GLOBAL FOR SELECT filename FROM ' + @DBName + '.dbo.sysfiles'
EXEC sp_executesql @SQL
OPEN curFiles
SET @SQL = N'sp_detach_db @DBName = ''' + @DBName + ''''
EXEC sp_executesql @SQL
SET @FileCount = 0
SET @SQL = N'sp_attach_db @DBName = ''' + @NewDBName + ''''
FETCH NEXT FROM curFiles
INTO @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FileCount = @FileCount + 1
SET @NewFileName = REPLACE(LTRIM(RTRIM(@FileN
SET @SQL = @SQL + ', ' + N'@FileName' + CAST(@FileCount as NVarChar) + ' = N''' + @NewFileName + ''''
SET @CmdLine = N'move "' + LTRIM(RTRIM(@FileName)) + '" "' + LTRIM(RTRIM(@NewFileName))
EXEC master.dbo.xp_cmdShell @CmdLine
FETCH NEXT FROM curFiles
INTO @FileName
END
deallocate curFiles
EXEC sp_executesql @SQL
Hey thanks! Just one thing, make sure that they name that you are changing the database from is not anywhere in the path to the files like if you changed your database named "books" in the following directory
C:\databases\Books Databases\books.mdf
into "magazines" it would do this
C:\databases\magazines databases\magazines.mdf
This is because I use the REPLACE sql command to replace one string with another so that I didn't have to write a whole path parsing system.
Anyways, that is the only caveat to what you asked for... Enjoy.
Business Accounts
Answer for Membership
by: RonBuesingPosted on 2003-02-07 at 13:28:57ID: 7905799
I think this is what you're looking for:
--Current Database name
EXEC sp_detach_db @dbname='MyDatabase'
--Rename Physical Names
EXEC xp_cmdShell 'rename c:\MyDB.mdf MyNewDB.mdf'
EXEC xp_cmdShell 'rename c:\MyDB.ldf MyNewDB.ldf'
--New Database Name
EXEC sp_attach_db @dbname = N'MyNewDB',
@filename1 = N'c:\MyNewDB.mdf',
@filename2 = N'c:\MyNewDB.ldf'