Cannot locate database backups

[TimeTraveler]

Missing backup file...

Whether right-mousing in Enterprise Manager and selecting 'All Task --> Backup Database...' or doing the 'Back Database' Wizard, the backup file itself doesn't seem to exist afterward. I've checked c:\MSSQL\Backup, C:\Backup, and did a systemwide search - and still no file.

The progress meter and resulting success alert message indicate the machine was actually doing something.

System: Windows Server 2003, Windows SQL Server 2003, Enterprise Manager 8.0.

Thanks in advance for any insight.

-Sean

 

[ptheriault]

There is no product named Windows SQL Server 2003.

If you want to know you SQL version run
SELECT @@version from Query Analyzer.

Have you checked.

c:\program files\microsoft sql server\mssql\backup
if it is a named instance

c:\program files\microsoft sql server\mssql$YOURSERVER\backup

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[onpnt]

Check the default location for backing up to disk

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\

Did you create a backup device?

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[ptheriault]

If forgot to mention that 'C:' will be whatever your default install directory is.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[TimeTraveler]

Thanks guys - I'll check.

Version: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

I'm an app guy that walked into a mess of machines, processes and policys and now playing janitor to a bunch of Microsoft stuff I barely understand. For instance the Beta machine had only 400MB left of HD space, and the common fileserver only had 6GB total, only 35MB available.
Nothing's documented - no runflows, see-alsos or anything like what I'm used to in larger companies.

I've check all around, including under Program Files, and cannot find the file.

My question, is that the dialogs asked me for the full path to the file, which I gave, and yet after it was done, the file was not present, not even an empty one. If it was going to put it wherever why did MMC bother asking me in the first place? How hard is it to stick a file in C:\MSSQL\Backup\someBackUp.bak?

The only thing I'm guessing is that when I did a systemwide search, I was looking for a file called some*.bak, when maybe I should look for some*.* in case MS mangled my filename.

 

[ptheriault]

Backup files aren't always named *.bak If you know what you named your backup search for the name without the extention.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[TimeTraveler]

Well, I've been doing every single possible combination of selections in the 'Backup Database...' dialog, and looking at the properties of C:\, the files and freespace didn't budge, even though EM reports the backup was completed successfully.

I'm supposed to have this database snapshot pulled into another server by noon tomorrow. I've been working on this since 11:30 this morning.

If someone could give me the definitive runflow for making a backup of a database within MS SQL Server, so I can FTP the file to another server to load in, that would be great.

It seems like this should be much simpler than what I'm experiencing.

Sean

 

[ptheriault]

Ok, lets try this. Open Enterprise Manager.
Drill down into the Management\backup folder
Right click on the Backup Icon.
Select Backup a Database
You will get a new window with 2 tabs General and Options
on the General tab
Select your database
Give your backup a name and description
Select the type of backup (Most likely Database - complete)
In the destination section click remove.
Now click Add
You'll get a new pop-up window.
Select Backup Device
Click the drop down and select new device
Type the name of the device
change the path. (write the path down so you don't loose it)
Click OK
CLick OK again
This brings you back to the main Backup window
Add a schedule (This will create a job)
Click ok
Now go to the SQL Server Agent jobs and run your new backup job.
When the job completes go check for your backup device at the location you saved it to.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[TimeTraveler]

Thanks for the runflow. My process mirror your runflow exactly.

I must thank you for your response. I learned since my prior posting that there's actually TWO servers - a web server and db server, and that the db server is behind a firewall at another IP.

Now, I don't understand why the option to backup my database wasn't grey'd out if not available.

But, the good news is that I was able to get a back up to use to make a copy on a development box and away we go.

By way of a tip to offer in return: I learned that Enterprise Manager cannot rename a database. The trick is to set the options of the database to single-user, back out of EM, load up Query Analyzer (QA), and run a stored procedure. E.g., 'sp_renamedb (dbname), (newname)'.

Thanks again for you help.

Sincerely,

Sean/TimeTraveler

 

[ptheriault]

Np Sean. I'm glad you got it to work.


- Paul
- If at first you don't succeed, find out if the loser gets anything.