Renaming all databases simultaneously | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Renaming all databases simultaneously

I have several hundreds of mdf and ldf files arranged in respective folder..like p2p_data.mdf, p2p_log.ldf are in people.com folder;yap_data.mdf, yap_log.ldf are in yapzap.biz folder and so on ;where database name are same as those of folder names in my source servers, what i mean to say is that I have the databases in my source machines whose name are not completly associated with their data and log files.Now when i create attach scripts from this source server and execute these in new server , the name of databases becomes different as sql server automatically make the name of database same as that of .mdf file.This will create hassle to all the users who will be connected after migration.
Can any one suggest how to rename all the databases in new server same as those on soure server.I have more than 100 such dbs.
You can specify the database name when using sp_attach_db. It doesn’t have to be the same as your mdf file names. Some scripts you can use when migrating a server, including scripts to generate attach scripts:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=4529
I have attach db scripts with me, but before executing I have to manually change the db name for each and every db.this is time consuming and error prone approach .I am wondering how to make the process automated.
To generate attach scripts that are identical to the source server when it comes to name and paths use this script: –sp_attach_db for all databases, using existing path of DBs, handles multiple filegroups SET NOCOUNT ON DECLARE @dbname SYSNAME
DECLARE @filelist varchar(8000)
DECLARE @filecmd varchar(8000)
DECLARE @filecount INT
DECLARE @filenbr INT
DECLARE @fileid SMALLINT
DECLARE @filename NVARCHAR(260) SELECT name
INTO #databases
FROM master.dbo.sysdatabases
WHERE DBID > 4
ORDER BY name CREATE TABLE ##files
(
fileid SMALLINT NOT NULL,
[filename] NVARCHAR(260) NOT NULL
) WHILE (SELECT COUNT(*) FROM #databases) <> 0
BEGIN SELECT TOP 1 @dbname = [name]
FROM #databases
ORDER BY [name] SELECT @filecmd = ‘INSERT INTO ##files SELECT fileid, rtrim([filename]) FROM ‘ + @dbname + ‘.dbo.sysfiles’
EXEC (@filecmd) SELECT @filecount = COUNT(*) FROM ##files SET @filenbr = 1 SELECT @filelist = ” WHILE @filenbr <= @filecount
BEGIN
SELECT @fileid = fileid, @filename = [filename] FROM ##files ORDER BY fileid DESC SELECT @filelist = @filelist + ‘@filename’ + CAST(@filenbr AS VARCHAR(3)) + ‘ = N”’ + @filename + ”” IF @filenbr = @filecount –last file, no comma
BEGIN
SELECT @filelist = @filelist + CHAR(13) + CHAR(10)
END
ELSE
BEGIN
SELECT @filelist = @filelist + ‘,’ + CHAR(13) + CHAR(10)
END SELECT @filenbr = @filenbr + 1 DELETE FROM ##files
WHERE fileid = @fileid END –print attach statment for current db
PRINT ‘sp_attach_db @dbName = N”’ + @dbname + ”’,’ + CHAR(13) + CHAR(10) + @filelist + ‘GO’ + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) DELETE FROM #databases
WHERE name = @dbname
END DROP TABLE ##files
DROP TABLE #databases
–Result
sp_attach_db @dbName = N’Northwind_test’,
@filename1 = N’D:MSSQLdata
orthwnd_test.mdf’,
@filename2 = N’D:MSSQLdata
orthwnd_test.ldf’
@filename3 = N’E:MSSQLdata
orthwnd_test.ndf’
GO
sp_attach_db @dbName = N’pubs’,
@filename1 = N’D:MSSQLdatapubs.mdf’,
@filename2 = N’D:MSSQLdatapubs_log.ldf’
GO
Yes ,I too have written similar script and tried your script,but is not generating the desired output.Database name in soure server is the inital name(before period) of the folder where its respective mdf and ldf file exist.Each and every database has a seperate folder for itself where its mdf and ldf file exist.I need to know how to attach all these databases on the destinaton so the db name remains same.
I don’t understand the issue. Can’t you just replace the file paths in the output from the script if you use other drives or directories on the new server? Or what is different on the other server?
I can change the db name in the attach scripts manually..but it is a time consuming process, as 100+ dbs exist in source machine.Manually changing the name of db is also error prone.
I still don’t understand the issue. Didn’t you want to keep the database names the same? Today you have: Database name: people
Data folder: X:people.comp2p_data.mdf
Log Folder: X:people.comp2p_log.ldf Database name: yap
Data folder: X:yap.bizyap_data.mdf
Log Folder: X:yab.bizyap_log.ldf How do you want it on the new server?
I want that the name of db should be same as that of old server.That is if the db name is "people" in old server it should not become "p2p" on the new server.It should remain the same as "people".When I am executing the attach scripts on new server ..the name of db is changing to "p2p".Pls suggest,this is the problem with most the dbs on the server.
I don’t see why it’s changing since sp_attach_db takes a dbname as a parameter. sp_attach_db @dbName = N’pubs’,@filename1 = N’D:MSSQLdatapubs_test.mdf’,@filename2 = N’D:MSSQLdatapubs__test_log.ldf’
Actually when i generate the attachdb scripts for 100+ dbs it become difficult to change the name of db in these scripts.
The script I posted will generate a script that include the old database names. There is no need to change anything.
]]>