CREATE DATABASE Adds extra backslash to Data File Path

Question

Post reply

CREATE DATABASE Adds extra backslash to Data File Path

Ben Blum

Right there with Babe

Points: 776
More actions
April 20, 2007 at 8:05 am

#177713
Hello All -
I have a strange problem that is causing headaches with our backup software (Backup Exec). It seem that when a database is created with a statement like so in Q.A.:
CREATE DATABASE Testbb
The path to the data files and log files includes and extra backslah before the file name. So this is what is written to sysdatabases and what show up in the database properties screen:
M:\MSSQL\Data\\testbb.mdf
If I create a database using E.M. the path is correct i.e. no double backslash.
The databases works fine however BackupExec will not back up any databse with the extra backslash.
So my question is three-fold:
1. why does this happen when I use the create database statement but not in E.M?
2. What do I need to change to make this work properly in the future?
3. What's the easiest, lowest risk method of fixing the existing database that have the two slashes in the paths to the data/log files so I can get BackupExec to play nice again?
Thanks in advance for any help you can give me, even if only to help with the third question.

Ben

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

under SQL Query Analyzer, run

exec masteer.dbo.sp_helpfile

In the result, does the file name column look like this(two slashes) ?

M:\MSSQL\Data\\master.mdf

If so, then when SQL server was installed, the directory for the system databases was specified with double slashes. Easiest fix may be to un-install and then reinstall SQL Server. The alternative is to follow the complex instructions on moving system databases located at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071

SQL = Scarcely Qualifies as a Language

george sibbald SSC Guru Points: 104210 More actions · Answer 2

are the default database file locations in EM defined with a \ at the end? if so , remove it

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

Ben Blum Right there with Babe Points: 776 More actions · Answer 3

Yes, the filepath column has the double slash.

I'm not up for the reinstall project, rather live with the minor problem and fix as needed. BTW - the easy fix is just to detach and reattach each DB.

Ben Blum Right there with Babe Points: 776 More actions · Answer 4

They originally were defined with the slashes at the end but I changed it and it still behaves the same.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

It still behaves the same because the databases already exist (they were created with the extra slash in th path). If you create new databases they will not have the double slash issue. In order to get around the issue on dataases that currently exist you have t different methods. For user databases a simple detach and attach with the correct pathname (no double slash). For system databases the process is more involved and you have 2 steps and SQL Server down time IS REQUIRED. The first stepis basically detach/attahc of msdb and the model databases - there is a KB article on moving these databases - http://support.microsoft.com/kb/224071/. The change for the master database is all in the registry. Use regedit and modify HKLM\Software\Microsoft\SQL Server\MSSQLServer\Parameters and change Arg0 and Arg2 (the master .mdf file path and master .ldf file path).

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Ben Blum Right there with Babe Points: 776 More actions · Answer 6

Thank you Rudy, what I meant when I said it behaves the same is that after I changed the default database settings in EM to:

Default Data Directory: M:\MSSQL\Data (removed extra slash at the end)

Defalt Log Directory: L:\MSSQL\LOG

When I run something like 'create database bbTest' in QA the entry created in sysdatabases still has the double slashes. I have not had a chance to reboot or restart the sql service so that may be required before the change actually becomes effective. We only take this server down a about six times a year so I can't really test that out very easilly.

Thankfully the master, msdb, etc were somehow not affected by this so I won't haveto mess areound with the special process you mentioned. The only other DB's I'm worried about detaching and reattching is ReportServer and ReportServerTempDB. Do you know if special techniques are required for the reporting service db's or can I comfortably detach-reattach without screwing up my reportserver environment?

Thanks again for your help,

Ben

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

Just shut down SQL Reporting Services ... after this is complete then those databases can be treated just as 'user' databases'.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

btio_3000 SSCrazy Points: 2662 More actions · Answer 8

Same problem here, but after executing exec master.dbo.sp_helpfile I see no double backslashes and CREATE DATABASE still does it with \\ in front of db name

Any advise? Thanks!

P.S. Oh yeah, and no backslash in Default Directories BTW ))))

btio_3000 SSCrazy Points: 2662 More actions · Answer 9

btio_3000

SSCrazy

Points: 2662

April 27, 2007 at 2:11 pm

#703055

Rebooting Server did the trick

Thanks!