If you are please let me know what software you are using. We currently are using Legato to back up our SQL Servers and it has
proven to be a real nightmare. Backing up to disk is not a solution for me since we have too many servers(16) and medium to large
Dbs. I would love any ones input into your current backup solution. Are you happy with backup time and restore time and is it
reliable?
How am I doing so far? I am trying to Insert the user_id if it does not allready exist in the table. But it does not like the value I am giving it in the INSERT statement.
What can I do?
ALTER PROCEDURE dbo.ap_Update_Usr_Type_Data
-- define parameters
--@passUserId int
AS
--Set @passUserId = UserId
SELECT [user_id] As UserId
FROM CC_host.dbo.usr_table As t1
Where(email LIKE N'%@%' And email LIKE N'%usa.com') Order By UserId
If Not Exists (Select [user_id] As UserId2 From Usr_Type_Data WHERE UserId2 = UserId)
INSERT INTO Usr_Type_Data (user_id, CustomerTypeId) VALUES(t1.UserId, 7)
GO
The error I am getting in Query Analyzer (SQL SERVER 2000) is:
The name 'UserId' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Hello Everyone, I am still learning Sql and need some help with a query. On the master table, I have an audit log. I have a trigger set up to run after an update. I only want to perform an update if the value is not true or one record updated will result on 1800 records in you audit log. I tried to disable and enable the trigger from within the trigger unsuccessfully. So now I am trying an If statement, in a trigger Maybe this is where you can help ?
I get the following error
Msg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Extract from Trigger.If (Select Discprency from Master_Tablewhere ((Pro1 <> Pro2 and Pro1 <> Pro3) or (Pro2 <> Pro1 and Pro2 <> Pro3) or (Pro3 <> Pro1 and Pro3 <> Pro2) or(Pro1 = Pro2 and Pro1 <> Pro3 and Pro2 <> Pro3) or(Pro1 = Pro3 and Pro1 <> Pro2 and Pro2 <> Pro3) or(Pro2 = Pro3 and Pro1 <> Pro2 and Pro3 <> Pro1))) = 0Begin Update Master_Tableset Discprency = '1'where Discprency = 0EndElseBeginUpdate Master_Tableset Discprency = '0'where Discprency = 1End
Thanks for your time.
Hi Folks,
Just me looking for some advice again on my complicated logon / registration process with DNN.
I ask my users to enter their league membership number as a password - this is then checked in a standalone table of data imported from FoxPro to test whether the membership no. i.e. password is current or not .... this all works fine. I do have a bit of a problem with the initial registration on the site however ...
All membership numbers acutally consist of a league division followed by the membership number so e.g. the member number 6789767 actually consists of two pieces of information - the first character is the league division and 789767 is actually the foxpro member number. So what I do is strip the first character away on sign in and registration when I am testing to see whether the user is a current league member e.g. ...
Dim MemberID As String = objUser.IsMember(Server.HtmlEncode(Mid(txtpassword.Text,2,10)))
This allows me to grab the real membership number out of the password and also helps to stop password guessing as no one will realise the first character is being stripped away. Fine except with the AddUser code in register.ascx.vb .... I don't know why but when I try to strip away the first character in a similar way in this line
Dim UserId As Integer
UserId = objUser.AddUser(PortalId, Server.HtmlEncode(txtFirstName.Text), Server.HtmlEncode(txtLastName.Text), Server.HtmlEncode(Address1.Unit), Server.HtmlEncode(Address1.Street), Server.HtmlEncode(Address1.City), Server.HtmlEncode(Address1.Region), Server.HtmlEncode(Address1.Postal), Server.HtmlEncode(Address1.Country), Server.HtmlEncode(Address1.Telephone), Server.HtmlEncode(txtEmail.Text), Server.HtmlEncode(txtUsername.Text), objSecurity.Encrypt(_portalSettings.HostSettings("EncryptionKey"), Server.HtmlEncode(Mid(txtPassword.Text,2,10))), IIf(_portalSettings.UserRegistration = 1, CStr(False), CStr(True)), UserId)
well it does the right thing and only adds the password minus the first character to the users table however when the user is pointed back at desktopdefault the users name is not displayed, the additional tabs available to registered users do not appear however the logoff link is active ... strange why this should be so but I can't work out what the problem is. So I was thinking that another way to approach this would be a trigger on the users table so that when a new record is inserted the trigger runs and strips the first character off the password before the password is committed to the table.
Can anyone show me a simple trigger that will do this / comment on why the problem with AddUser occurs .... not au fait with triggers but a trigger could make me happy in this instance I think.
Look forward to a reply
Laura D
I just wanted to wish everyone a Happy New Years and I wanted to thank all submitters to this forum for breaking up my code monkey workday and cutting into my productivity and I have found many of these discussions helpful in my preperation for my long procrastinated MCDBA exams.
Get hammered and do not drive.
Just got cut loose...
have a safe and happy new year!
anyone seen my salt shaker?
damn what a time to misplace it....
I am trying to create a very minimal auditing system for a series ofdatabases.I am in the process of writing Update triggers for 5 Tablse. I willwrite a trigger for each table-the trigger's function will be toINSERT a row into my MasterChanges table everytime ANY data is changedin each of the 5 tables.I have set up MasterChanges to capture the following:what Table the change was made inwhat Field the change was made inwhat the Field was changed towhen this change occured.I have used the Columns_Update() function to enable the trigger tosurvey all 22 fields in the first table (my first attempt at this). Ican hardcode in the table's name, as there will be a separate triggerfor each table.The problem is how to identify the NAME of the COLUMN that was updatedand have the trigger write that NAME into MasterChanges, as well asthe new value of that column.My first thought is a very unacceptable hardcoding of multiple IFSTATEMENTS for each of the 22 fields.ie:IF(colA) then...elseif(colB) then...elseif(colC) then...etc.There must be a better way to do itI assume this would be done using Columns_Update() in some way, butcannot figure it out.Can anyone help?Thanks,Trevor Fairchild
View 2 Replies View Relatedremember if you drink too much, get a taxi and charge it to your company.
View 4 Replies View RelatedThe space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
View 3 Replies View Related
Hi,
I'm trying to work out how to backup an ASP.NET 2.0 site which uses an SQL 2005 Express database located in the App_Data directory. It seems that the database file cannot be copied while in use so I guess that either leaves taking the site offline or maybe the database can be backed up to another location using a scheduled task?
Any suggestions?
Thanks,
Dale
What backups options do you have if you do not have a tape drive connected to your SQL 7.0 Server ? Can you run a SQL Maintenance Plan and backup the databases to a remote server. If not, can ARCServe 2000 backup the SQL backups to another server with a tape drive ?
View 1 Replies View RelatedI'm having trouble backing up two databases to tape. Should I be using a backup_device?
have a job defined with the following in it:
backup database test1
TO TAPE = '.Tape0'
with ...
go
backup database test2
TO TAPE = '.Tape0'
with ...
go
where should the init go?
I've set up several backups in the past using the backup screen. I've also set up a Maintenance Plan to back up my database using MyDbName* where the * is the familiar suffix automatically generated by the system when the backup runs every night.
The trouble is that, even though I've deleted old maintenance plans and scheduled backups, they're not going away. Every morning, I see new nightly backups of old backups etc., in the backup folder. Furthermore, the maintenance plan I want to backup my database isn't creating a backup file.
How can I make the old backups stop running, and make sure the new backup runs? I've gone through everything and can't seem to find any residue of these old scheduled backups--yet they still run! Can anyone help?
We are having problems with our tape drives so we are attempting to back up our databases to disk. I have a whole server on the network I could use for this. When I use the backup that comes with SQL 7.0 it won't allow me to choose a network drive. It looks like it must be a local drive. Is there a way around this so I could back it up to this other server. I jave 32 gig available on the server which would be plenty. We don't have enough disk space on the local drives to back it up. We have been having problems with our current tape drive company Exabyte. Does anyone have any recommendations. We are thinking of switching to another company.
View 3 Replies View RelatedI currenntly backup several databases to thier own unique device. Each backup is executed as a full backup (databases are small)and they are set to expire after 7 days. I checked my devices after 7 days and they continue to grow in size - is this right? I expected the device size to hold constant once I had a rolling seven days of backup (The databases don't change in size from day to day) Any ideas?
View 2 Replies View RelatedI'm using the database maintenance plans to backup my databases. We are backing them up to tape. Is there a way within the database maintenance plan to tell it to format the tape instead of appending it to the tape.
View 1 Replies View RelatedI couldn't restore a SQL 6.5 Backup in my SQL 7.0, because it is generating an error in SQl 7.0 that shows an Incompatible types.(media)
How can I restore a SQl 6.5 Backup into my new SQL 7.0 ??? Please Help me, because I'm a Beginner in SQL 7.0.
Thanks in Advance.
I am trying to make sure I have my backups scheduled correctly and in the correct spot. We have a tape drive on the server which is backing up the databases each night. The logs I have them currently being backp up once an hour to disk. My question is would it better to have the logs back up to the tape drive or to disk. The server where I'm backing up the logs to has 2 drive C and D. Where D contains the data and C contains the application/program files. I'm assuming I wouldn't want it on D since if there was a crash with that drive and we lost the data we wouldn't be able to get the logs for that day so it would be better to put it on C. What are most people doing to backup there logs. At the end of the day after a full backup I clear the device that contains the log backups. It this good practice?
Stacy
How do you backup to a remote drive?
The book says that inorder to backup to a remote drive, you need to log on with a domain NT account with administrative privileges.
I am unable to log on with a domain account and backup to a mapped drive.
For example, S1 and S2 are two servers in the domain D1. The NT domain administrative account is A1. I need to backup a database D1 on S1 to a location on S2. For this I do the following.
On S1,
sp_addumpdevice 'disk','dump_dev','s2sharenamefilename.bak'
On executing, I get,
(1 row(s) affected)
'Disk' device added.
Then when run the command,
backup database D1to dump_dev
I get the following error,
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'dump_dev'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
The event log shows the following error message:
BackupDiskFile::CreateMedia: Backup device 's2sharenamefilename.bak' failed to create. Operating system error = 1326(Logon failure: unknown user name or bad password.).
Please let me know how to back up to a mapped drive using TSQL and Enterprise Manager.
Thank you.
Praveena
I have a dump device configured to append the backup file each time a backup is run. This file has grown extremely large. Im looking for a way to go into the file and delete all but the last 2 or 3 backups. Is there a way to do this?
Thanks
Hello,
is there any backup software (archserve,ultraback) that has a sql agent for 7.0 ? I have not found one yet.
Charlie Principato
Dialamerica Marketing,Inc.
I have a SQL2K/SP3 database with an MDF of 4GB and an LDF of .5GB. If I do a backup using Enterprise Manager, whether manually or via a DB Maintenance Plan, I get a .BAK of approx. 2GB. If I do a backup using:
USE master
EXEC sp_addumpdevice 'disk', 'RAMScopyBAK', 'F:MSSQLBACKUPRAMScopyRAMScopy.BAK'
GO
BACKUP DATABASE RAMScopy TO RAMScopyBAK
GO
I get a .BAK of 24GB !!!
In the end I want a T-SQL step to be part of a SQA job that will back up the database, thus the use of the dumpdevice command. I could create a maintenance plan, disable the jobs it creates, and then start them via sp_start_job, but this is just an example of a systemic problem; i.e. I'd have to do individual maintenance plans for more than one database.
Help?
Thanks,
Al
For a lot of smaller/medium sized systems that I administer, I tend to use SQL Server to backup to a backup device and then let the normal system backups backup the dump files to tape.
However, I am taking on a new system with approx 900Gb of data. We dont want to buy another 900Gb of disk to secure the databases ! I have found the SQL Agents in Arcserve 2000 to be unreliable to say the least. (we dont want to stop the SQL Services either)
Would anyone recommend alternative software to backup large databases.
Thanks
Tom
Hello,
Can anyone guide me which method is best to dump databases to a tape.
Using Sql server to dump database to tape or dumping of the file to tape.
Thanks
Sejal
Is there a way in SQL 2k running on win2k adv. server to map a scsi tape device from one sql server to another so database backups can run to the remote tape device??
View 3 Replies View Relateddatabase backups and differential backups
i took database back up on 01-jan-2005 and from 2nd jan onwards im taking differential backups. if i want to restore the database to a new system, is it sufficient that i restore the back up which i took on 1st jan , and the most recent differential backup ? will the entire data till the last back up date be restored?
Hi Guys,
I'm pretty new to SQL Server. I have a ? regarding backups.
I have a database which I'm backing up everyday using BACKUP DATABASE db TO db_bak WITH INIT. This job runs everyday successfully, but I'm having an evergrowing transaction log file. I presumed a full database backup will apply transaction logs and will truncate the log files. Isnt it so, or am i missing some concepts? This database can have a data loss of 1 day which is covered by my full database backup job. I dont need a transaction log backup. But to reduce log file size should I do a log backup?
-Sunil.
I have some backups scheduled on my sqlserver.most of them are differentail backups.But among them some have options 'Append to media' and some of them are 'Overwrite existing media '. But i dont know which of them falls into which category.How can i find that and can i cahnge 'Append to media' to 'Overwrite existing media '?If yes how can i do that?Does it effect my db if i change it?
INfact which one is the best option ('Append to media','Overwrite existing media ') among these two?
Thanks.
Just Read this :-
A complete database backup is very simple to execute and use in recovery. If your data doesn't change often, you might simply schedule a nightly full backup of your database. Even if you need more frequent backups, regularly scheduling a full database backup each hour might be sufficient (if your database is small enough).
Now what does a small database mean , can someone please let me the size in (GB) that a DB is considered to be small (even a range would do )
& How much time does the backup take , or should taken on a dual processor server. I mean need to get a rough idea
Thanks...
I am trying to restore a database to a point in time preferably a minute early before a crash. Initially i have a table called orders where i inserted new rows but it has later been dropped. If i later want to recovered that drop table along with the inserted rows before the drop
would this be the correct procedure using the stopat?
RESTORE DATABASE DB1
FROM 'c:db1.bck'
WITH RECOVERY, STOPAT = 'mmm dd, yyyy hh:mm AP'
the following is what i have worked out so far. Would it make sense?
1. I backed up the database.
backup database db1 to disk = 'c:db1.bck' with init
2. I Added 2 rows to two tables Orders and OrderDetails table.
insert into Orders(salesproductID,OrderQty,status)
values(' 1','5','6')
insert into orderdetails(salesproductID,OrderQty,ProductID)
values('9','6','8')
3. I backed up the log
backup log db1 to disk = 'c:db1.bck' with init
4. here I recorded time using the GETDATE() function.
select GetDate()
5. I added Add 3 more rows to the two tables like this
insert into Orders(salesproductID,OrderQty,status)
values(' 2','3','5')
insert into Orders(salesproductID,OrderQty,status)
values(' 4','2','9')
insert into Orders(salesproductID,OrderQty,status)
values(' 3','2','1')
insert into orderdetails(salesproductID,OrderQty,ProductID)
values('8','4','2')
insert into orderdetails(salesproductID,OrderQty,ProductID)
values('6','3','2')
insert into orderdetails(salesproductID,OrderQty,ProductID)
values('3','7','9')
6. I Performed a differential database backup.
backup database db1 to disk = 'c:db1.bck' with differential, init
7. I then recorded the second time: Time 2.
select getdate()
8. I Dropped the OrderDetails table.
drop table orderdetails
9. I then record the third time: Time 3.
select getdate()
10. I then Added 3 rows to the Orders table.
insert into Orders(salesproductID,OrderQty,status)
values('7','21','10')
insert into Orders(salesproductID,OrderQty,status)
values('14','12','19')
insert into Orders(salesproductID,OrderQty,status)
values(' 13','12','11')
11. I Backuped the log and record the fourth Time 4.
backup log db1 to disk = 'c:db1.bck' with init
select getdate()
Now when i tried to add rows to the OrderDetails table it does not exist but I like to get back the dropped table OrderDetails and not lose the three rows added to the table Orders. the only thing i would know is that the table orderdetails existed at time 2 and not at time 3
I am in search of a simple .bat file or .vbs script that I can use tostop my SQL database server. I am looking to schedule a script of sortsto initiate at a certain time every day. This script needs to be able tostop my server and/or initiate a backup. The script should be able to beran remotely from the lan. can anyone help me?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View Related
hi ,
I have small doubt on backup and restore .if we taking daily two transactional backups(1 pm and 5 pm) , one differential backups(7 pm).and weekly once full backup monday 9 am.
if some one deleted data on wednesday 11 am . how can we get back that data.i tryed in my system but i failed to get back that data.
pls help some one ...
thanks
manoj.
SQL 2005
I have a scheduled back up running at 1am everynight which after a few problems is running ok.
Can I get it to overwrite the existing nights back up? no data has yet to be added to the database as we are still testing yet the back up is doubling in size each night.
Not really a SQL specialist this has been dropped on me so go easy on me please
Thanks