Tuesday, March 27, 2012

attach database issue

Hi,
I have got database files from sql server 2000 which i'm trying to
attach to sql server 2005 database. During the attach process, I'm
getting the following erros:
EXEC sp_attach_db @.dbname = N'Dev',
@.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
@.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log
.LDF';
Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Dev'. CREATE DATABASE is aborted.
Any idea what this refers to?
Regards,
D2D2,
How about trying to do same thing in SSMS attach menu?
and I found similar SQL in BOL :
CREATE DATABASE pubs ON PRIMARY
(FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs.mdf')
LOG ON (FILENAME =
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\pubs_log.ldf')
FOR ATTACH;
GO
try it
+--
Kenial.GhostOnNetwork.
D2 wrote:
> Hi,
> I have got database files from sql server 2000 which i'm trying to
> attach to sql server 2005 database. During the attach process, I'm
> getting the following erros:
> EXEC sp_attach_db @.dbname = N'Dev',
> @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
> @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log
.LDF';
>
> Msg 601, Level 12, State 3, Line 1
> Could not continue scan with NOLOCK due to data movement.
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'Dev'. CREATE DATABASE is aborted.
> Any idea what this refers to?
> Regards,
> D2
>|||D2
This example I took from the BOL. Try it .
USE master;
GO
sp_detach_db Archive;
GO
-- Get the SQL Server data path
DECLARE @.data_path nvarchar(256);
SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
-- Execute CREATE DATABASE FOR ATTACH statement
EXEC ('CREATE DATABASE Archive
ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
FOR ATTACH');
GO
"D2" <dhapola@.yahoo.com> wrote in message
news:1182324219.833398.13530@.q19g2000prn.googlegroups.com...
> Hi,
> I have got database files from sql server 2000 which i'm trying to
> attach to sql server 2005 database. During the attach process, I'm
> getting the following erros:
> EXEC sp_attach_db @.dbname = N'Dev',
> @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
> @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log
.LDF';
>
> Msg 601, Level 12, State 3, Line 1
> Could not continue scan with NOLOCK due to data movement.
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'Dev'. CREATE DATABASE is aborted.
> Any idea what this refers to?
> Regards,
> D2
>|||Tried the attach from menu before writing queries :-)
thanks for your time.
On Jun 20, 2:04 pm, Kenial <ken...@.shinbiro.com.korea> wrote:[vbcol=seagreen]
> D2,
> How about trying to do same thing in SSMS attach menu?
> and I found similar SQL in BOL :
> CREATE DATABASE pubs ON PRIMARY
> (FILENAME =
> 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs.mdf'
)
> LOG ON (FILENAME =
> 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\pubs_log.ldf')
> FOR ATTACH;
> GO
> try it
> +--
> Kenial.GhostOnNetwork.
> D2 wrote:
>
>
>
>
>|||Already tried that; BOL query is only trying to dynamically attach the
folder path.
sp_attach_db sp is internally executing the same query.
thanks for your time.
On Jun 20, 2:03 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> D2
> This example I took from the BOL. Try it .
> USE master;
> GO
> sp_detach_db Archive;
> GO
> -- Get the SQL Server data path
> DECLARE @.data_path nvarchar(256);
> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
> FROM master.sys.master_files
> WHERE database_id = 1 AND file_id = 1);
> -- Execute CREATE DATABASE FOR ATTACH statement
> EXEC ('CREATE DATABASE Archive
> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
> FOR ATTACH');
> GO"D2" <dhap...@.yahoo.com> wrote in message
> news:1182324219.833398.13530@.q19g2000prn.googlegroups.com...
>
>
>
>
>
>|||D2
Do you have any open transactions? Any other actvities while the attach
process is running?
"D2" <dhapola@.yahoo.com> wrote in message
news:1182332774.416879.280860@.j4g2000prf.googlegroups.com...
> Already tried that; BOL query is only trying to dynamically attach the
> folder path.
> sp_attach_db sp is internally executing the same query.
> thanks for your time.
> On Jun 20, 2:03 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>|||On this particular database: no. i'm trying to attach these files to a
new database.
However the same sql server instance is hosting many other databases,
in any of those there may be open transaction.
On Jun 20, 3:29 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> D2
> Do you have any open transactions? Any other actvities while the attach
> process is running?
> "D2" <dhap...@.yahoo.com> wrote in message
> news:1182332774.416879.280860@.j4g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>|||D2
Are you saying that you created a new database called 'Dev' and then run the
following script?
EXEC sp_attach_db @.dbname = N'Dev',
@.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
@.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log
.LDF';
sp_attach_db is depricated by MS and will be removed in the future.
I used CREATE DATABASE ...FOR ATTCH option and it worked just fine.
Try moving those files to another location and try execute.
"D2" <dhapola@.yahoo.com> wrote in message
news:1182336562.364728.71990@.i13g2000prf.googlegroups.com...
> On this particular database: no. i'm trying to attach these files to a
> new database.
> However the same sql server instance is hosting many other databases,
> in any of those there may be open transaction.
> On Jun 20, 3:29 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>|||No. What I mean is.. while executing these sql statements, a new
database called Dev is being created having filegroups set to the mdf
and ldf files ginve in the filename parameters.
I have done attach/detach many times.. both by writng query and using
ssdm menus. Only this time I'm getting some problem.
On Jun 20, 4:22 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> D2
> Are you saying that you created a new database called 'Dev' and then run t
he
> following script?
> EXEC sp_attach_db @.dbname = N'Dev',
> @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
> @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log
.LDF';
> sp_attach_db is depricated by MS and will be removed in the future.
> I used CREATE DATABASE ...FOR ATTCH option and it worked just fine.
> Try moving those files to another location and try execute.
> "D2" <dhap...@.yahoo.com> wrote in message
> news:1182336562.364728.71990@.i13g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>|||D2
Okay, do you have a backup of the database. Try using RESTORE coomand
instead of attaching the files.
"D2" <dhapola@.yahoo.com> wrote in message
news:1182342259.453149.68560@.i38g2000prf.googlegroups.com...
> No. What I mean is.. while executing these sql statements, a new
> database called Dev is being created having filegroups set to the mdf
> and ldf files ginve in the filename parameters.
> I have done attach/detach many times.. both by writng query and using
> ssdm menus. Only this time I'm getting some problem.
> On Jun 20, 4:22 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>sql

No comments:

Post a Comment