April 1, 2009 at 12:20 pm
I inheritied a sql database and I know nothing about SQL. Its SQL 2000 Server.
The database is running and not marked suspect or damaged that I can see in Enterprise Manager.
The SQL folder has a 2 Gb mdf file and a 12 Gb ldf file.
When I try to back the database from within Enterprise manager it fails saying it can't find a TRN file with a path to a drive that doesn't exist.
How can I back up this database?
April 1, 2009 at 12:59 pm
BACKUP DATABASE 'database-name'
TO DISK='your file path'
trry this and let us know
April 1, 2009 at 1:26 pm
OK I ran this
BACKUP DATABASE 'mydatabase' TO DISK='E:\SQL Backup'
and I get his response
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'mydatabase'.
April 1, 2009 at 1:53 pm
No quotes.
BACKUP DATABASE MyDatabase to DISK = 'E:\SQL Backup'
April 1, 2009 at 2:03 pm
Edd (4/1/2009)
OK I ran thisBACKUP DATABASE 'mydatabase' TO DISK='E:\SQL Backup'
and I get his response
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'mydatabase'.
oops sorry that was my mistake I just wanted to hightlight that you need to include your database name.
BACKUP DATABASE yourdatabasename
FROM DISK='.bak file path'
April 1, 2009 at 2:04 pm
That gets me here,
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'E:\SQL Backup'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
The log shows
date time backup BACKUP failed to complete the command BACKUP DATABASE mydatabase TO DISK='
date time spid61 BackupDiskFile::CreateMedia: Backup Device'E:\SQL Backup' failed to create. Op
The drive is online and I have no problems accessing it or writing to it otherwise?????
April 1, 2009 at 2:06 pm
I gave the backup location a filename and the bak extention. Is seems to be running. At least I have no error message, yet!
April 1, 2009 at 2:13 pm
Have you go the folder created? You should create the folder and create hte .bk file
For example if your database name is 'Mydatabase'and you want to back it up to the folder SQL Backup on E drive
Then you would have to write:
BACKUP DATABASE Mydatabase
TO DISK='E:\SQL Backup\Mydatabase.bak'
[\code]
You can specify any extension to the backup file but this is the normal way and you can name it in anyway.
April 1, 2009 at 2:22 pm
And now that you have a backup running, you need to follow up and perform a transaction log backup. I am going to guess that your database is set to full recovery model (with a 12GB transaction log for a 2GB database, it is a pretty good guess).
I am also going to guess that you do not have regular transaction log backups scheduled, which are required when the database is in full recovery model. This is why the transaction log is so large.
Please read the article I link to in my signature about managing transaction logs. That article will outline the choices you need to make going forward. If you have any questions, post back here and somebody will be sure to help you out.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 1, 2009 at 2:25 pm
Edd (4/1/2009)
I gave the backup location a filename and the bak extention. Is seems to be running. At least I have no error message, yet!
That's good enough!
April 1, 2009 at 2:26 pm
Krishna Potlakayala
I thank you very much. I'll feel a lot better after I have a good backup of the database. You got me close enough to figure it out. Thanks again.
I'm waiting now for it to finish.
April 1, 2009 at 2:31 pm
Edd (4/1/2009)
Krishna PotlakayalaI thank you very much. I'll feel a lot better after I have a good backup of the database. You got me close enough to figure it out. Thanks again.
I'm waiting now for it to finish.
Welcome 🙂
But you need to do lots to get this going smoothly. What is your backup strategy? Is this your production ? and about you transaction log size too as Jeffrey mentioned it's huge you need to back it up your log too to truncate it.
Go through the article that has been mentioned and post if you any doubts.
April 8, 2009 at 8:01 am
Heres where I'm at now
The back up folder I created on another drive now has these files.
1.8 Gb BAK file
11.2 Gb TRN file
77 Mb TRN file
65 Mb TRN file
176 Mb TRN file
68 Mb TRN file
80 Mb TRN file
The SQL data folder still has a
1.8 Gb mdf file
12 Gb LDF file
The SQL log and Event Viewer indicate the maintenace plan is still looking for an old missing TRN file.
(Operating system error = 3. The system cannot find the missing path.
April 9, 2009 at 4:44 pm
Make a transaction log backup, you have a lot of transactions in it, and this thing put all in your DB, and the LOG will be more little. (I think you have space for it.)
Or, Truncate transaction.log.
Then try bkp your DB.
April 9, 2009 at 4:53 pm
Edd (4/8/2009)
HThe SQL log and Event Viewer indicate the maintenace plan is still looking for an old missing TRN file.
(Operating system error = 3. The system cannot find the missing path.
Hi Edd,
What are you trying to do here? Whya re you getting this error?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply