Moving a DB.

[willydude]

I copied my Payroll DB project to a flash drive. I believe the Payroll.mdf file should be in: “F:\Microsoft SQL Server\MSSQL.1\MSSQL\Data”, but there is nothing after \Data.

I did a search on the F: drive for *.mdf and found the Northwind, Pubs, master, msdbdata and many other files, but no Payroll. I am afraid to open one of the master or other system files to look. Plus the modified dates on these files are 2-3 years old.

Is my Payroll DB buried in one of the above files?

I am wanting to put my Payroll DB back onto my hard drive so I can continue to work on it. My PC crashed over the weekend.

I did find some info on the MS support site at:

http://support.microsoft.com/kb/224071

(“Moving user databases”)

Is the support info what I need to copy the Payroll DB (if it’s on F to my harddrive? I do not want to make things worse.

Thanks.

Bill

 

[ca8msm]

I copied my Payroll DB project to a flash drive. I believe the Payroll.mdf file should be in: “F:\Microsoft SQL Server\MSSQL.1\MSSQL\Data”, but there is nothing after \Data.

How did you copy it? If you copied it in windows explorer, and it now isn't there, you either deleted it or didn't copy it properly.

You could always retrieve it from one of your backups...you do have a backup don't you?



-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[HarleyQuinn]

you do have a backup don't you?

Hopefully the flash drive wasn't it...

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[willydude]

I dragged and dropped everything (obviously not everything) that I saw that was related to SQL. Where specifically is a user created DB stored?

Also, how/where do I store my stored procedures that I create. F5 runs them, but when I go to save the sproc, I get directed to "MyDocuments\SQL Server Management Studio Express\Projects. But this is outside of my Payroll DB, I need them stored inside the DB.

I have verified the "not saving" of my sprocs by looking thru Object Explorer in Mgnt Studio under Databases/Payroll/Programability/Stored Procedures. My sprocs are not showing up, even after Refresh.

I have all my sprocs on the F: drive and can get them to open and run, but they will not save inside my Payroll DB. Even if I copy and paste them to a new
stored procedure.

What am I missing?

Thanks,

Bill

 

[willydude]

As to the saving of stored procedures issue, they would not save b/c they are "Alters", not "Creates."
They were being "re-introduced" to the DB. DUH!

I still am very curious about where my DB files are saved.

Sorry and thanks,

Bill

 

[JoeAtWork]

What you "saved" was a script for creating the stored proc. To actually create the stored procedure, run the script (and make sure you are applying it to the correct database, i.e. USE [myDatabseName]).

Everything is saved in the MDF or LDF file. I doubt that dragging and dropping these files will work while the database is online - you would need to detach them first.

At this point, I would highly recommend getting a good introductory book on SQL Server to get an overview, as it will take you forever to learn these foundamental concepts one Tek-Tips post at a time.

 

[JackVam]

If you need to see the path of the MDF/LDF files , do a :
USE Master
SELECT name,filename FROM sysdatabases

Jack Vamvas

All the IT jobs in one place -

http://www.ITjobfeed.com

 

[mrdenny]

I doubt that dragging and dropping these files will work while the database is online - you would need to detach them first.

You are very correct. If the SQL Server service was running when you tried to drag the files to the USB drive then the data files would not have been copied. The databases would have needed to have been detached or the SQL Service would have needed to have been stopped for you to back them up in this method.

If you do not have the mdf and ldf of the Payroll database, and you don't have a backup file of the Payroll database (taken with the BACKUP DATABASE command) then you don't have the Payroll database anymore.

As for the stored procedures, when you execute the CREATE PROCEDURE script and no error is returned then the stored procedure is then loaded into the SQL Server. It will be in what ever database you create it in. It's common to drop them into the wrong database because there is no USE statement at the begining of the file.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[JoeAtWork]

I'm guessing your stored procedure ended up in the "master" database. I know that's where mine end up when I'm not paying attention

 

[bboffin]

It looks like you don't have what you need on the Flash drive but don't despair just yet.

You say that your PC crashed but don't indicate what that means. Unless you have suffered a major hard disk crash that has physically damaged the drive the chances are that it will still be usable on another PC. Get your IT support to plug it into another PC as a slave drive and see if you can recover the MDF/LDF files. By default these tend to end up in the Data folder of the SQL Server instance that you have installed so start at C:\Program Files and work your way down or just use Search to find all MDF/LDF files on the drive. This time you should be able to copy the ones you need to the Flash drive.


Bob Boffin