SQL Server Manages Files???? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL Server Manages Files????

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-17 : 15:08:03

OK, Never been here before, becuase mostly I screwed up.

I thoughth when you set up the box you had a place to define where the default Paths of the database would be. NOrmally on my D: Drive (C: fpr O/S)

So..I have all of the dbs on c...and, yup, I'm running out of space.

So I use Tara' Admin Backup sproc (THANK YOU SO VERY MUCH), and then I started to do the restores over to D

No problems...piece of cake.

Now the Question.

I couldn't believe it, but I found the mdf and ldf files where gone off the C:\...I thought I goofed, and did a couple of more...and yup, they're gone.

Now I don't know if the C and D are logical drive or physical, and the restores where fast.

Did SQL Move the files? In this case just the pointers?

That was cool, if it's true.

Anyone know (but, of course)

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2005-03-17 : 15:22:17

So ... have I got this right? You did a RESTORE along the lines of:


RESTORE DATABASE MyDatabase
	FROM DISK = 'x:\SomePath\MyDatabase.BAK'
	WITH
		REPLACE,
		RECOVERY,
	MOVE 'MyDatabase_data' TO 'D:\MSSQL\DATA\MyDatabase.mdf', 
	MOVE 'MyDatabase_log' TO 'D:\MSSQL\DATA\MyDatabase.ldf'

in which case the MDF file will now be on D: - and Yes, I believe the old one on C: will have been deleted.

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-18 : 09:03:36

No RECOVERY... I did...


RESTORE FILELISTONLY FROM DISK='D:\MSSQL\BACKUP\ABAnalyzer\ABAnalyzer_20050317140343.BAK'

ALTER DATABASE ABAnalyzer SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE ABAnalyzer
   FROM DISK = 'D:\MSSQL\BACKUP\ABAnalyzer\ABAnalyzer_20050317140343.BAK'
   WITH   MOVE 'ABAnalyzer_Data' TO 'D:\Database\PAERSCBVD0014\Default\ABAnalyzer_Data.MDF' 
        , MOVE 'ABAnalyzer_Log'  TO 'D:\Database\PAERSCBVD0014\Default\ABAnalyzer_Log.LDF'
	, REPLACE

ALTER DATABASE ABAnalyzer SET READ_WRITE

Where the files were dumped to D:\ using Tara's script....

The files on C:\ were moved I believe...I never have seen this behave before...and confirmed it.

I have a few more to do, so I can reconfirm....

Nobody has seen this before?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-18 : 09:06:07

quote:
Originally posted by Kristen

and Yes, I believe the old one on C: will have been deleted.

Why would they...they never have before...I would restore a dump to many differently named db's on the same server for developers...they never got deleted, and why should they...

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-18 : 12:23:36

If you restore over a database that exists, it deletes the MDF and LDF files of the existing database.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-18 : 13:42:19

Well I guess that was a stupid question....Thanks

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-18 : 13:43:41

The stupid question is the one that hasn't been asked. Isn't that someone's signature here?

Tara