MS SQL: Reset identity seed

Occasionally, I need to reset the value of the identity column in MS SQL database. I keep forgetting how to do this, and have to search all the time.
So here is a note to myself:



Backup & Restore MS SQL 2000 Database

This is a short note I used at work to back up and restore the a MS SQL 2000 database.

I have a database with the name abcengine running on a local SQL Server. I need to back up this datbase to a file, send it over to a remote SQL Server to restore it.

There are two ways, which I’ve known, to back up a database. One of them is to use the Enterprise Manager to virsually back up, and another way is to use the Query Analyzer, writing the T-SQL code and run it.

In this case, I opted in to use the second option.

	TO DISK = 'C:\tmp\abcengine.bak'

Execute the above code in the Query Analyzer, and if everything works well, we’ll get a backup file of the database, abcengine.bak stored in C:\tmp folder.

Now, send that backup file to the remote server people, so that they can restore it on their SQL Server.

Before we should run the restore command, we need to find out the name of the mdf and ldf files in the abcengine.bak. The mdf is the data file, and the ldf is the log file. The reason we need to check this is because usually two SQL servers tend to store the database files on different locations.

	FROM DISK = 'c:\location_you_keep\abcengine.bak'

We should see the names used in the abcengine database for mdf and ldf files. Now, we’re ready to restore this backup to a new database.

	FROM DISK = 'c:\location_you_keep\abcengine.bak'
	WITH MOVE 'abcengine_Data' TO 'c:\MSSQL2K\abcengine_2.mdf',
	MOVE 'abcengine_log' TO 'c:\Backup\abcengine_2.ldf',

That’s it. It worked fine for me.

SQL Setup error on Vista: SQL Server Setup Failed to compile MOF

I was attempting to install MS SQL Server 2005 Express edition without success on a computer run Windows Vista Business. There was an error message mentioned “…failed to compile the Managed Object Format (MOF)…” I’ve tried about five times with various solutions; still it didn’t fix the problem.

As always, I found a working solution when I was about to give up on this installation. The problem was that the WMI repository was corrupted. There are two ways to fix this: one is to reinstall the WMI, and alternatively you can repair it instead.

I opted for the 2nd option. Here is how you can do it. Run the command prompt (cmd) as the administrator. Then execute this command:

winmgmt /salvagerepository

Wait for a lil while, you should see the following error message:

WMI repository salvage failed
Error code: 0x8007041B
Facility: Win32
Description: A stop control has been sent to a service that other running ser
vices are dependent on.

Then, re-execute the same command for one more time to finally repair the WMI repository.

C:\Windows\system32>winmgmt /salvagerepository
WMI repository has been salvaged

Once you’ve finished the above step, try to reinstall the SQL Server 2005 Express edition again. Please be sure to install the latest version of the SQL Server Express edition which is the SQL Server 2005 Service Pack 2.

Source: SQL Server Setup error on Vista