SQL Server database renaming, data export, and other issues

The first problem is database rename: In the Enterprise Manager, you cannot rename the database directly. You can only perform operations in the query analyzer.

Create proc killspid (@ dbname varchar (20 ))
As
Begin
Declare @ SQL nvarchar (500), @ temp varchar (1000)
Declare @ spid int
Set @ SQL = 'Clare getspid cursor
Select spid from sysprocesses where dbid = db_id (''' + @ dbname + ''')'
Exec (@ SQL)
Open getspid
Fetch next from getspid into @ spid
While @ fetch_status = 0
Begin
Set @ temp = 'Kill '+ rtrim (@ spid)
Exec (@ temp)
Fetch next from getspid into @ spid
End
Close getspid
Deallocate getspid
End
-- Create a stored procedure
-- Exec killspid 'test1' -- call the stored procedure to close the connection.
-- Exec sp_renamedb 'test1', 'test2' -- rename the database

 

You can use the above method to rename the database. Of course, you can also choose database> all tasks> detach database> clear connection in the Enterprise Manager, and then directly call

Exec sp_renamedb 'test1', 'test2'

 

The second problem is changing the database owner: select the database to be changed in the query analyzer and execute the following code to newowner as the new owner.

Exec sp_MSForEachTable 'SP _ changeobjectowner ''? '', ''Newowner '''

 

The third problem is the data import and export of data tables: when data is imported and exported, database objects are often lost, such as primary keys, identifiers, and default values, the solution is to select the third item in the step of table copy or query.

Copying objects and data between SQL Server databases

Select next. If you only want to operate a part of the table rather than the entire database, cancel the copy all objects option and click "select" next to it to open the select object dialog box, select the database object to be operated, and click Next to complete the operation.

 

The fourth problem is to replace the field content: If your database is injected with virus code and there is no backup, you have to replace it by yourself.

Update dunktopic set field = replace (field, 'replaced content', 'replaced content ');

 

The fifth problem is the table owner problem in the database: in some cases, if the 80040e21 error occurs when you access the website after you restore the website database,

Error '80040e21' ODBC driver does not support required attributes

This may be caused by a problem with the database owner. The solution is to change the database owner to dbo or another user using the method in question 1.

The sixth problem is data restoration. After the database is restored, the internal server 500 error may occur inexplicably when accessing the website, because the database user is not assigned the database permission, the feature is that the login of the database user is gone. Solution: Delete the user without a login name. In the security settings of Enterprise Manager, select the user without the login name and assign the datawrite and dataread permissions of the restored data to the user. We recommend that you do not assign the owner permission for security purposes.