April 27, 2013 at 2:10 am
Can I rename the physical name of both mdf and ldf files of system databases such as msdb,model and master?
April 27, 2013 at 3:12 am
IT researcher (4/27/2013)
Can I rename the physical name of both mdf and ldf files of system databases such as msdb,model and master?
Without breaking anything?? I very much doubt it.
Why do you want to rename only some of the system databases files in the first place?
April 27, 2013 at 3:49 am
Steve JP (4/27/2013)
IT researcher (4/27/2013)
Can I rename the physical name of both mdf and ldf files of system databases such as msdb,model and master?Without breaking anything?? I very much doubt it.
Why do you want to rename only some of the system databases files in the first place?
+1 million.
I would not be surprised if the next patch failed spectacularly
---------------------------------------------------------------------
April 27, 2013 at 4:37 am
I am changing file name of mdf and ldf file not database name. Does changing the MSDBData.mdf to MSDB.mdf cause problem?
April 27, 2013 at 4:43 am
yes we got that. But why do you want to change the names of the physical files????
April 27, 2013 at 6:18 am
I cannot think of any good reason to change system database file names and cannot guarantee it won't cause a problem either now or with some future change MS makes that makes an assumption about system database file names.
---------------------------------------------------------------------
April 27, 2013 at 10:51 am
I'd put money on it breaking future updates, considering how SQL 2005 would fail to install some updates if you simply moved the resources db to a different physical location than the master database.
April 28, 2013 at 10:48 am
Since it is not advisable to change system dbs physical file name. If you want to do it for fun :hehe: then just use the below command:
alter database master modify file(name=<logical_name>,filename='<physical filename along with its path>')
And then you need to stop SQL services,change the file name at its physical position and then again start SQL services.
note: for master you need to change physical file name in start up parameter also.
🙂
April 28, 2013 at 2:17 pm
varunlpu,
to move master you only need to amend the startup parameters, stop SQL, copy files to new location, and restart SQL.
(and then cross fingers) 🙂
---------------------------------------------------------------------
April 29, 2013 at 12:57 am
I'm just curious to find what made you to think that renaming a system databases is option for you. 🙂
April 29, 2013 at 3:18 am
The only reason to rename the file is for automated backup. I do have my own backup application where I can automate backup on daily,weekly or monthly basis. I am using it from so long time. Now i have shifted the all system database from installation directory to non windows drive just for security and disaster recovery plan. Till now I did't have backup for system database and now I am planning to include to it. But the backup application requires the database name and it's mdf file name to be same.(In my case only msdb has different mdf name as MSDBData.mdf . So I needed to rename the file. I just tested renaming the file in a test server and it worked fine.
April 29, 2013 at 3:22 am
The only reason to rename the file is for automated backup. I do have my own backup application where I can automate backup on daily,weekly or monthly basis. I am using it from so long time. Now i have shifted the all system database from installation directory to non windows drive just for security and disaster recovery plan. Till now I did't have backup for system database and now I am planning to include to it. But the backup application requires the database name and it's mdf file name to be same.(In my case only msdb has different mdf name as MSDBData.mdf . So I needed to rename the file. I just tested renaming the file in a test server and it worked fine.
April 29, 2013 at 3:23 am
Sorry for the double post of same message.
April 29, 2013 at 3:36 am
The backup command does not require the filename so not sure why you need it. How are you doing your backups?
IF you want to get filenames extract them out of master.sys.master_files rather than hardcoding and making assumptions about the naming convention.
I would still have concerns about renaming system database files with future patches in mind even if SQL does run OK.
---------------------------------------------------------------------
April 29, 2013 at 12:20 pm
IT researcher (4/29/2013)
The only reason to rename the file is for automated backup. I do have my own backup application where I can automate backup on daily,weekly or monthly basis. I am using it from so long time. Now i have shifted the all system database from installation directory to non windows drive just for security and disaster recovery plan. Till now I did't have backup for system database and now I am planning to include to it. But the backup application requires the database name and it's mdf file name to be same.(In my case only msdb has different mdf name as MSDBData.mdf . So I needed to rename the file. I just tested renaming the file in a test server and it worked fine.
I would be very concerned about this automated backup process - since it obviously is not using native SQL or the published interface for interacting with SQL Server backups.
This sounds like some kind of file system backup utility - which will not work with SQL Server database files because they are locked by SQL Server.
If your process is actually taking SQL Server down for the backups, this is not a recommended method for backing up a database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply