Backup and Restore Scripts for MSSQL Server 2005

MSSQL Server 2005 Management Studio (SSMS) is a very fine tool with many easy to use features. Personally I have always been one that wants to know how to use SQL scripts as well as the SSMS. I think it makes for a better more rounded DBA.

Below are two scripts. One for backing up your database and the second to restoring the database. The paths are for the default install on the C: drive so you will need to adjust for your installation. As always you should test these scripts before running on production servers.

/* Note: When using the following scripts
databasename = the database to backup
logfilename = name of the log file backed up without the “.bak” suffix. */

/* What database you are going to use this script on */
USE [databasename]
GO

/* The backup script */
BACKUP DATABASE [databasename] TO  DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\databasename.bak’
WITH NOFORMAT, INIT,  NAME = N’databasename-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

/* Shrink the backup */
DBCC SHRINKDATABASE(N’databasename’ )
GO

/* Restore the database */
RESTORE DATABASE [databasename] FROM  DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\databasename.bak’ WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

Advertisements

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s