Solved

retrieve sql database stored procedures

Posted on 2010-08-18
10
597 Views
Last Modified: 2012-05-10
tried to retrieve a sql server database using detach and attach.  got the database working but the stored procedures did not come across to the new database with the same names as the source database.
0
Comment
Question by:wuot
10 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 33472140
if you go for detach/attach, it will SURELY bring all SPs as it is. there may be some user or login issue with you.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33472143
>>got the database working but the stored procedures did not come across to the new database with the same names as the source database.

Ouch.. using detach, attach?
That is something really weird.... what exactly do you mean.; if you check the SSMS, database, Procedures catalog, they do not appear?  Have you realigned permissions if you moved to a new server - it could be a permission issue?
Do you mean their names changed (never seen it)?
0
 
LVL 17

Expert Comment

by:Shinesh Premrajan
ID: 33472163
Better option is to export and import the database as it retains all the constraints and the procedures in place.

Hope this helps
0
 
LVL 11

Expert Comment

by:slemmesmi
ID: 33472211
Or maybe simpler, on source databse to export the sql creation statements for the sp's, then run these on the target database...
0
 

Author Comment

by:wuot
ID: 33474082
export and import not working because ssms not working on the source server.  added / changed logins and can acces the new database.  a stored procedure name in the source database for example see old sql 05 screen for stored procedures compared with the current 08 screen.  
stored-procedures-schldr-08.JPG
stored-procedures-schldr-05.JPG
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33474174
All that looks like to me is that either
(1) you are comparing apples with oranges (not the same db); or
(2) an asp.net app has been installed against the database.  It introduces a whole lot of aspnet_* stored procedures.

Have you scrolled further down to see if your original procs are listed - they should be?
0
 

Author Comment

by:wuot
ID: 33475613
none of the old procs lised.  one more fact.  the old db had the name schldr1 as seen in the file mgr but schldr in ssms.  but when trying to attach to a newly created db named schldr got the error msg db already exists. so i set up a new db named tempdb then attached schldr1 to tempdb then imported the tempdb into the new schldr db.  i also wanted to get the db in the default location and the origingal attach left the db in the location of the soucrce file copied.  i think this cased the problem.  but how to do the attach so that the .mdf file ends up in the default sql directory and allows changing the name to schldr in ssms.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33480044
>  but when trying to attach to a newly created db named schldr got the error msg db already exists

Okay, so what you are saying is that you are comparing the "schldr" that ALREADY EXISTED against the "schldr" (file name schldr1) from the old server.  How do you expect them to be the same?

> so i set up a new db named tempdb then attached schldr1 to tempdb

You do know that "tempdb" is the system temporary workspace, so how exactly do you create a db named "tempdb" (exactly)?

Something is missing from this picture.

What you need to do is to forget what you did.  Delete/detach the current schldr db from the new server (if you really don't need it anymore), then move the files away (if detached).
Place the schldr1.mdf and .ldf files into the default sql directory.
Using SSMS, attach a db, find the schldr1.mdf file and name it as schldr (this works now that the other db is gone).
Finally, refresh the databases tree and you should see schldr with the procedures all intact.
0
 

Author Comment

by:wuot
ID: 33480217
yes i know this but as i stated previously.  the file name for schldr is schldr1 and the ssms name is schldr.  the db has two names when viewed in file mgr schldr1 when viewed in ssms schldr.  i do not know for sure how this coud be.  but we get an error msg db already exists when trying to attach, that canot be overcome directly in the ssms ui.  so somehow, we could change the name of schldr / schldr1(the source db)in order to attach as schldr or set up some kind of intermediary db then import or attach as (change name) schldr in the default sql data directory.  i don't remember exactly the temp db established previoulsy as an intrmediary but the process worked for geting the db across to an extent but not completely in that we did not get the user defined stored procedures.
0
 

Accepted Solution

by:
wuot earned 0 total points
ID: 33508026
1) When we tried to attach the database using “create database for attach“ we got below error :
File activation failure. The physical file name "D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\schldrlog_1.ldf" may be incorrect. Msg 5123, Level 16, State 1, Line 4 CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\schldr_log.LDF'.
Msg 1813, Level 16, State 2, Line 4
Could not open new database 'schldr'. CREATE DATABASE is aborted.

2)We found that SQL Server service account didn’t had required permissions on ldf file. So we gave full permissions on folder which contained ldf file to SQL Server service account. Then we were able to attach the database.
 
3) We also gave access to a login to attached database. We were getting execute denied permissions on a stored Procedure which we resolved by granting execute permissions.


RELATED KNOWLEDGE BASE ARTICLES
=================================
http://technet.microsoft.com/en-us/library/ms176061.aspx  --CREATE DATABASE FOR ATTACH

http://msdn.microsoft.com/en-us/library/ms188371.aspx -- GRANT Object Permissions
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now