First let me say: I dont have any expensive programs lide VS, only the MSDE desktop engine and Web Matrix and a Community Starter Kit v1.0 VBSDK.
But i have the free SQLExec version 1 from laplas-soft.com. It looks like you are in full control with this program, but when according to help file i try to backup i get: "Device error or device off-line. See the SQL Server error log for more details". Of course i start up by connecting to the database.
Any ideas?You need to create a backup device first. From books online:
<quote>
A. Back up the entire MyNwind database
Note The MyNwind database is shown for illustration only.
This example creates a logical backup device in which a full backup of the MyNwind database is placed.
-- Create a logical backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_1',
DISK ='c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat'
-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_1
</quote>|||Sorry, but i'm new into .net and i dont know how to make such a device without more details.
I can see that it is not a .bat file i have to make, though.|||You would need to run this script through osql. ALternately, the data only could be backed up using bcp, a command line tool that can be placed in a batch file. Find BCP.EXE on your machine, run BCP /? for details on what it can do.|||I would much prefer to backup the whole database instead of data only. Because i want to copy the backup'ed database too. Can i find this osql?
And thanks..
I mean, i would like to just work on the remote databaseses. So if i can backup/copy it remotely, that may be best.|||Can't he just copy the *.MDF and *.LDF files his site is using?
Regards
Wayne Phipps|||OSQL is a program that is installed whenever MSDE or SQL Server is installed. You should search Deja.com or similar for all the details of using it, or maybe even usethis link to download the SQL Server Books Online.|||Well, yes, but that implies stopping and restarting the database, which I generally presume folks do not want to do. In addition, he would want to explicitly detatch the database, to reliably be able to reattach.|||Thanks for the help so far. I will look into it, and hope you will return. Its a bit late evening here in Denmark, so goodnight.|||Well, it seems from my experience with this, that it could take me a week to learn just to make a simple backup. I know it is not as the easy days with an Acces database, but maybe i have to try the way Wayne Phipps suggested. I dont mind shutting down the site(s) an early morning for a few minutes.
On the other side. It seems a simple thing with OSQL. Found this on deva (or google.com):
"
You can use the OSQL command prompt utility. For example:
OSQL -S MyServer -E -Q "BACKUP DATABASE MyDatabase TO
DISK='\\MyLaptop\BackupShare\MyDatabase.bak' WITH INIT, STATS=5"
Note that the SQL Server service account will need permissions to the
share. Alternatively, you can backup to a local file and then copy the
backup file to the remote machine.
See the Books Online for details.
"
Where can i see the books online? The download link doesnt work, nothing happens.
Can someone give me directions to make it work for an online sql database?
It looks like some autentification is missing in the above?|||Is it really so hard to backup.
Well, it seems from my experience with this, that it could take me a week to learn just to make a simple backup.
A backup of a database (SQL) shouldn't be as hard as that. You have not got correct approach yet, otherwise, you will feel the backup is just as easy as 1, 2, 3.
First, let me clarify your situation:
1. You wanted a (SQL) database backup. Thus, this issue is not related to something likeVS, ..., Web Matrix or a Community Starter Kit v1.0 VBSDK.
2. You have a MSDE desktop engine. As my knowledge, it doesn't have a Enterprise Manager as SQL7/2000 do. However, you do have a SQLExec that is somewhat similar to SQL2000 Enterprise Manager.
I am not fimiliar with MSDE and SQLExec, I will give you possible solutions for SQL Server 7/2000. These solutions will probably be suitable for your case. Otherwise, we start further discussion from there.
Method 1: SQL server backup using Enterprise Manager
1. Open SQL Enterprise Manger, right click the database you wanted to backup, say, Northwind, --> All tasks --> Back Database ... --> lefe click to open the window "SQL Server Backup - Northwind".
2. To add the destination of your backup, click the "Add" button to open "Select Backup Destination" sub-window: there will be a default directory in the "File name" box. For SQL2000, it would be "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\"
YouNEED to specify the file name:
a. you could look up a file from your disk by clicking the browse button "..." at the right of the box;
b. if you made the backup first time and you didn't have a backup file in your disk anywhere, youNEED just simply to add a fine name into the box, say Northwind.bak (for Northwind database). Now, the "File Name" box would have the file name as
"C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind.bak"
3. Click "OK" button in "Select Backup Destination" window to close it. Then click "OK" button in your "SQL Server Backup - Northwind" window, you have done your backup SUCCEESSFULLY.
For your case, if you used SQLExec from laplas-soft.com, it would be only one window like this:"Database Backup" window of SQLExec, where the destination box includes directory as
"D:\DBB\SQL7\mcp_23\"
If you clicked the "Apply" button now, I guess, you would probably got the error "Device error or device off-line. See the SQL Server error log for more details" that is exactly the error you could get in SQL Enterprise Manage without specifying the destination file name but only directory.
Type a fine name under the directory or another full path file name into the desitination box, click "Apply" button, you have 95% chance to have your job done, I assume laplas-soft.com made its SQLExec's behavior as same or close as SQL Server. If this was true, you probably would need five minutes to read this suggestion and another five minutes to do your test. 10 minutes, instead of one week, you have your job done. ;)
More, in the next post.|||Thanks very much for taking time with this ghan from NYC. I am greatful to get all the help here at the forums.
Now i got a backup!!!
Everything seemes to have its tricks to fix.
Now what to do with it?
I would like to restore that backup. Restore could may be putting it on the remote server instead of the current. Some day it may be neccecery.
And if possible make new databases from this .bak file.
I loooked twice in the SQLExec for a restore function, but found none.
Waiting for input_|||Oh, it seems you got your solution within less than 10 minutes. :)
For completeness, I will first finish my previous discussion about the backup topic in the next post, then go to your restore issue. Be a little bit patient, you will get your solution for your new question in another 10 minutes.|||Continue fromMethod 1:
So, the point is you need to specify your backup device, or the backup file (wiht full path name) in your case.
Back to SQL Server 6.5, there was a "Backup Devices" Directory (also a "Database Devices" directory), at the same level of "Databases" directory in Enterprise Manager, then user would explicitly to make a backup device over there.
From SQL 7, these two directories ("Backup Devices" and "Database Devices") have been gone. When you make a new database, you do not need to create a "Database Device" first, SQL Server make it for you automatically (as a yourDatabaseName.mdf file for your database and a yourDatabaseName.ldf file for your database log file). However, when you backup a database, you have to make your backup device manually in some extent.
Besides the way, I mentioned in the previous post, you could create a backup device as follows:
1. Manually created a empty backup file anywhere in your disk, say, under
"C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\"
create a new empty "test.bak" file (in fact, it could be any name and suffix). Then, you could look up the device file from your backup process.
2. In the "Select Backup Destination" window, discussed in the previous post, there is another choice "Backup device", from there, you could choose <New Backup Device> and create a new device. But, it is nothing more than the way I have introduced in the previous post.
3. You could use Transac-SQL to do it, as Douglas mentioned by system SP "sp_addumpdevice".
Method 2: SQL Server backup using Detach/Attach methods
As mentioned by Wayne Phipps.
The Detach/Attach methods are available for SQL 2000 in both Enterprise Manager choice and Transac-SQL choice, but only Transac-SQL choice for SQL 7 as
sp_detach_db and sp_attach_db
See details from the thread246218.
For this method, beside it could be used as an alternative to backup/restore, it is mostly used as an alternative to import/export functions in SQL EM.
Method 3: SQL Server backup using Transac-SQL code
You could get info from SQL online, Backup. Although one could do the job in that way, but, as a programmer, I have never done it by myself.|||Para7,
Actually, you have already had your solution for restore issue from my previous post. Especially, from the thread246218 for details.
Basically, that uses the Detach/Attach methods. But, you could use backup/restore to do the trick also, see my nest post in another 10 minutes.
No comments:
Post a Comment