I have an MS SQL server running in the office, which is used for more than one application - the same server hosts databases for at least two totally seperate applications. One of these databases is called CApplication, and another iscalled CSystem, and these two together support an app called Client.
All I want to do is create CApplication2, which is an exact one-time copy of CApplication. I don't need it to update, or synchronise, or link to any other database, or anything. I want to write an interface for this DB, and I don't want to use the live one.
Do I "detach" then "copy" then "reattach" or something? If somebody could give me some rather lame step-by-step instructions I would really appreciate it - I can take this databasse offline, but I really should try to put it back online as soon as possible, and it is essential that nothing changes in between!
I'm a bit nervous about this, if I break this database I'm a dead man. Thanks guys!
I am trying to do a group by and left join together but it does not seem to be working.
SELECT sb.[BrowserID] ,sum([BrowserTotal]) ,[BrowserName] FROM [IISLOG_REPORTS].[statistics].[browser] sb left join [IISLOG_REPORTS].[lookups].[browser] lb on sb.browserid=lb.browserid GROUP BY sb.[BrowserID] ORDER BY sb.[BrowserID]
The above code works without the blue line there (which is being fetched from the lb table). Any suggestions for this? I would need all 3, including the BrowserName. Thanks
Hey all, sorry for asking such a grunt question, and thanks in advancefor any help...My hosting ISP offers access to their MS-SQL database, and myunderstanding is that it's generally simplest to use some sort ofclient app to do stuff like adding tables and whatnot.SQL Server includes Enterprise Manager, but since my ISP is running theserver, I don't need my own copy of SQL Server, just the client.Any tips? I couldn't seem to find an appropriate download on theMicrosoft site...thanks again, -Scott
I'm totally new to SQL Server 2k5 and need to do something rather basic: import some CSV files into tables. I'm getting translation errors and would like to know what's the best way to cast the strings before inserts.
I'm doing the import in BI Development Studio.
Current situation: Created connection managers to csv files created SQL server destinations pointing to the tables connected them directly with a dataflow path Ran the packadge: one import went just fine, the other one complains about conversion errors like "Conversion DT_STR and DT_I4 not supported"
Both tables have the same kind of fields (varchar, float, datetime, int)
I looked at converting the data using a transformation but am somewhat confused of which one to use.
What's the best way to transform the data before insert: derived column, import column or data conversion? Or something else I overlooked?
My hosting company just "upgraded" to mylittleadmin for SQL, we can no longer connect and manage using enterprise manager or local other tools. I need the ability to connect locally, so I'm changing hosting company. The problem is I need an updated backup to transfer to the new server, even tech-support can't figure out this new "upgraded" system. The only help they offered was try the "dump" command, but that only give me a Incorrect syntax near '20' error. This is what I tried using the online Query Analyser: dump dbname dump 20twenty
We have a SQL Server 7.0 database running with trunc. log on chkpt andselect into/bulkcopy checked and need to develop a backup strategy.One of our DBAs insists that since the transaction log is beingtruncated, we can't do a hot backup (a FULL backup in multiuser mode)because if a transaction comes through during the backup it will leavethe backup in an inconsistent state. I'm skeptical, but I don't knowhow SQL Server 7 avoids this problem.If SQL 7 is not truncating the transaction log, it uses thetransaction log to roll forward changes that occurred during thebackup. Obviously if it's truncating the log it must have some way toapply these transactions anyway (such as not doing a checkpoint duringthe backup, backing up the log at each checkpoint, etc.).Can anyone confirm that a hot backup will be valid when trunc. log onchkpt is checked? Does anyone know how SQL 7 accomplishes this?Thanks!James
BACKUP DATABASE CorporateComplaints TO DISK = 'E:MSSQLBACKUPInsight_Dump.BAK' WITH INIT -- Declare the variable to be used. DECLARE @MyCounter INT DECLARE @MySpid INT DECLARE @MYSQL varchar(50)
-- Initialize the variable. SET @MyCounter = (SELECT Count(spid) FROM SysProcesses WHERE SysProcesses.dbid = ( SELECT dbid FROM SysDatabases WHERE SysDatabases.name = 'CorporateComplaints2' ))
print'mycounter '+ cast(@MyCounter as varchar (5))
-- Test the variable to see if the loop is finished. WHILE (@MyCounter > 0) BEGIN -- Kill process. EXEC sp_refreshview ActiveProcessesView SET @MySpid = (SELECT Min(Spid) FROM ActiveProcessesView) SET @MYSQL = 'KILL ' + CAST (@MySpid AS Varchar ) EXEC (@MYSQL)
-- Increment the variable to count this iteration -- of the loop. SET @MyCounter = @MyCounter - 1 END GO
RESTORE FILELISTONLY FROM DISK = 'E:MSSQLBACKUPInsight_Dump.BAK' RESTORE DATABASE CorporateComplaints2 FROM DISK = 'E:MSSQLBACKUPInsight_Dump.BAK'
WITH REPLACE, MOVE 'CorporateComplaints_Data' TO 'E:MSSQLDATAcorpcomps2data.mdf', MOVE 'CorporateComplaints_Log' TO 'F:MSSQLDATACorpcomplaints2Log.ldf'
error event id 17055
Can any body help wht is wrong in script. I dont understand what is there in middle part its killing some process. what is need.
what I can usderstand its taking a backup of database CorporateComplaints and restoring it to CorporateComplaints2.
I thought I would start learning how to backup my SQL Server 2005 database using the simple recovery method. Using the SQL Server Management Studio I started the backup task. In the Back Up Database dialog box the Recovery Model is set to Full and disabled (grayed out). It does not give me the option to switch to the Simple Recovery Model. Is there a setting I need to adjust that will allow me to switch to the Simple Recover Model?
Been lurking for a while, and new to MS-SQL, and didn't see an answer to a question that I have.
Running MS-SQL2000. SP2
Have a need to make a backup of a database at midnight on the last day of the month and to then make that data available to Accounting for end of month reporting.
The easy part was to schedule a full backup to occur at midnight on the last day of the month. The db was backed up to the file name Chorro_MonthEnd.BAK.
I then added to the scheduled job the following T-SQL:
RESTORE DATABASE [Chorro_MonthEnd] FROM DISK = N'D:Data FilesMicrosoft SQL ServerMSSQLBACKUPChorro_MonthEnd.bak'
The question(s) that I have are:
1) The data in the db Chorro_MonthEnd should be over-written with the data from the restore?
2) Since the Chorro_MonthEnd db exists, is MOVE necessary as part of the restore command? I have been told that if you don't use the MOVE command, it puts the original file names back into wherever directory structure they were backed up from. The concern is that it could overwrite the original db MDF/LDF files.
What we are trying to avoid is having someone from the IS side of the house having to be on-site to restore a db for Accounting. IS type get cranky if they don't get enough sleep :D
Pardon me and my ignorance for asking this question. I just want to understand the backup architecture more clearly. According to BOL (both in SQL 2k and SQL 2k5) in simple recovery mode trasaction log backup is not possible since the log is truncated on checkpoint which is true. Also we know that FULL backup backups both the db and transaction log as well.
My question is what happens when a database is in simple recovery mode and a full backup is done. since the tran log cannot be backed up does only the db backup is done when a full backup is done?. What exactly happens behind the scenarios?. Is it that only the active log gets backed up when a full backup is done in simple recovery mode?. I am trying to understand how a full backup in simple recovery mode behaves without contradicting the full backup architecture and that the veracity of the statement (both db and tran log backup in full backup mode) holds true for a simple recovery scenario.
MVPs/ MS Experts if you could Please explain it in detail, I would really appreciate it.
When we do a full database backup manually, we are seeing the trn file reflect the current date/time, but we are not seeing the mdf reflect the new date/time. And we are not seeing the transaction log file decrease in size. the recovery mode is set to full, do we need to change to simple to see both the mdf being backup'ed?
I have a small, ~10GB SQL 2008 R2 database, that was setup with simple recovery. We do full backup each night at midnight when no one is using the database. Is there any problems with doing differential backups during the day when users may be writing to the database? Could I even do hourly differential backups while users are using the database? I'm conflicted about switching over to full recovery mode and using transaction logs to have the ability restore data between backups. If I can do a couple daily differential backups while users are using the database during the day, in addition to our nightly full backups, than I live with simple recovery mode.
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?
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday) 1 1 a b c d e m 1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using where (media.mediaTyple like 'thumbnail' or media.mediaType is null) but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
On the SQL Server the Event Viewer shows the same messages and errors every evening between 22:05:00 and 22:08:00. The following information messages are shown for every database:
"I/O is frozen on database <database name>. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup."
"I/O was resumed on database <database name>. No user action is required."
"Database backed up. Database: <database name>, creation date(time): 2003/04/08(09:13:36), pages dumped: 306, first LSN: 44:148:37, last LSN: 44:165:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{A79410F7-4AC5-47CE-9E9B-F91660F1072B}4'}). This is an informational message only. No user action is required."
After the 3 messages the following error message is shown for every database:
"BACKUP failed to complete the command BACKUP LOG <database name>. Check the backup application log for detailed messages."
I have added a Maintenance Plan but these jobs run after 02:00:00 at night.
Where can I find the command or setup which will backup all databases and log files at 22:00:00 in the evening?
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.
I use the Transact-SQL BACKUP statement in Visual Basic to backup my local MSSQL Database. It give me this error
Error 3041
BACKUP failed to complete the command BACKUP DATABASE [BCFPC] to BCFPCBKP
I already created a backup device called BCFPCBKP and it is backup to the disk.
I tried to run the same BACKUP statement in SQL Query Analyzer and it worked fine. I tried to run my VB application in another PC. It worked fine when i use this command remotely. Can anyone tell me what's the problem?
Using Ola Hallengren's scripts I do a full backup of a database on a Sunday. Then differential backups every 6 hours and log backups every hour. I would like to keep a full week of backups based off the full backup done on Sunday. Is there a way for me to clear out the diff and log folders after the successful full backup on Sunday nights?
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.
We take a full backup in the early morning and hourly transaction log back during the working hours for one database in the production server. The application team made certain changes to the design of the said database in their development server. The backup from the development server was restored to the production server during working hours. After the restoration should we take a full backup before next transactional logbackup? Would the transactional log backup with out a full backup after the restoration of a database be valid?
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
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?
I'm looking to schedule a maintenance plan for my databases which I have done.I'd like this database to be copied to another folder and the name altered to include the file name and the current date time stamp.Is this possible in the scheduled maintenance plan?
I have a database that is just over 1.5GB and the Full backup that is 13GB not sure how this is since we have compression on for full backups and my other full backups are much smaller than there respective databases...Now my full backup is taken every Sunday night and the differentials are taken every 6 hours after the full backup. Now I have been thrown into this DBA role with little to no experience just what I have picked up and read. So my understanding of backups are limited but what I think I understand is that we take a full backup and the differential only captures what changes in the database so my question is why is my database 1.5GB but my differential is 15.4GB? I have others database that are on the same instance and don't seem to have this problem. I also just noticed that we do not rebuild the index before a full backup like we do on other instances...
This is probably a simple question but I have to ask it anyway. When backing up I can backup to a file on my local drive but I also can create a device to the same location on my local drive. Is this doing the same thing. If I so desire to backup to the local drive(bear with me) what is the difference between creating a device and a file called mybackup or just choosing to backup to a file called mybackup? Should I always create a device? I know these are dumb question but....
