June 22, 2004 at 8:01 am
I wrote a program that runs at month end, backs up the 'data' database under a different name and is supposed to attach it. The Attachdb part does not work. I have not been successful at automating that part. Can anybody help? I followed the syntax.
|
June 22, 2004 at 7:15 pm
You followed what syntax? You might want to post the code. Also, the account has to have sufficient priviliges to run sp_attach_db.
Only members of the sysadmin and dbcreator fixed server roles can execute this procedure.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
June 22, 2004 at 10:05 pm
Also, if you are backing it up, then attach doesn't work. You'd want to do a restore. Detach is the way to prepare the database for attach.
June 23, 2004 at 4:33 pm
If you wish to use (assuming you are using SQL2000) sp_attach_db, I suggest the following basic steps:
1) detach the db in question (lets say it is called db_Orig).
eg exec sp_detach_db 'db_orig'
2) copy the physical db files (the mdf and ldf) to new file names (lets call the new db 'db_new')
eg copy db_orig.mdf to db_new.mdf and copy db_orig.ldf to db_new.ldf
Assuming default installation, you can find the physical files in c:\mssql\data or you can check the sysfiles table for the physical file name and location (eg select filename from db_orig..sysfiles where name like 'db_orig%')
3) attach the db_new (the new db) using sp_attach_db
eg exec sp_attach_db @dbname=N'db_new',@filename1=N'c:\mssql\data\db_new.mdf',@filename2='c:\mssql\data\db_new.ldf'
That should work though you may wish to look at things such as updating statistics. Also, you wish to ensure the db_orig is in single user mode (eg exec sp_dboption 'db_orig', "single user","true" so that no one but you accessing it at time.
Hope this helps!
Serdal Dik
June 24, 2004 at 7:19 am
| I got it to work. I used sqldmo connection and attachdb. It only worked when I removed the .ldf file from the statement. It will only take the .mdf file?? |
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply