Currently my SQL Database has more than 10,000 records on it. What must I do to backup my data in SQL Server, so that I may not accidentally lose all the data I have during my web development?
===============
JSC0624
===============
Hai friends,
I am using sql server 2000 as a Backend. I have stored nearly 50 user tables.Now i need backup that(export) , and i need to put that in another pc where sql server 2000 installed(import).
i have a "master" database.
do u have any simple steps , pl reply to me....
THANK U in ADVANCE
Ambrose
5 March 2000
Using SQL Server 2005 Server Management Studio, I attempted to back up a database, and received this error:
Backup failed: System.Data.SqlClient.SqlError: Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the satement after the current backup or file manipulation is completed (Microsoft.SqlServer.Smo)
Program location:
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropOptions.OnRunNow(Object sender)
Backup Options were set to:
Back up to the existing media set
Overwrite all existing backup sets
I am fairly new to SQL 2005. Can someone help me get past this issue? What other information do I need to provide?
Our HP representatives are telling us that backing/restoring our MS SQLServers with "HP Data Protector (HPDP)" is better and safer than usingMS Sql's backup/restore process.I have strong hesitations, because MS Sql's processes gives us a greatdeal of flexability.Has anyone have any experience wsith "HP Data Protector (HPDP)"?
View 4 Replies View RelatedI have a live server running windows Nt and sql server We brought a new
backup server of higher configuration we wanted to swap the data we installed nt and sql server in the new server will a cut paste of the data directory of the live server to the backup server is sufficient or the data directory to be restored to the respective files through sql enterprise manager please do clarify me
Regards
Ramesh
If my backup starts at 8PM and take 1 hour to complete, will the changes made to the database during that hour be captured in the full backup?
Stated another way, will my backup be a snapshot of:
a) 8PM when the backup started
b) 8PM with some of the changes made between the hour
c) 9PM when the backup finished?
Anybody know the exact way SQL Server handles that logic?
Thanks,
Marc
I have last 5 years data in my database(SQL server).
I want to take backup only last 1 year(last 365 days) data using SQL script.
I want to SQL server data backup from remote server. I can able to access remote server through query analyzer only. How can I Possible do that through my local query analyzer and remote my local system?
Thanks for advance
If data is modified (by an insert, update, or delete) while the backup is running, will the backup contain those changes or will it be added to the database afterwards?
View 2 Replies View RelatedHi,
I had SQL Server 2000 installed like below:
Installation Directory = C:Program FilesMicrosoft SQL Server
Default data directory = D:
Default log directory = D:
I found out that the data is stored in D:Program FilesMicrosoft SQL ServerMSSQLdata and backup is done in D:Program FilesMicrosoft SQL ServerMSSQLackup
However, Microsoft says we should never put the ..ackup and ..data folders in the same partition.
My question is: How can I move the path of the backup directory?
I checked the registy but this is what I found:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServer
..BackupDirectory=C:Program FilesMicrosoft SQL ServerMSSQLackup (<-- note it is pointing to C: not D:)
..DefaultData=D:
Can somebody help please?
I have automated process, which synchronizes a transactional publication using initialize from backup approach. It drops subscriptions and puts them back again once the restore on the subscriber is completed.
Dropping the subscriptions causes a lot of blocking and deadlocking. I've decided to remove those steps, but it causes loss of data on the subscriber.
Is it a must to drop and re-create the subscriptions during such process? If not, how can I avoid the loss of data?
SQL Server 2008 r2 - 6 GB memory...I attempted a backup on a 500GB database but it was taking way too long. I checked the resources on the box and saw the CPU at 100%. I checked the SQL Server activity log and saw a hung query (user was not even logged on) that had multiple threads so I killed it and now the CPU utilization is back to normal.
Trouble is, now all of the threads in the activity monitor for the backup show 'suspended' and the backup appears to be not doing anything.
Need to restore database,here's the scenario:
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
Windows 2003 backup utility uses the shadow copy option that allows it to copy open files.
Therefore, can I use this utility to backup the .mdf and .ldf files for my SQL 2000 database?
I can then attach the .mdf files if I need to restore the database to another server.
Can anyone tell me if this is safe? I've tried it and it worked but I'm worried there maybe some lurking danger in using this approach.
I've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...
/* Script to delete older than N days backup from a specific directory */
USE [db_admin]
GO
IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL
DROP PROC usp_DeleteBackup
GO
[Code] .....
I have only one tape drive.
My server is using the SQL server 2005 backup tool and arcserve backup tool to backup different files .
Everytime I need to disable and enable the tape device driver (in device manager /windows) before I can use SQL server 2005 backup tool.
Everytime I need to disable and enable the tape device driver (in device manager /windows) before I can use arcserve backup tool.
It seems that the tape device resource is held by softwares even the tape drive is not in use.
What should I do to make the tape drive shared by different softwares?
Regards,
Manuel
I want to know how people are backup up their win2k system and SQL 2000 server. If you want a run backup once a night and first backup win2k file system and then run backup using sql agent will that work?
Thanks.
SQL 7.0 SP1.
We do SQL Server backup and also third part backup (with SQL agent active) of SQL Server databases.
Sometimes happen this situation:
01.00 SQL Server backup of DB1
01.00 third part backup of DB1
02.00 SQL Server T-LOG backup of DB1
03.00 SQL Server T-LOG backup of DB1
03.30 SQL Server differnatial DB backup of DB1
04.00 SQL Server T-LOG backup of DB1
or this situation:
01.00 SQL Server backup of DB1
02.00 SQL Server T-LOG backup of DB1
03.00 SQL Server T-LOG backup of DB1
03.10 third part backup of DB1
03.30 SQL Server differnatial DB backup of DB1
04.00 SQL Server T-LOG backup of DB1
My questions now are:
Are my SQL Server backups valid (DB,Differenatial and T-LOG)?
In case of a restore operation, can I use my SQL Server backups without problem?
In other words, can we leave the third part backup tool active or it should be taken away?
Can anybody clarify this for me?
Thank you in advance.
Franco
Hi,I have(had) an old Win2k Server server with about 30 web site databases(SQL 2000) that just went under due to hardware problems. Thankfully, Ihave backups of all the databases plus the MDF and LDF files from thehard drive.I want to move all of these sites and their data to a newer server(Win2003) running SQL2000.What's the best way to copy the database from the old server hard drive(now mounted as an extrnal drive to a local machine; I'm currentlyFTPing all of the web site directories from it to the new server)?Just upload the original data to the new server and then mount the MDFand LDF files within the new SQL server? Or do I restore the backupfiles in the new SQL2000?All of my previous data migrations have been DTS operations from onelive server to another, so no experience with either of the abovescenarios. I'll certainly have a lot more experience at one of them bythe time this weekend is through.Thanks for any help you can offer.
View 1 Replies View Related
Hi there
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
Thanks
Jane
hi, pls tell me any step by step tutorial (with graphic presentation) to restore and backup the database on sql server 2005. thank you.
View 1 Replies View RelatedIs there any software that can make backup of data from MSSQL Server, LIVE - during work - to another machine on CDROM?
Anybody has the experience - good or bad with the CD solution?
Please, can anyone sort this out for me?
What is the difference between backup-restore and export-import data?
Thanks in advance :rolleyes:
I have a problem i set old backup for my database all the new data gone any one know how to reset database to specific date that before the day i restore the old data
i know it may be impassable
hi all
right now , i am still learning how to use sql2005
is it possible if i want to read the backup data without restoring the backup database.? if it possible can you show me how to do it
cheers Arif
arifliminto86
Hello, everyone:
Does any body have the experience to execute data warehouse backup/recover? What I want to know is how to backup/recover database in data warehouse and cubes.
Thanks.
ZYT
Hi,
Is there a preferred backup order for backing up data and log files?
Which is preferable to backup first?
Thank you,
Seb.
I have just started in the scary world of SQL Server admin and am trying to unravel the mysteries of backups etc.
If I run 'BACKUP DATABASE xxx TO DISK = 'D:DB_Backupsxxx.bak' WITH RETAINDAYS = 7' each day, each db backup if appended to the same '.bak' file and the RETAINDAYS protects the backup from being deleted by SQL Server. OK so far. But does anyone understand what criteria is used to decide when to overwrite the older backups? My backup file is getting bigger everyday, with no sign of any of the old data being deleted! Do I have to wait for the entire disk to become full before they start to get overwritten? Or should I just not worry and trust that it will do it all correctly?
Any ideas would be much appreciated.
Is there an equivelant to the SQL Server Agent in SQL server express edition? If not, how can I schedule the backup of the data/log files?
Thanks,
i have a 2005 db with full recovery mode. daily full backups, diff backups and log backups are done through sqlagent. i wanted to make a copy of it on another instance using the restore method with the latest full backup. after i created the new db, i noticed that a few tables were missing and columns were missing from existing tables also. futhermore, the recrods in these tables were not up-to-date either. i did fresh a full backup and tried again and the problem persisted. i aslo tried to restore on the same sql server instance under a different db name and that reproduced the problem.
the database schema was changed a few weeks ago and it seems that i am only seeing a snapshot of the database before the schema change. dbcc checkdb returns no error. the size of the backup file looks reasonable and i seen an increase in size since the schema change which is expected. there is no active transactions in the db and if i generate a create script, it contains proper t-sql that matches the current schema.
what am i missing there? what could i be doing wrong? i am lost here and any help or advice will be greatly appreciated!
I have two databases, one production and other a production copy.
One column of the production table is encrypted by the following syntax.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!'
CREATE CERTIFICATE cert_demo
WITH SUBJECT = 'encryption demo'
CREATE SYMMETRIC KEY symkey_demo
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE cert_demo
-----------------------------------
I have stored procedure to add records to a production table like this.
ALTER Procedure AddRecord
@id int,
@var1 varchar(100)
AS
BEGIN
DECLARE @var2 varbinary(256)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
SET @var2 = EncryptByKey(
key_guid( 'symkey_demo' ),
@var1 )
insert into dbo.Test(id,Data,EData)
values(@id,@var1,@var2)
CLOSE SYMMETRIC KEY symkey_demo
END
---------------------------------
Now I need to decript the data in the production copy database. So I had export production data to the production copy database and also generates the same key and cerificate. When i tried to decrypt the data, no results has returned.
Create procedure ViewData
@id int
AS
BEGIN
DECLARE @var1 varbinary(256)
DECLARE @var2 varchar(100)
OPEN SYMMETRIC KEY symkey_demo
DECRYPTION BY CERTIFICATE cert_demo
select @var1=EData from Test where id=@id
Set @var2 = convert( varchar(100), DecryptByKey( @var1))
print @var2
END
It doesn't print the enctypted data. Please suggest what goes wrong.
Hello,All:
I have two sql 2000 servers,one for production and the other is for backup server,I used the sql agent to create bakup daily in Sql server Enterprise Manager.
Now I want to import the backup data(generated by production server),I don't know how to do it.
Is it possible to do it ? anyone can give me a soluation ?
thanks in advanced!
I created a new database and want to recover data from a backup this script
use master
go
restore database new
from disk='D:
ew.bak'
go
but get an error
Message 3141, Level 16, State 0, Line 1
The database to be restored was named 'ats342'. Reissue the statement using the WITH REPLACE option to overwrite the 'new' database.
Message 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.