You can use SQL Server Configuration Manager to check it, but I just pop open the Services applet in Admin Tools.
the only difference is that the original server has the instance of sql "sqlexpresssome"
although it isn't running, so I dont think that would be causing the issue
An extra instance won't matter. The key is the error you received:
S:\DoNotDelete\LexisNexisMainDB\MSSQL11.LEXISNEXISMAINDB\MSSQL\DATA\mastlog.ldf for file number 2. OS error: 5(Access is denied.)."
If I could reattach all the databases in mass quantity would be the simplest way at this point since I'm really not sure what I'm looking at, all the permissions look the same, the domain is the same, the accounts within sql are the same, the directories are the same, the build and version are the same, although when I copy and overwrite all files from current sql the new box's sql service doesn't start, when I copied the original data folder it fixed the problem, then when I tried to recopy everything back again the service wouldn't start back up regardless if I copied the original data folder back. The error remained the same for why it wasn't able to start.
I tried a repair and that wouldn't solve my issue so again I did a full reinstall.
btw, I'm now on an actual box which I have installed server 2008 same as original box and have duplicated the dns/ad
Untested, but here you go. Note that you'll need to copy the output into a query window and run it. This only produces the commands, doesn't run them.
declare @path varchar(500), @cmd varchar(1000)
set @path = 'C:\MSSQL\Data'
set @cmd = 'dir ' + @path + '*.mdf /B'
create table #t (output varchar(500))
insert into #t
exec master.dbo.xp_cmdshell @cmd
select 'exec sp_attach_single_file_db @dbname = ''' + left(output, charindex('.', output)-1) + ''', @physname = ''' + @path + output + ''''
from #t
where output is not null
drop table #t
All I change in this is the @Path = 'C:~~~~~\data' to my data folder path correct? or is there anything more I'm to change? my error consists of "exec sp_attach_db File Not Found"
In the message tab it states:
"Msg 537, level 16, State 2, Line 16
Invalid length parameter passed to the LEFT or SUBSTRING function."
which would be the line below
select 'exec sp_attach_single_file_db @dbname = ''' + left(output, charindex('.', output)-1) + ''', @physname = ''' + @path + output + ''''
It's because you have an extra dot in your path that my code isn't expecting. You probably can use charindex('.mdf') instead.
By the way, my code only needs the mdfs. If you have the ldfs in that same directory, you'll want to delete them or you'll have to handle modifying my code to handle both files. You would need to use sp_attach_db instead in that case.
If you use my code, delete the ldfs in that directory. It'll create them for you. Please don't delete them on the prod server. This is to be done on the new server ONLY.
Use at your own risk. Untested.
This is the path to my data folder: S:\DoNotDelete\LexisNexisMainDB\MSSQL11.LEXISNEXISLAWSVR\MSSQL\DATA
I'm unsure of what you're referring to by the extra dot in my path as well as using charindex('.mdf)
other than attaching the databases in mass quantity I've tested by attaching a few database and was able to access everything without error.
My code is breaking because I didn't anticipate MSSQL.11 in your path. That dot is what broke my code.I don't have time to rewrite it, so try using "charindex('.mdf'" in my code instead of "charindex('.',". Be sure to remove the ldfs as my code doesn't handle that either. If you want to use those ldfs, you'll need to add that functionality to my code.
this time I got
Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
It means you aren't connected to SQL Server.
If your system databases are in that folder, you're going to want to exclude them from the list as they are already attached. Whatever databases are already attached need to be excluded. You can do that in the WHERE clause.
I just realized I left in sp_attach_db in the code so I edited that post to remove it. I decided on sp_attach_single_file_db to save me time and not have to deal with the ldfs.
i get the same error as I did the first time I tried running your code before adjusting it and adding the mdf
It's working fine for me. I even created a directory with a dot in it so that I could match your environment.
Show me the exact code you are running and the error.
Hi guys, new here. Was just reading your posts. Came across them in a Google search. Just wanted to let you know that your posts caught my interest enough to sign up. Miss Tara seems to know her stuff! I'm jealous! I recently had to move hosts with our site at work with absolutely no login or User info because our Web Master has been 'unavailable'. I first tried restoring backups to the new server which worked fine except absolutely nothing connected to the database would work afterwards because of permissions and users and the like. Next I tried running the script generator to recreate the database independently. It worked very very well however I still had to recreate the logins. I'm not sure if you can do multiple databases at the same time though. That is a very wierd way to set a system up as Tara has already mentioned. Anyways, thank you both for the interesting thread. I will keep reading.
2 Likes
Here is a copy of what I have entered, as well as a screenshot to show the code along with the error.
declare @path varchar(500), @cmd varchar(1000)
set @path = 'S:\DoNotDelete\LexisNexisMainDB\MSSQL11.LEXISNEXISMAINDB\MSSQL\DATA'
set @cmd = 'dir ' + @path + '*.mdf /B'
create table #t (output varchar(500))
insert into #t
exec master.dbo.xp_cmdshell @cmd
select 'exec sp_attach_single_file_db @dbname = ''' + left(output, charindex('.mdf', output)-1) + ''', @physname = ''' + @path + output + ''''
from #t
where output is not null
drop table #t
p.s. hi simms
It's because you didn't specify the ending slash for @path. This was just quick code and didn't handle that situation.
set @path = 'S:\DoNotDelete\LexisNexisMainDB\MSSQL11.LEXISNEXISMAINDB\MSSQL\DATA'
Wow... My only task was to enter the path correctly..... So this produces all the commands for me to attach all of these databases? I'm assuming I copy paste the message field into a new query and execute
Yes and yes. 