Following script can be used to detach or attach database. If database is to be from one database to another database following script can be used detach from old server and attach to new server.
Process to move database :
----Step 1 : Detach Database using following script
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks',
@keepfulltextindexfile = N'true'
GO
----Step 2 : Move Data files and Log files to new location
–---Step 3 : Attach Database using following script
USE [master]
GO
CREATE DATABASE [AdventureWorks] ON
( FILENAME = N'C:\Data\AdventureWorks_Data.mdf' ),
( FILENAME = N'C:\Data\AdventureWorks_Log.ldf' )
FOR ATTACH
GO
IF EXISTS ( SELECT name
FROM master.sys.databases sd
WHERE name = N'AdventureWorks'
AND SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [AdventureWorks].dbo.sp_changedbowner @loginame=N'sa',
@map=false
GOReference : Pinal Dave (http://www.SQLAuthority.com), SQL SERVER - 2005 Take Off Line or Detach Database
pinaldave
I backed up the database ‘City’ and user logins from one server and restore the database and user logins by another server. Everything was fine. I refreshed the database, but I still can’t login the database. How can I trouble-shoot the problem?
Hi Pinal,
I noticed that when a database is re-attached, it’s creation_date is updated to the date of attachment. Is there any way to change this?
Thanks
Lyn
Attach db query not working at all !!!
Do i have to create a new db and then run this attach query?
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘AdventureWorks’. CREATE DATABASE is aborted.
Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 19, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Hi,
This topic is very useful
Thank’s
Worked flawlessly, thanks for the script!
Apparently no one in this uinverse knows how to do this. On the same server, yes it work, but move to other server and you get: Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘AdventureWorks’. CREATE DATABASE is aborted.
Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 19, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
@Larry,
Chill Man !!!
I guess, this is what you did ?
1. You executed detached database statement ( sp_detach_db) on server1 and then
2. You logged in to Server2 ( different machine) and
3. There you are trying to attach the database by executing this statement ( sp_attach_db).
But my dear, you have also have to manually move those .mdf and .ldf files from old server to new server.
And while using sp_attach_db stored procedures, give the new location of these files ( where ever you moved it to, ex: D:\program files\ microsoft sql server\………)
Once you move ( copy those .mdf and .ldf files)to new server then use sp_attach_db with the new location ( on new server).
You should be able to attach it.
If you cannot do it through sql commands, let me know I will try to post screen shots for the same using interfaces.
Hope this helps,
Imran.
I used sp_attach like this and get the Msg 1813:
USE [master]
GO
sp_attach_db @dbname = N’TestDB’,
@FILENAME1 = N’C:\Data\TestDB.mdf’ ,
@FILENAME2 = N’C:\Data\TestDB_1.ldf’
What did I miss?
Helped me immensely for coming over issues with login after detaching a default DB of windows user. Alter login didn’t worked that time.