If you want to exclude some columns or modify the order of columns, create a format file and specify it as a parameter to BULK INSERT. You can refer to Books Online to learn more about how to use BULK INSERT and how to create a format file.
— Roman Rehak
Return to SQL Server databases FAQs
2. Is there a query to find SQL Server database names created by users?
Can you give me a query to get all the database names (created by users) of one SQL Server?
You can query the sys.databases view or the sysdatabases compatibility view.
— Greg Low
Return to SQL Server databases FAQs
3. How do I upgrade databases to SQL Server 2005 after server upgrade?
I need to upgrade a SQL Server 2000 SP4 to SQL Server 2005. As all my other 2005 servers are "clean installs," i.e. not upgrades, I'd like to take the same approach with this one. So, the plan is to back up the databases, uninstall SQL Server 2000, install SQL Server 2005 SP2 and attach databases. Now, obviously these will be 2000 databases. How do I upgrade the databases to SQL Server 2005, and scripts/stored procedures etc? Can you enclose the necessary links or stored procedures?
The SQL Server 2000 databases will be upgraded to SQL Server 2005 as soon as they are attached to the SQL Server 2005 instance. You will need to change the compatibility level from 80 (SQL 2000) to 90 (SQL 2005) in order to get the full benefit of SQL Server 2005. After you attach the databases to the SQL Server 2005 instance and change the compatibility level, it's recommended that you rebuild your indexes and update your statistics.
— Denny Cherry
Return to SQL Server databases FAQs
4. How to synchronize SQL Server databases on two separate active servers?
I want something similar to mirroring, except the databases on both servers need to be active. Basically, the servers are pulling information into identical databases but need to be in a load balancing arrangement, and if one were to fail the other could pick up the load and have complete and current data. Is there a way to keep databases on two separate servers synchronized without going to a SAN solution?
If this is only for failover purposes you could look at using log shipping or if you are running SQL Server 2005, you could look at using database mirroring. If you need to have both databases active all of the time, you would need to use SQL Server replication.
— Greg Robidoux
Return to SQL Server databases FAQs
5. What does a SQL Server database marked 'suspect' mean?
My SQL Server database is marked 'suspect.' What can I do?
The first step is to relax. Panic will get you nowhere, and SQL Server database problems like this can be stressful! So take a few deep breaths and remember that everything will be okay.
Next, read Tibor Karaszi's "Recommended actions for corrupt or suspect databases."
If you get to step six and still cannot get yourdatabase back up and running, it's time to restore from your last good backup. But you still might have a chance to retrieve any changed data that's in the database. SQL Server 2005 introduces a new database state, called EMERGENCY. This state puts the database into read-only, single-user mode, with access allowed by members of the sysadmin role only. This will make your database accessible and let you get in to pull out any data before doing a full restore.
To put a database into EMERGENCY state, use the following T-SQL, substituting the name of your database for 'YourDatabase':
ALTER DATABASE YourDatabase SET EMERGENCY;
— Adam Machanic
Return to SQL Server databases FAQs
6. How can I copy databases when upgrading to SQL Server 2005?
I am trying to migrate from SQL Server 2000 32-bit with close to 300 databases, to SQL Server 2005 64-bit. I have tried the "Copy Database" feature but it fails to copy any database over. I also need all of the logins moved over. I am open to any suggestions.
I generally find that the Copy Database Wizard is slow and inefficient compared with other methods of moving data. It is best used for cases when you need to do a very quick and dirty transfer of a small amount of data between a couple of servers, and don't want to take the time to do a backup or detach the database.
If you need to move 300 databases, I think you'll discover that the fastest way is to use sp_detach_db on the SQL Server 2000 instance to detach each database and sp_attach_db to re-attach the databases on the SQL Server 2005 instance. Doing so also has the benefit of preserving your database users.
However, even with that method you'll still have to get the logins into the SQL Server 2005 instance. Check out the Microsoft Knowledge base article "How to transfer logins and passwords between instances of SQL Server." This article includes a script that should help you with that task.
Note that you might have to re-map the logins to database users once you're done. To do so, use the sp_change_users_login system stored procedure.
— Adam Machanic
Return to SQL Server databases FAQs
7. Is it possible to back up tables from SQL Server 2005 database to a file?
Can we back up selected tables from a database to a file in SQL Server 2005 standard edition If it's possible, what is the command?
Yes, it is possible to back up selected tables to a file using Bulk Copy Program (BCP) or SQL Server Integration Services (SSIS).
— Greg Robidoux
Return to SQL Server databases FAQs
8. Can a SQL Server database be restored with a corrupt data file?
Can a SQL Server database be restored if the data file is corrupt? The problem is, we do not have any valid backups but do have the current data file and the log file.
If you have the .mdf and .ldf files and they are not corrupt you can do an "attach" to connect the database to SQL Server.
If the files are corrupt and you do not have a good backup, there is not much you can do. There are some companies out there that say they can recover the data. So, I would look them up on the Internet to see what they can do for you.
— Greg Robidoux
Return to SQL Server databases FAQs
9. How to display a SQL Server database report in Excel?
We are working with SQL Server 2000 Enterprise Edition. We have been tasked with developing a report from the SQL Server database, which we then have to display in Excel. This data will be calculated using a query and then placed into specific cells of the Excel workbook for calculation and graphs. Should we use Visual Basic or does this involve other services provided with SQL 2000?
You can use DTS and SQL Server 2000 to transfer data directly to Excel by specifying Excel as your destination end in the data pump. You should consider looking at the Microsoft knowledge base article: "HOW TO: Transfer Data to Excel by Using SQL Server Data Transformation Services" for some details.
— Joe Toscano
Return to SQL Server databases FAQs
10. Can I restore a SQL Server database without a .mdf file?
I have the .ldf file but don't have the .mdf file of my database. Can I restore my database from the existing .ldf file?
No, this is not possible. The data is all stored in the .mdf file, so without that you won't have any data.
— Greg Robidoux
Return to SQL Server databases FAQs
|
| |
|
Didn't find what you were looking for?
Visit our SQL Server IT Knowledge Exchange where you can ask a question, debate a technical problem and share tips and tricks with other IT professionals.
Check out our SQL Server expert's blog SQL Server with Mr. Denny for more advice on managing your database system.
|
|
|
|
