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.
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?
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
Hi, There alot of talk about Service Master and DB Master keys and the importance of backing them up seperately. What about the DPAPI keys used to protect the Service Master keys. What procedures to I take to back those up during my database backup. If I understand correctly, if I do a backup on one machine, and then try to restore in a different machine, I won't be able to decrypt data becuase the DPAPI keys don't "match". This is assumming I don't use a password to protect my DB Master keys, I just rely on the service master to protect them.
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 am from NTU, Singapore and we have a windows 2003 server edition installed on a DELL server and it also runs MS SQL server 2000 for a project of ours. We have installed a HP tape drive on the dell server and it works fine if ntbackup is used at the command prompt but then the SQL enterprise manager does not recognize the tape drive and backing up of our database is of high priority as it contains sensitive information. But the tape drive is installed properly as the device manager does not show any error and ntbackup also recognizes it. I cant seem to figure out why SQL server 2000 cannot recognize it. It would be very helpful if you could kindly guide me on this issue.
Anyone know of a good "free" way to back up web files and SQL Server 2005 Express Database? I was able to use Windows Server 2003 Backup utility to back up the folder where the Databases were stored, as well as the web files, with no errors. But I have heard a lot of discussion that you can't just simply backup SQL Server data files? I'm wondering how sound the backup I've created is... Any suggestions?
i wanted to test routines using my local sql server engine and thought I could backup the mdf ldf file on the true network sql server in which I am listed as a dbowner with full permissions. In fact i can add users for the at db etc.The backup allows me to browse to a local folder
with the following being the default C:Program FilesMicrosoft SQL ServerMSSQL10_50.CMS_PROJECTMSSQLBackup
i name the file and it executes properly but when i try to restore that file to my local db that path is not even visible to me.In fact when i use exploreer I cannot even find the folders after Microsoft SQL Server!I search for .bak but nothing.How can I see that folder construction in the sql browser but never in explorer -
Im backing up to a network directory thats actually a mount point on a different server.My backup was slower than usual so i opened up perfmon to have a look.
When selecting the mount point from the Logical Disks section in perfmon i can see that writes/sec & write bytes/sec both show zero for a long period of time, even though the backup percent complete is increasing.Then all of a sudden the writes to the network share jump massively.
Is there some caching mechanism for backups in sql where during a backup data is only flushed to the disk periodically during backup?
I am currently creating an application that requires the use of Pocket PC/PDA. The same application that runs on the desktop is to be run on the mobile device metioned above. I am confused as to which of the following combination servers i should use.
Option1) Desktop - SQL Server Express Edition (Multi-user) Mobile Device - SQL Sever Compact Edition
OR
Option 2) Desktop - SQL Sever Compact Edition Mobile Device - SQL Sever Compact Edition
To my understanding, if I use SQL Server Compact Edition on both my desktop and mobile device, when i need to synchronise, the entire database from the mobile device is copied over to the desktop and vice- versa. However, I prefer only modified data to be synchornized. Can I achieve it with the second option? Is it also possible with the first option? Please advice. Thank You
I'm looking to purchase new server hardware that will host my corporate intranet and a sql server instance. In most cases I can't see this server being hit by more than 20 or 30 users but I want it to be quick. I'm planning on running Server 2003 with sql server 2k and the intranet on IIS.
What I'm not sure about is the single vs dual processor and the RAM. How valuable is the dual proc? And the RAM, I plan on 1.5 GB but I wonder what the benefits are beyond that.
Perhaps with this many users it doesn't make that much difference but I can see adding more load.
I've got a situation where I need to regularly (maybe each month), detach a DB, copy its files from their highspeed SAN location to a slower NAS, then re-attach it and make it available on-line. We're doing this for our DB's as they age to > 3 years.
Just wondering if any of you have scripts you can point me to so I don't have to re-invent the wheel.
Also, after you re-attach, how do you verify the NAS DB is 100% ok before deleting the original from its SAN location?
We currently have a Server: Dual Intel Xeon 3.0 GHz 4 GB ram (C) 2x18 GB SCSI disks RAID 1 for OS (D) 4x72 GB SCSI disks RAID 5 for Data
The server is running: Windows 2003 + IIS (Single web application) Tomcat (Single web application) (Is about to be outfased) MS SQL 2005 (In simple recovery mode)
The C-driver is currently only used for OS and applications The D-driver is holding all data + SQL Data files and SQL Transaction files... E.g. - SQL Data files = 7 GB - SQL Transaction files = 20 GB - Image liberary = 100 GB - Other = 15 GB
The IIS uses around 768 MB ram for cashing and execution of the web application The Tomcat uses around 350 MB ram The SQL is the to use a max of 2.2 GB ram (and 1024 MB pr. query) (Leaving around 512 MB ram left)
There is only one database installed on the SQL, but it is very large (+15 mill records) We are currently doing Free-Text searches in one table (~3.5 mill records), and it is to slow.
So the question is what is the best way to gain performance... - Increase to 8 GB ram? - Purchase a new server as webserver, so the curr. server can be dedicated as SQL? - Attach a NAS to split the SQL files into several groups, and seperate transaction logs from data? (6 disks are max in curr. server)
I'm hoping somebody can help me here as i'm struggling to find any information elsewhere on the net. We have recently purchased a new server, the rough specs are:
2 X Quad-Core Xeon E7320 2.13GHz 4Mb Cache 32Gb PC2-5300 DDR II RAM
We are planning to install the 64 Bit version of SQL Server 2005. We want to use the server for a number of purposes.
Building and weekly processing of 2 complex data marts (approx size is 1Tb each)
Processing and querying of 2 Analysis Services databases that will be built from these data marts. These will be queried by no more than about 15 users (no more than about 5 simultaneously).
Relational querying of the data marts themselves (same users as above) My problem is that I am not sure of the best way to configure SQL Server. Should I use 2 separate instances? How should the processors/memory be shared between SQL Server/Analysis Services? My main priority is the performance of the OLAP querying. However, I also want the weekly processing and any ad-hoc SQL querys run against the marts to be efficient.
I know it's bad to use the built-in SA account to access SQL Server from my ASP.NET application.
I did some reading but would like some suggestions as well. One article I read talked about Application roles in SQL Server. Is this the best way to handle SQL Server access for read/write/update/delete privileges from an ASP.NET application?
If this is the way to go, how do I set it up? Can anyone suggest a good web site to read up on this?
I'm looking for configuration advice for a SQL Server. I have thefollowing hardware to work with:1 Dell P4 2.66 GHz PC with 512 MB RAM and a 40GB IDE drive2 Seagtate Barracuda 4.55GB SCSI drives (ST34572W) in an external bay1 Adaptec 2940UW SCSI ControllerWindows 2000 Advanced ServerMSSQL 2000 EnterpriseThe PC will strictly be a SQL Server only - no IIS or FTP or otherservices. It will host about 8-10 databases that service a departmentof 300 people. My plan is to install W2K and MSSQL on the PC's IDEhard drive and configure the identical SCSI Barracudas as a mirroredvolume to host the databases (MDF/LDFs).Will there be any performance problems installing MSSQL Server on theIDE drive while the MDF/LDFs are on the SCSI drives? From aperformance standpoint, would I be better off installing MSSQL Serveron the SCSI drives?
I've been using Sql Server for my development for perhaps two years. My systems have experienced crashes, I switched from XP to Vista, it crashed again because of malware, etc. In short I have installed and reinstalled the Sql Server Express quite a few times. In the meantime my databases have been growing. So far I've had my development only on one desktop but now I am expecting a new DELL Dimention top of the line machine and I want to switch some of the development over there.
This is the scoop. I need to make numerous improvements in my databases to increase performance, sometimes you add a column or delete one from a table, etc. At the same time the data is being collected. I want to have my Sql DBs set up on one machine and be able to work on another and grab the data, etc. In other words I want to start doing some networking which so far I've done only on a limited basis just for file transfer and printing.
I have no clear image as to how it should be arranged. If I leave my Sql Server DBs on one machine and start using them on another one, do I have to have a new client set up?
Perhaps I need to have another instance set up also or is it the same?
In one of the prior installation which is now in my WIndows.old folder I have 3 Sql Server instance folders: MSSQL1, MSSQL2, MSSQL3. for some reason in this latest installation I got only one folder: MSSQL1. Shall I copy two other folders (MSSQL2 and MSSQL3) from Windows.old to this new Program FilesMicrosoft Sql Server 2005 folder or not?
Two of my machines that will access the databases will be at my office connected via a 10/100/1000 switch but the third one will be home. It will be connected via Vista Computer Name service (Telnet I presume) and I have a high speed cable to the server.
This is the setup as clear as I can see it now. Depending on the answers I get I may come back with some more questions.
Hi, i was after some advice on moving a SQL Server 2000 database from one server to another.
Usually i would do this by backing up the database on the original server the copying it accross the LAN to the new server and restoring it there. This database is 10Gb in size and copying it accross the LAN will take some time and i would like to minimise downtime if possible. The database is at a customer site where i am not responsible for the network or Hardware.
Does anyone have a good starter's tutorial on authorization advice & configuration for SQL Server 2005?I need to know what is adviced what web users can specifically do and dont in my db, and how to configure that...Thanks!
Hello,I'm looking into offering a custom data driven web app that I wrote for an organization that I'm apart of to other similar organizations. I would be hosting the data and web application code on my dedicated server. This application is using the membership api supplied in .NET 2.0 and also has my own custom data tables within it.My question is what would be the best way to add clients to this? Should I simply create a new database for each new client like so: ACME_Database, ABC_Database, AAA_Database etc. Or should I add some sort of client "Tag" (tag meaning column within each datatable) to these databases and then update my SQL queries to process them accordingly. I imagine I could do both but I guess I need some advice from people that already had experiance with providing this kind of service. Thanks!Jason
