Sql-server – the best powershell script to restore an SQL Database

powershellsql server

To restore an SQL Server 2008 database, I would lile to be able to just do something like this in powershell:

./restore.ps1 DatabaseName.bak

Then the powershell script would by convention restore it to a database with name "DatabaseName". It would disconnect any user connected to this database so that it can restore the DB. It would store the mdf and ldf in the default location.

This would mainly be while developing on my personal machine. Just a quick way to restore a DB.

Anyone has such a script?

Thanks

Best Answer

I created a script and posted it on PoshCode which should meet your requirements. The script uses the SQLPSX sqlserver module. Note I had to add a restore file list only option to the invoke-sqlrestore function, so you'll need to grab the module from source code section rather than download section. This will be added to a future release of SQLPSX:

Module: http://sqlpsx.codeplex.com/SourceControl/changeset/view/58378#564810

Script: http://poshcode.org/2530

Related Solutions

Sql-server – T-SQL Script to copy a database

SSIS has a copy database function, so you would have to execute your T-SQL via SSIS, and then you could have a copy database as a step in that process.

SQL Server Database on External Hard Disk Drive – Setup Guide

Well, now I know the answer to my question!

According to http://support.microsoft.com/kb/304261, you may use SAN/NAS storage to store your databases' files using a TraceON flag. Something like:

DBCC TraceOn(1807);
GO

this command flags the 1807 TranceOn to let you use a UNC like "\Server-name\Path-to-Database-File.mdf" as the path to database files. Now you need to Create a folder on your removable USB HDD and use "Sharing and Security" to grant Full Control over that folder to "Network Service" or whatever user your SQL uses to interact with your Windows. Remember to remove everyone and Add Administrators too.

Now you're done; create the database and have fun!

DBCC TraceOn (1807);
Go

Use master;
GO

CREATE DATABASE [test001] 
    ON  PRIMARY (
        NAME = N'test001', 
        FILENAME = N'\\PC-Name-Where-Share-Is\TempDB\test001.mdf' , 
        SIZE = 2048KB , 
        FILEGROWTH = 1024KB
        )
    LOG ON ( 
        NAME = N'test001_log', 
        FILENAME = N'\\PC-Name-Where-Share-Is\TempDB\test001_log.ldf' , 
        SIZE = 1024KB , 
        FILEGROWTH = 10%
    )
GO

Best Answer

Related Solutions

Sql-server – T-SQL Script to copy a database

SQL Server Database on External Hard Disk Drive – Setup Guide

Related Topic