Renaming system databases

  • Can I rename the physical name of both mdf and ldf files of system databases such as msdb,model and master?

  • 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?

  • 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

    ---------------------------------------------------------------------

  • I am changing file name of mdf and ldf file not database name. Does changing the MSDBData.mdf to MSDB.mdf cause problem?

  • yes we got that. But why do you want to change the names of the physical files????

  • 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.

    ---------------------------------------------------------------------

  • 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.

  • 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.

    🙂

  • 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) 🙂

    ---------------------------------------------------------------------

  • I'm just curious to find what made you to think that renaming a system databases is option for you. 🙂

  • 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.

  • 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.

  • Sorry for the double post of same message.

  • 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.

    ---------------------------------------------------------------------

  • 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