What should I do if I lose the LDF file?

How to import mdf ldf in sql2005ee

11:38:30 | category: Server | font size subscription

Method 1:

In the SQL Server 2005 database, a misoperation occurs. After the database is detached, the log file is deleted directly, and an error is appended, so it cannot be appended. After the following solution, the database is attached successfully, the procedure is as follows:
Step 1: first create a database with the same name, stop SQL server2005, overwrite the original. MDF database file, and restart the database.
Step 3: run the following code in the query Analyzer:

Alter database your. MDF file name set emergency '-- set the database to emergency

Use master

Declare @ databasename varchar (255)

Set @ databasename = 'your. MDF filename ''' -- Your. MDF filename

Exec sp_dboption @ databasename, N 'single ', N 'true' -- set the target database to single-user status

DBCC checkdb (@ databasename, repair_allow_data_loss)

DBCC checkdb (@ databasename, repair_rebuild)

Exec sp_dboption @ databasename, N 'single ', N 'false' -- set the target database to a multi-user State




Run the above Code at the same time. The error "the other files in the database do not match the main file of the database..." may occur. Please retry the above Code multiple times.

Method 2:
Connect to the data in SQL Server mangement studio, select create query, and then execute the statement
Exec sp_attach_db @ dbname = n 'dbname',-> Generate a database name
@ Filename1 = n'd: \ filename. MDF ',
@ Filename1 = n'd: \ filename. ldf ',
If the database is read-only:
Open SQL Server Configuration manger open SQL Server express attributes
Restart the built-in account network service> local system SQL Server 2005 Express
 
Method 3:
1,Import the existing database (*. MDF) file in sqlserver 2005 to connect to the database in SQL Server Management studio, select create query, and then execute the statement
Exec sp_attach_db @ dbname = n 'sql2005db ',
@ Filename1 = n'd: \ sql2000db. MDF ',
@ Filename2 = n'd: \ sql2000db. ldf'
Data can be imported into sqlserver2005.
2,Directly copy data files
Copy the data files (*. MDF) and log files (*. LDF) of the database to the target server and use the statement in SQL Server Query analyzer for recovery:
Exec sp_attach_db @ dbname = 'test ',
@ Filename1 = 'd: mssql7datatest_data.mdf ',
@ Filename2 = 'd: mssql7datatest_log.ldf'
In this way, the test database is appended to SQL Server and can be used as usual. If you do not want to use the original log file, use the following command:
Exec sp_detach_db @ dbname = 'test'
Exec sp_attach_single_file_db @ dbname = 'test ',
@ Physname = 'd: mssql7datatest_data.mdf'
This statement only loads data files. log files can be automatically added by the SQL Server database, but the data recorded in the original log files is lost.
3,Additional methods
On the SQL 2005 console, right-click "Database" and select attach.
4,SQL2000 databases generally only have two files: MDF and one LDF. You can directly load the file to sql2005, and sql2005 will automatically add something.
How to view the relationship diagram when migrating a database from SQL2000 to sql2005
Select the database and create a query. Enter the following script:
Exec sp_dbcmptlevel database_name, 90
Use database_name
Exec sp_changedbowner 'sa'
Database_name indicates the name of the database.
It may take some time to run. After the operation is complete, click "Graph" and refresh. You can see the graph.
Some commands that may be used to migrate mssql2000 database files to mssql2005 -- modify the object owner
Exec sp_changeobjectowner 'user _ database', 'dbo ';
Exec sp_changedbowner 'sa'
-- Check empty users
Sp_change_users_login 'report'
-- Fix empty users
Sp_change_users_login 'Auto _ fix', 'mydb', null, 'mydb'
-- Merge physical files, delete files, and clear active transactions
-- And null. Run the following Transact-SQL statement in the compressed data or information file:
DBCC shrinkfile ('logical <File Name> ', emptyfile)
-- To delete a file from a database, run the following statements:
Alter database <Database Name> Remove File <logical File Name>
-- Rename a logical File
-- Specifies the logical name of the data file or log file to be modified by using the name parameter, naming the logical file name, and specifying the new logical name of the file by using the newname parameter. Rename the logical file and run the following statements:
Alter database <Database Name> Modify file (name = <current_logical_name>, newname = <new_logical_name>)