December 9, 2008 at 10:06 am
I am a SQL Server 2005 novice so this may be due to my lack of familiarity with the Management Studio but ...
Scenario 1: I select a database, All Tasks, Back Up. I select backup to disk, and type in a path and filename. Click OK - everything works great, database backup file is created.
Scenario 2: Same actions as above except instead of clicking OK, I generate a script and click Execute in the query window. I immediately get a 'Query Completed Successfully' message but no backup is performed, no backup file created.
December 9, 2008 at 10:45 am
Can you post the script that is created?
December 9, 2008 at 10:53 am
Absolutely.
BACKUP DATABASE [JDE_EGEN] TO DISK = N'H:\SQL2005\EGEN.BAK' WITH NOFORMAT, INIT, NAME = N'JDE_EGEN-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Note: My purpose for doing this in the first place was to test the COPY_ONLY functionality. I thought the easiest thing to do was to generate a basic script and add the WITH COPY_ONLY directive. When this didn't work, I went back to the freshly-generated script and it didn't work either.
December 9, 2008 at 11:32 am
Give a try to the following
BACKUP DATABASE [JDE_EGEN] TO DISK = N'H:\SQL2005\EGEN.BAK' WITH INIT, NAME = N'JDE_EGEN-Full Database Backup', STATS = 10
GO
The NOFORMAT, NOREWIND, NOUNLOAD, SKIP aren't relevant within the context of backing up to disk.
The WITH INIT command will overwrite any existing file.
December 9, 2008 at 11:44 am
Removed the NOFORMAT, NOREWIND and SKIP directives (wanted INIT) and got the same result:
BACKUP DATABASE [JDE_EGEN] TO DISK = N'H:\SQL2005\EGEN.BAK' WITH INIT, NAME = N'JDE_EGEN-Full Database Backup', STATS = 10
GO
December 9, 2008 at 11:52 am
Phil Auer (12/9/2008)
Removed the NOFORMAT, NOREWIND and SKIP directives (wanted INIT) and got the same result:BACKUP DATABASE [JDE_EGEN] TO DISK = N'H:\SQL2005\EGEN.BAK' WITH INIT, NAME = N'JDE_EGEN-Full Database Backup', STATS = 10
GO
That's odd, I copied your query precisely (created a db of the same name, but a different backup path) and had no problems.
It doesn't do anything other than state "query completed successfully" which is what's really throwing me. It's almost like it's not even trying to execute the backup command, which makes no sense. Have you tried using a different database and a different path to see if you get the same result?
December 9, 2008 at 11:58 am
I have actually hand-type simple backup commands for different paths and different databases and none of the scripts executed properly.
December 9, 2008 at 12:02 pm
Random question, have you attempted to use a different SSMS console to perform the backup?
December 9, 2008 at 12:29 pm
You have officially stepped outside my wheelhouse. What I was trying to do should have been straightforward and simple.
December 9, 2008 at 12:31 pm
I completely agree with you. Honestly I am lost as to what the resolution may be. The last thing I could figure was trying to back it up from another machine.
December 9, 2008 at 12:54 pm
Phil Auer (12/9/2008)
I have actually hand-type simple backup commands for different paths and different databases and none of the scripts executed properly.
I'm sorry for being a bit dense - but, what does this mean? Are you getting an error? How do you know that it is not executing properly? What do you expect to happen compared with what is actually happening?
And finally, is the H: drive a local drive on your server?
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
December 9, 2008 at 1:10 pm
- It means that a backup run completely from the Management Studio GUI work perfectly (executes for a minute or two) but if I click on the 'script' button and run the resultant code from the SQL window, it does not work.
- I am not getting an error per se. I IMMEDIATELY get the 'Query Completed Successfully' message.
- I know it did not execute properly because (1) no backup file is created and (2) no information is written to the msdb (does not show up when I try to RESTORE).
- The H: drive is a local partition on the the server.
December 9, 2008 at 1:36 pm
Can't say that I have seen that kind of issue before. I know this is a stupid question, but have you verified that you are connected to the right instance of SQL Server?
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
December 9, 2008 at 1:49 pm
Are you clicking on the Execute button or the Parse button.
With the query highlighted, hold down the control key and press the E key.
let us know what happens.
December 9, 2008 at 2:06 pm
Lynn Pettis (12/9/2008)
Are you clicking on the Execute button or the Parse button.
which button are you clicking to execute the script?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply