I am fairly new to SQL server and am having a problem getting the SQL backup or "dump" to do what I need it to do. My desired end result is to have a database backup every night to a backup device on the same server as SQL is running, and keep one weeks worth of databases in the backup device, overwriting the database backup from 7 days before. This device is then transferred sometime later to another server using the NT AT scheduler. The first time I tried to accomplish this I used Enterprise manager to schedule a full backup to be run each night at midnight. The backup device was set to be initialized and retained for 7 days. This procedure worked once, the first night. There after it failed. In looking at books on line it said that I needed to change the transact -SQL command from INIT to NOINIT as the INIT instruction was the cause of the failure. I changed the statement in the Scheduled Task manager to NOINIT. That seemed to do the trick as I checked the backup device each of the next few days and saw that it was indeed performing the backup and retainng the databases. A week later I checked the backup device again and found that it is still adding database dumps to the file, but it is not overwriting the dumps that are 7 days old. I am told that I need the INIT statement in the dumpdevice command in order for the RETAIN DAYS = command to kick in. However, this did not work before. I can not continue adding to the dump device as it will get too large and take up too much disk space on the server. The only way I can get the device to a reasonable size is to do a backup and initialize the device, which gets rid of all of the older databases. This seems like a catch 22. Is there any way I can make this work. I am running SQL 6.5 on the latest service pack (5a). Any help would be greatly appreciated.
I have an int field - that will hold number for an ordering system. It's a databound field to a textbox in a grid, I want the default to be empty, however not all fields are required so i may have 1-6 as bound values, and the rest should fall in line behind them - the only thing i can think of to do is default the values to 9999 so they come after 1,2,3,4,5,6 etc... I don't like seeing 9999 in my bound fields. thanks for any suggestions you may have. Jeff
i have a field of type int, that when returning results, needs to be in ascending order. the problem is that this field can also contain null values. is it possible to return the results in ascending order but have all the null values at the bottom of the result table?
1. I need to create a replication enviroment. In that i want to snapshot the database as and when i want from my Java program, so that selective tables are updated with latest data. Is there anyway i can do with out creating native replication methods.
2. Is there any way with out creating a snapshot or replication. I need to copy the data from my master database to child database(my reporting database) with out straining much my master database
When I use "DUMP DATABASE", it will help me to backup both database and transaction log. However, it will not help me to clean up the transaction log. Therefore, I am worry that will the following happen?
1. DUMP DATABASE Start backup database ( there are transactions during the backup process ) Finish backup database Start backup transaction Finish backup transaction
2. DUMP TRANSACTION Start backup transaction and clean up the transaction log
So, will the transactions been backuped twice? If yes, will it cause those transaction do twice when I restore the database then restore the transaction?
Tried googling, but I guess I have the wrong terminology for MSproducts.Using PostgreSQL, or MySQL, if I wanted to tranfer a database from onemachine to another, I'd do a "dump" with a single command, such as:mysqldump [databasename] > [path/filename]Then I'd take the file generated and dump it back onto a new machine.What's the process with MS SQL Server? Can someone point me in theright direction to a webpage dealing with this?--[ Sugapablo ][ http://www.sugapablo.com <--music ][ http://www.sugapablo.net <--personal ][ Join Bytes! <--jabber IM ]
Hello, How do I import data from a text file into columns (DB_table) that I have created in SQL server 2005? Data in text file does not have any commas, semicolons, or spaces in between them. Each instance in the text file consists of 109 characters (real numbers). I also know that first 4 numbers go in particular field, next 4 go in another field, and next 6 are particular field, and so on. I also have created columns in a table respectively.
Both "dump database" and "sqlmaint.exe -bkupdb" would be able to backup database, one important advantage I can see from "sqlmaint" is that it has the option to cleanup the very old generations of backups by using -delbkups option in a scheduled maintenance backup job, while the "dump database" would first create a dump device and it'll grow with time. Is there any suggested way to use the "dump transaction" but still we can get control on the size of dasd used ? Any negative part of using "sqlmaint" utitity to backup database as compared to the "dump database " utility ? Anthony
We run SQLMaint hourly ( as a scheduled task ) to clear our transaction logs. Last night our hard drive filled up during a log dump, causing SQLMaint to fail, and a subsequent transaction filled up the log.
We succeeded in manually truncating and clearing the log, but SQLMaint still insists on us doing a DUMP DATABASE to clear the logs before it will run again. Unfortunately, we do not have enough space left on our hard drive to do a full database dump.
Is there any way of fooling SQLMaint into believing that a DUMP has been run? Some flag in a system table somewhere, or maybe a registry entry?
I'm a newbie. I've installed a replication between 2 NT4 servers, with SQL 6.5. My logreader tasks has crashed "Can't allocate space for objects 'syslogs' in database 'distribution' etc...". With a DBCC SQLPERF(LOGSPACE) I can see that the log is 100% full, but I can't dump it : when I use DUMP TRAN DISTRIBUTION WITH NO_LOG, my distribution log is still 100% full. So how can I do to dump it and what can I do to prevent it from filling up ? Do I need to dump it like I do for my database but how could it work if a DUMP TRAN doesn't work ?
Thanks in advance.
Sorry for the English, it's not my native language.
hi,There is facility to do backup of the database. I can use thatfacility to backup the database. But before i do a backup i want tocheck if the disk space available enough to backup that database. Ihave a 22gb database mdf file, when i took a backup of that its only3gb. SO i cannot use the size of mdf file to determine the databasedump file. Is there any facility available to find out the backupdatabase dump size before doing the backup?ThanksNabhonil.
We are running sql 6.5 with Serv. pack 4. All of our Prod. database are backedup weekly via scheduled tasks. On occasion the dump of a particular database will cause 1 of 2 things: 1. The db dump seems to work correctly but stops sql executive service. 2. The db dump seems to work correctly but sends an alert email msg to me indicating that it failed. The msg in the email is "NO Message".
Has anyone experienced either of these 2 problems or have any solutions?
I want to schedule a job to run nightly where a remote mysql database is imported into a local sql server db. I ran through the wizard for scheduling a job, previewed my table data, and viewed the scheduled job in the DTS queue. However, the job never runs. I can't figure out why.
Anyone have any idea why? Is there a step-by-step process somewhere for doing this?
Does anyone has a script which gives all databases names and thereallocated, used sizes in SQLserver2k. I want to schedule this to create adaily report.Thanks,Nasir
Hi, I have added a new SQL Server database to my project and I was wondering how to do generate a SQL dump of the database, tables, and queries? I couldn't find any utilities in Visual Studio 2008 Pro. I also couldn't figure out how to create indexes using VS. Thanks!
I get the following error when doing a dump on a 6.5 database. *** Page '697569' of database 'FOREX_UPLOAD' in the database has the PG_DMPTRLR bit set. Page '698121' of database 'FOREX_UPLOAD' in the database has the PG_DMPTRLR bit set. Page '698233' of database 'FOREX_UPLOAD' in the database has the PG_DMPTRLR bit set. *** I have run SP5 as suggested,and can see that more of the db has been dumped than before but still I get the error I get no errors when doing dbcc's. Any ideas???
I need to dump transaction log every night. Before I do that I'd like to check those open transactions and kill the ones not part of sa or SQL server dba's activity. How can I achieve this?
After Dumping a database device, I still find the xxx.DAT files inthe /Data directory. Can these be 'safely' removed/backed up ? They are taking up WAY too much space.
Question 2: After installation and after creating devices & databases, is there any way to reassign the /Data directory on another drive? Either for future devices/databases or existing?
I understand from the beta sql 7 newsgroups that 7 can't restore a 6.5 dump. This is extremely frustrating that MS wouldn't put this type of backword compatibility into 7.
Has anyone found another way to accomplish this? I can't use transfer manager as the 2 servers are on seperate, non-connected boxes. I ftp'd the dump from 6.5 server to a middle-man machine, then copied it to the 7.0 server.
Am I looking at bcp'ing out all 203 tables in my 1GB db then bcp'ing them back in?
Is there a way to dump sql in Access like in Transact SQL or mysqldump? I've gotta convert an Access Database into a MySQL database, it is a small database, but I'd not want to put it in by hand .
If not, I'll just make a PHP script to rip it from a textfile.
I am trying to import a database from a dump file. The dump file is from a sql server db.
I have created the db on another server and want to import the data from the dump file.
I use the bcp like this :
bcp nnfdb.sa.users in x -Usa -P
nnfdb is the name of the database users is the name of a table x is the name of the dump file.
But I get this error : SQLState = S0002, NativeError = 208 Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'nnfdb.sa.users'.
also I am sure if I have to run the bcp for each table or if I can import all tables in one bcp run.
