Question

T-SQL Script to Change an Active Database Name and File Names

Asked by: bgungor

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.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2003-02-07 at 10:18:18ID20504779
Tags

file

,

name

,

database

Topic

MS SQL Server

Participating Experts
3
Points
500
Comments
23

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

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.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

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.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

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.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Analyzing tables in SYS
    I have a query I often run but it takes a looonnng time to complete. I took a look at the explain plan and figured that 16 depths of NESTED LOOPS may be the reason why. I don't believe I can modify the query to run faster but the tables it joins do not have statistics held. ...
  2. efficient sql script to change a column type.
    Hi , I am looking for an efficient sql script to change one or more column types from varchar2 to clob Notice that the table has a lot of data (50M Records).

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

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.

Join the Community

Answers

 

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'

 

by: russellshomePosted on 2003-02-07 at 20:23:03ID: 7907266

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.

 

by: bgungorPosted on 2003-02-09 at 13:31:35ID: 7913914

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\PersonalLines.mdf'
EXEC xp_cmdShell 'rename C:\Work\WBMI\PersonalLines\PersonalLines.ldf'

--New Database Name
EXEC sp_attach_db @dbname = N'NewPersonalLines',
  @filename1 = N'C:\Work\WBMI\PersonalLines\NewPersonalLines.mdf',
  @filename2 = N'C:\Work\WBMI\PersonalLines\NewPersonalLines.ldf'


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\PersonalLines\NewPersonalLines.mdf' may be incorrect.


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\PersonalLines\NewPersonalLines.ldf',
  MOVE 'PersonalLines' TO 'C:\Work\WBMI\PersonalLines\NewPersonalLines.ldf'

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

 

by: RonBuesingPosted on 2003-02-10 at 06:46:56ID: 7917811

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\PersonalLines.mdf'
EXEC xp_cmdShell 'rename C:\Work\WBMI\PersonalLines\PersonalLines.ldf'

What it should be is:

--Rename Physical Names
EXEC xp_cmdShell 'rename C:\Work\WBMI\PersonalLines\PersonalLines.mdf NewPersonalLines.mdf'
EXEC xp_cmdShell 'rename C:\Work\WBMI\PersonalLines\PersonalLines.ldf NewPersonalLines.ldf'

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.mdf NewPersonalLines.mdf

Good luck -

 

by: bgungorPosted on 2003-02-10 at 08:23:45ID: 7918533

Ron,

I tried it again, with the same result.  To verify, I performed these operations in SQL Enterprise Manager manually:

1) Detached database
2) Renamed the files in Windows Explorer
3) Attempted to reattach, and received the same device activation error.

Bg

 

by: RonBuesingPosted on 2003-02-10 at 09:26:19ID: 7918978

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

 

by: bgungorPosted on 2003-02-10 at 11:06:15ID: 7919712

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

 

by: RonBuesingPosted on 2003-02-10 at 14:07:22ID: 7921083

I am glad it works for you.

I will create a Stored Procedure for you. However it will not be ready until tomorrow.

Ron

 

by: bgungorPosted on 2003-02-11 at 15:06:00ID: 7929650

Thanks!

Bg

 

by: russellshomePosted on 2003-02-12 at 00:17:26ID: 7932090

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.

 

by: bgungorPosted on 2003-02-12 at 07:52:32ID: 7934433

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

 

by: RonBuesingPosted on 2003-02-12 at 08:38:38ID: 7934808

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.

 

by: bgungorPosted on 2003-02-12 at 20:24:16ID: 7939364

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

 

by: russellshomePosted on 2003-02-13 at 03:53:50ID: 7941150

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'

 

by: russellshomePosted on 2003-02-13 at 03:56:02ID: 7941167

OOps - @newLogFilePath should be varchar(4000) not sysname

 

by: russellshomePosted on 2003-02-13 at 03:58:24ID: 7941187

Also I'm assuming you know how to use transactions & error handling in SQL. If not then ask for this as it is necessary to make the procedure safe (you do not want to drop the database until you are sure it is copied).

 

by: bgungorPosted on 2003-02-13 at 08:03:53ID: 7942850

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

 

by: russellshomePosted on 2003-02-15 at 01:13:57ID: 7955144

I don't want the points bad enough to put up with your demanding tone.

 

by: bgungorPosted on 2003-02-16 at 18:19:06ID: 7963125

Very well.

Moderators, please close this question.  It was not answered completely, and apparently noone has a solution to solve the problem, given the lack of qualified answers.

Thank you.

Bg

 

by: PicklePosted on 2003-03-09 at 14:58:04ID: 8099317

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(@FileName)), @DBName, @NewDBName)
     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

 

by: bgungorPosted on 2003-03-11 at 19:38:25ID: 8116648

Bravo!  This is perfect, and exactly what I need.  Thanks for making my life a lot easier.  I will post another question titled "Points for Pickle", for the other 200 points.  God knows you've earned it!

Thanks a bunch!

Bg

 

by: PicklePosted on 2003-03-11 at 19:47:14ID: 8116688

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.

 

by: bgungorPosted on 2003-03-12 at 06:14:15ID: 8119448

I think I can write the path parsing stuff.  If not, I'll ask the Experts! lol...

Thanks again, and thanks for noticing that assumption.

Regards,

Bg

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...