Restoring an SQL Server Database that is in use

Often we have to restore a database in sql server but that is in use.  The script below enables restore of database even if it is use. 


Adjust for your needs and use at your own risk.

USE [master]
GO
if exists (select * from sysdatabases where name='MY_DATABASE') begin
print 'taking database MY_DATABASE to single mode'
ALTER DATABASE MY_DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
end
GO
if exists (select * from sysdatabases where name='MY_DATABASE')
begin
print 'drop MY_DATABASE'
DROP DATABASE MY_DATABASE
end
go
USE [master]
GO
RESTORE DATABASE MY_DATABASE FROM
DISK = N'C:\MSSQL\Backup\MY_DATABASE_backup.bak' WITH FILE = 1,
MOVE N'DataTemplateSQL_dat' TO N'c:\MSSQL\DATA\MY_DATABASE.mdf',
MOVE N'DataTemplateSQL_log' TO N'c:\MSSQL\DATA\MY_DATABASE.LDF',
NOUNLOAD, STATS = 10
GO
use MY_DATABASE
go

Working with the new SQLLocalDB From Microsoft

SQLLocalDB  is a new, lightweight database engine from Microsoft.  You can get it from here.  I have downloaded it and installed the 64 bit version on my Windows 7 laptop.  However, after installation there are no shortcuts created so no gui to manage this "instance".

To start the database engine, open a command line window and type the following:
1.

cd "c:\Program Files\Microsoft SQL Server\110\Tools\Binn"

Substitute for your installation directory.
2. Type:

"SqlLocalDB create "SQL11Local" 11.0 -s"

This will create an sql server instance called "SQL11Local" and start it.
3. Type:

"SQLLocalDB info SQL11Local"

Note the Instance pipe name in the info window.  We will use that to connect to this instance from SQL Server Management Studio

Now start SQL management Studio and in the Server name textbox copy and paste the Instance pipe name from the info command results.  You should connect to the database instance and you can do all the usual things like create , delete and attach databases.

Generating Insert statements for MS Sql Server table data

The file below contains an adjusted script for generating insert statements for sql server table data. It was originally written by Narayana Vyas Kondreddi. You can find example usage inside the script.

sp_generate_inserts

Note that this procedure's name has the devilish sp_ prefix, which means that it better be created in the master database.  This way, it will be available to all databases in an sql server instance.  Otherwise you'd have to create it on each database on your server.