HELP. Back Up Problem When Backing Up To A Mapped Drive On Another Server
Mar 20, 2002
Hi,
I have a rather odd problem that hopefully you'll be able to shed some light on.
We want to back up the databases to a hard drive held on another server so I mapped the drive in explorer to the drive then went into Enterprise manager and tried to create a backup device and it won't see the mapped drive.
I've tried mapping to my PC and I can see that via enterprise managers backup stuff (infact any PC in the office works) but it won't see any of the servers even though we can map to them and access them via windows explorer.
I've tried when logged on via sa and the windows NT administrator and still no luck. In fact no matter what I type or do it fails and keeps telling me device error or device off line which it isn't.
On our test instance of SQL Sever we can backup to other servers but not the new live version!
Any thoughts on what might cause this to happen and how to fix it?
I have a rather odd problem that hopefully you'll be able to shed some light on.
We want to back up the databases to a hard drive held on another server so I mapped the drive in explorer to the drive then went into Enterprise manager and tried to create a backup device and it won't see the mapped drive.
I've tried mapping to my PC and I can see that via enterprise managers backup stuff (infact any PC in the office works) but it won't see any of the servers even though we can map to them and access them via windows explorer.
I've tried when logged on via sa and the windows NT administrator and still no luck. In fact no matter what I type or do it fails and keeps telling me device error or device off line which it isn't.
On our test instance of SQL Sever we can backup to other servers but not the new live version!
Any thoughts on what might cause this to happen and how to fix it?
I have been trying to use openrowset with a shared drive, and even though the share has "full control" permissions granted to "everyone" and the accout that SQL runs under has been granted explicit full control permissions I am unable to open the file which itself has no security on it.
Can I not use a \ path and only use mapped drives?
Thanks
below works...
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:5People.xls', [Sheet1$])
below doesn't work...
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\cluster02FileManager5People.xls', [Sheet1$])
I created a DTS local package on the SQL Server. It's basically importing a text file into a table in my database. This file resides in a mapped drive (X:) from another server. When I schedule the DTS as a job, it fails. It doesn't execute any of the workflow in the design.
However, when I copy the text file into a drive local to the SQL server (D:), it runs flawlessly.
What I do right now is I have a windows scheduled task that runs a batch file that copies the text file from X: to D: at certain time intervals. Then the job scheduler runs to import it.
What am I missing? How come the job scheduler can't read the file directly from the mapped drive?
I am having a problem with a DTS package that pulls from a flat file off a mapped drive. When the package is ran alone, it runs perfectly but the stored proc that I took from an example from the net will not execute the DTS properly and I am unsure as to why it will not do so.
CREATE PROC spExecuteDTS @Server varchar(255), @PkgName varchar(255), -- Package Name (Defaults to most recent version) @ServerPWD varchar(255) = Null,-- Server Password if using SQL Security to load Package (UID is SUSER_NAME()) @IntSecurity bit = 0,-- 0 = SQL Server Security, 1 = Integrated Security @PkgPWD varchar(255) = ''-- Package Password AS
SET NOCOUNT ON /* Return Values - 0 Successfull execution of Package - 1 OLE Error - 9 Failure of Package */ DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)
-- Create a Pkg Object EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT IF @hr <> 0 BEGIN PRINT '*** Create Package object failed' EXEC sp_displayoaerrorinfo @oPKG, @hr RETURN 1 END
-- Unitialize the Pkg EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize' IF @hr <> 0 BEGIN PRINT '*** UnInitialize failed' EXEC sp_displayoaerrorinfo @oPKG , @hr RETURN 1 END
-- Clean Up EXEC @hr = sp_OADestroy @oPKG IF @hr <> 0 BEGIN EXEC sp_displayoaerrorinfo @oPKG , @hr RETURN 1 END
RETURN @ret GO
that is the stored proc that i am using along with a couple error trapping ones but this being the one that does the actual execution. Is there anything i can change about this in order for it to run the DTS properly from the mapped drive?
From my workstation (with SQL Server 7 Desktop Edition SP3), I seem unable to restore a database on my Server (SQL Server Standard Edition SP3). I am logged into both machines, and I am an Administrator on both machines. Using either a UNC or Mapped Drive (see below)
RESTORE DATABASE ogAEC FROM ogAECDump WITH REPLACE , RECOVERY , STATS , MOVE 'AEC_Data' TO 'Og-sqlsrvrC-DriveMSSQL7DataogAEC_Data.MDF' , MOVE 'AEC_Log' TO 'Og-sqlsrvrC-DriveMSSQL7DataogAEC_Log.LDF'
RESTORE DATABASE ogAEC FROM ogAECDump WITH REPLACE , RECOVERY , STATS , MOVE 'AEC_Data' TO 'Q:MSSQL7DataogAEC_Data.MDF' , MOVE 'AEC_Log' TO 'Q:MSSQL7DataogAEC_Log.LDF'
I get Server: Msg 3156, Level 16, State 2, Line 1 The file 'Og-sqlsrvrC-DriveMSSQL7DataogAEC_Data.MDF' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.
But I am successful if I run the essentially command locally from the server:
RESTORE DATABASE ogAEC FROM ogAECDump WITH REPLACE , RECOVERY , STATS , MOVE 'AEC_Data' TO 'C:MSSQL7DataogAEC_Data.MDF' , MOVE 'AEC_Log' TO 'C:MSSQL7DataogAEC_Log.LDF'
What can I do to be able to restore DBs from my workstation?
I am setting up a new SQL Server that has an external HP 920 SAS Ultrium tape drive attached to it via a p400 sas card.
I am running 2003 R2 x64 with 2005 SQL Server x64, and I am trying to set up my DB backup plan but for some reason I can't get SQL to recognize my tape drive, alhough it is listed in device manager.
I've been working with SQL Server for a few years now and I've had quite a few cases where users cannot backup SQL to a network location or external hard drive. Why can SQL not do this? Is it some limitation of the speeds at which SQL needs to backup the information and the external hard drives/network drives do not meet the requirements?
I have VC++ express and MSSQL SMS express and have an application working nicely locally. The Data explorer and data connections part works really easily.
Now, I want to make the application available to my home network.
I mapped the drive where the database is and called it Z: so I could put my "release" on my other network PC and assumed it would find Z: if I mapped the shared network drive on that machine and called it Z:
But: I can't even add the mapped connection on the local machine, I get:
The file "Z:databasescalorie.mdf" is on a network path not supported for database files. An attempt to attach.....etc"
It works fine on the original F drive.......
Am I approaching this the wrong way. How should I distribute to network PCs?
Does anybody know of a way to rollback SQL Server 2005 databases back to SQL Server 2000? Is there a way of doing it without resorting to Copy Database Wizard? I love to find a way of attaching a SS 2005 database to a SS 2000 instance without any issues.
I recently upgraded to SS 2005 and I am very unhappy with the SS 2005 and I want to rollback to SS 2000, which was a lot more stable. I am having several major issues that are affecting my whole company's day-to-day operations and the managers are not happy. Some of the issues include night time batch running very sluggish for no apparent reason. This is a biggest problem because it only occurs once or so a week and causes a disturbance with the daily activities when the night time processing isn€™t completed on time. The rest of the time, the batch processing runs great, even a little better then on SS 2000. I don't believe it is a matter of my application needing to be retuned because if that was the case, then why isn't it running sluggish every night? Also, it's never the same day that the sluggish behavior occurs. If it was occurring on the same night, then I would have something to investigate within our application, but it doesn't. Another issue that I am having involves a night time job that restores a copy of the production database to the Data Warehouse server to be used for updating the data warehouse. Again, most of the time it runs great (~2 1/2 hours), but once or twice a week, it goes stupid and takes 6 1/2 hours for no apparent reason. Again, it is not happening the same day either, which could give me something to invesigate. On SS 2000, this same job ran flawlessly. Never I did I run into situation that the database restoration took that long to run. Even another issue involves a SQL Server Agent Job that was put into suspended state. What's a suspended state and how can I get it out of suspended state? I can find no information about suspended state in BOL. I did a Google and nothing came up. If this suspended state was put in for security reasons, great, but then tell me how I can remove the suspended state. I am also not happy with the fact that I can't get accurate information about the queries that are actively running at that particular moment. In SS 2000, when I noticed high CPU usage on the server, I would run the sp_who2 active stored proc and it would show me all the active thread and how much CPU it was consuming. I would then find the running threads with the highest CPU numbers and investigate the query and see if we could improve it. Now in SS 2005, I get in the same situation and run the sp_who2 stored proc, and there is no smoking gun. All of the active threads are showing very little CPU usage, which I am very suspect of. What the heck happen to sp_who2? I looked at some of the other ways of looking at running processes (i.e... sys.sysprocesses) and they don't appear to be giving the information that I need.
I am very unhappy and I just want to roll back to SS 2000 and wait a couple of years before I upgrade to SS 2005.
I mapped a drive on to my SQL Server box. It points to another server from the same domain. When I try to backup or restore a database, I can't see this mapped drive through my SQL Server. Even if I type the entire path, SQL Server wouldn't take it. I don't have a clue about why it is not working. Can anyone throw some light on this. Your help is grately appreciated.
Being a very novice SQL Server administrator, I need to ask the experts a question.
How do I go about moving a database from 1 drive to another? The source drive (C is local to the server, but the target drive (E is on a Storage Area Network (SAN), although it is still a local drive for the server. I want to move the database from C: to E:. Can someone provide me with instructions?
How to backup half of dbs from a server on C drive and the other half on D drive and vice versa, first half on D drive and other half On C drive using only one job and one stored procedure??
Using scheduling from job add 2 schedules to the job so first schedule backup first half to C and second half to D , the second schedule backup first half to D and second half to D.
Hey, please forgive me if my infomation source was incorrect. But a friend of mine mentioned to me he had hurd of a program, or code that will make an "install script" of your database. Basically it`ll back up all the tables on your server and if the worst case senario happens then you can run the file like an install script. Is this true? It sounds invalueable! If it isnt true, can someone tell me if it is possible to use code to create a "install script" of your own database? I am using "Microsoft SQL Server".Thanks in advance John
Hi, I've recently created a maintenance plan, which backs up my database and transaction log. However I also have a Veritas (Backup Exec for Windows Servers) tape backup which runs at night and backs up my files. It is setup to backup the whole of my MSSQL DATA and BACKUP folders.
Today I went to do a test restore, and found that the tape backup contained nothing in the MSSQL DATA folder, but everything that was in the BACKUP folder. Is this because everything in the DATA folder is in an open state? On checking back, the tape backup has never held anything in the DATA folder. Should I be worried by this, or is it what you would expect, and is the SQL backup sufficient?
hi, I have total 45 GB of data in the following servers ( 40 GB in sql server 6.5, 5GB in ftp server). both servers are in one machine(server). I want to make sure that I am doing the right thing for backup. The machine is not connected to a Lan. What is the best way to insure that my data are backed up safely? what I am thinking of doing is to back up the database to using sql tools in the menu bar, that will backup the data to the same machine in this directory: c:mssqlackupDBname.DAT
I will do this for all database in the sql server. Am I save to do this... I am wondering what IF the machine in which both severs are in crashed. My backup effort will be lost too. right. so what is the best way to protect my data. Do I need to buy a tape backup so I can do what I am doing Plus back up the c:mssqlackupDBname.DAT to the tape backup... I would really appreciate any suggestion in choosing any media (software/ hardware)to back up my data.
I want to reformat my servers harddrive and install a newer operatingsystem (Win 2003). I am running SQL Server 2000 with about 10databases. What is the best way to backup and restore SQL Server forthis so I don't lose anything.Thanks,Rick
I am just getting started with MS SQL and am having to learn from scratch. I have a DB running with an ISP which I need to back up to one of my local machines. I have installed MS SQL Server 2005 and also MS SQL Server Management Studio Express. Using this, I have opened a new DB on the local machine and then accessed the DB on the remote server. If I then right click on the remote DB file and select TasksBackup, I see the backup options screen. The problem is that the destination for the backup is automatically set to a folder on the remote server and I cannot figure out how to change this so that I can download to the local machine. If I click on the 'Add' button to create a new backup path, it will only offer me a view of the remote machine, not the local file structure.
The solution is probably pretty simple, but after several hours of experimentation and searching for solutions, I still can't come up with the answer. Can anyone offer some guidance please (in idiot language - I'm new to this).
Hi I am trying to make a backup of my SQL server 2005, used with Web Developer 2005 Express, in osql.exe (can't find any other way of doing it). I get the error that "under the default settings SQL server does not allow remote connections".I connect to it fine from the Web Developer 2005 and my application . I followed another post's instructions where I enabled the TCP/IP and named pipes for "Protocols for SQLEXPRESS", but didn't work. In fact when I changed TCP/IP properties/ IP Addresses/TCP Dynamic port to nothing , and TCP Port to 1357, the server wouldn't start again !! (not doin THAT again)Regards Amanda
How can I back up existing SQL Server 2005 Express tables? I'm presently developing on a 4 year old laptop which is toooo sloooow and need to transfer the tables and data to a desktop system.
My client is changing hosting providers. We are trying to download a full backup of our MS SQL 2000 database from the old host. We can get all of the data using the Data transfer wizard in Enterprise manager. We can get all data and structure using the Data Transfer wizard with other options. However, when we do this, all of our tables get renamed with a prefix that is our username from the old host.
So, if we have a table named customers and our MSSQL login on the old host is userA, then our table gets renamed to tableA.customers.
This won't work with our applications and stored procedures. Changing tens of thousands of lines of code is not acceptable.
Does anyone know a way we can get a full copy of the database (rules, data, structure, keys, identies, etc) without having the username prefix?
We're running SQL Server 2005 (9.00.1399.06, RTM, Standard Edition (64-bit)). A couple of times each day, all connections to the server will fail, and this behavior coincides with backup activity. What tends to happen is that after performing flawlessly for hours, SQL Server will start rejecting all connections. Over the course of the next 30 seconds or so, several hundred instances of the following error message will appear in the Application event log:
"SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems." Source: MSSQLSERVER, Category 4, Event ID 17189.
Immediately following these Error entries are Information entries that indicate some backup activity has been performed:
"Database differential changes were backed up. Database: [lots of db-specific details] ..." Source: MSSQLSERVER, Category 6, Event ID 18270.
As soon as the backups are completed, everything returns to normal and connections are once again accepted. The entire cycle takes less than a minute. Obviously, something is causing SQL Server to lock up and reject connections while it's backing up. Is there a setting that controls this, or does this behavior point to an overloaded machine?
We don't seem to be having any other performance problems at all, and there are no other Errors in the Application event log from SQL Server. If I perform a manual backup, I don't get any dropped or refused connections, even if I do it during peak query hours. So I'm stumped as to why the machine is locking up in this manner. Unfortunately there's no info in the EEMC about event ID 17189.
I have my SQL7 server running on NT4 server. Currently the transaction log backups are on the same server but I want to back these up to a Win2000 server in another building. When creating a Backup Device in Enterprise Manager on the NT server it cannot see the drive but not the folder I have created on the Win2000 server, even though I have shared this folder out and set the permissions. Any help would be appreciated.. Thanks
I am getting an error while backing up a particular database named "asset" . The error says ........
Server: Msg 3132, Level 16, State 1, Line 4 The media set for database 'Asset' has 2 family members but only 1 are provided. All members must be provided. Server: Msg 3013, Level 16, State 1, Line 4 BACKUP DATABASE is terminating abnormally.
I have created two backup devices for this database ("asset") through enterprise manager. AssetDMP AssetLogDMP
I have total 9 databases out of which only this database throws error. All other database backups going fine.
Here is the query that I am using to backup the database and Log.
USE master GO IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'Asset') BACKUP DATABASE Asset TO AssetDMP WITH NOUNLOAD, STATS=10, INIT, SKIP GO IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'Asset') BACKUP LOG Asset TO AssetLogDMP WITH NOUNLOAD, STATS=10, INIT, SKIP
