Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

retrieve sql database stored procedures

Posted on 2010-08-18
10
Medium Priority
?
617 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
 
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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
A procedure for exporting installed hotfix details of remote computers using powershell
This tutorial will show how to push an installation of Backup Exec to an additional server in both 2012 and 2014 versions of the software. Click on the Backup Exec button in the upper left corner. From here, select Installation and Licensing, then I…
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…

877 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