Description of SQL Server database renaming and data export

Time:2022-5-13

The first problem is renaming the database: you cannot rename the database directly in the enterprise manager, but only in the query analyzer

Copy codeThe code is as follows:
create proc killspid (@dbname varchar(20))      
  as      
  begin      
  declare     @sql     nvarchar(500),@temp   varchar(1000)  
  declare     @spid     int      
  set     @sql=’declare     getspid     cursor     for          
  select     spid     from     sysprocesses     where     dbid=db_id(”’[email protected]+”’)’      
  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 slave stored procedure above
— exec killspid ‘test1’ — call the stored procedure to close the connection
  — exec sp_ Renamedb ‘test1’, ‘test2’ – Rename database

You can rename the database by using the above methods. Of course, another method is to select database – > all tasks – > detach database – > clear connections in the enterprise manager, and then call directly

Copy codeThe code is as follows:
exec sp_renamedb ‘Test1′,’Test2’

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

Copy codeThe code is as follows:
exec sp_MSForEachTable ‘sp_changeobjectowner ”?”, ”newowner”’

The third problem is the import and export of data table data: people often lose database objects when importing and exporting data, such as primary key, ID, default value, etc. the solution is to select the third item in the step of making table copy or query
Copy objects and data between SQL Server Databases
Select next. If you only want to operate a part of the table instead of the whole database, cancel the option of copying all objects, click the following selection to open the select object dialog box, where you can select the database object to operate, 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 yourself

Copy codeThe code is as follows:
Update dunktopic set field = replace (field, ‘replaced content’, ‘replaced content’);

The fifth problem is the owner of the table in the database: in some cases, if an 80040e21 error occurs when accessing the website after you restore the website database,
Error ‘80040e21’ the ODBC driver does not support the required properties
It may be caused by the problem of database owner. The solution is to use the method in problem 1 to change the owner of the database to dbo or other users.
The sixth problem is the problem of data restoration. After the database is restored, the internal server 500 error will appear inexplicably when visiting the website. In fact, it is because the database user is not assigned the database permission, which is characterized by the lack of login of the database user. The solution is to delete the user without login name. In the security settings of enterprise manager, select the user without login name just now and assign it the dataWrite and dataread permissions of the data just restored. For security reasons, it is recommended not to assign owner permissions.

Recommended Today

Oeasy teaches you to play VIM – 17 – # up and down

Up and down Recall the last lesson andfRelated is to jump to towardsfrontJump to yesf towardsafterJump to yesF andtRelated is to stick towardsfrontClose yest towardsafterClose yesT anda lowercase letterRelated isforward forwardJump isf forwardClose yest andCapitalizeRelated isbackward backwardJump isF backwardClose yesT And continueFind directionRelated is;、, Keep directionyes; Change directionyes, Up and down This time, it is […]