satya - Wednesday, February 01, 2012 3:38:03 PM

How can i backup and restore a SQL server database?

Search for: How can i backup and restore a SQL server database?

satya - Wednesday, February 01, 2012 3:44:42 PM

Here is a reasonable start

satya - Wednesday, February 01, 2012 3:45:24 PM

what version of sqlserver I have?


SQL Server 2008 R2

satya - Wednesday, February 01, 2012 3:45:53 PM

SQL Server 2008 R2 backup restore

Search for: SQL Server 2008 R2 backup restore

satya - Wednesday, February 01, 2012 3:49:06 PM

MSDN link: Copying Databases with Backup and Restore

satya - Wednesday, February 01, 2012 5:17:44 PM

Instructions from Bharat

in SQL Management studio, right click on the database and from TASKS menu, select BACKUP.

Backup Type: Full
Backup TO: DISK.
Note down where it is backing up, 
   OR REMOVE and ADD your own backup 
   location and file name (on your laptop).
Click OK

To Restore:

For Case II - FTP the backup file to JDServer before following the steps below.

For Case I - follow the steps below.

right click on destination database, Tasks->Restore->Database
(you can also right click on DATABASES->"Restore Database")

Source: Device
Click on the button labelled "..." on the right of the Device.
Select the backup file you created

Destination - select the existing database to 
   overwrite or type name of a new database to create.

All the way on the left, "Select a Page", click on FILES

Under column RESTORE AS, select the folder where you want to restore the database.

You need to type in new file names if you are not overwriting the database.

All the way on the left, "Select a Page", click on OPTIONS

Check "Overwrite existing database"

Click OK

satya - Thursday, February 02, 2012 12:45:38 PM

The default path for backup is


c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup

satya - Thursday, February 02, 2012 12:47:01 PM

I can change thsi to


C:\satya\data\data\akc-data-backups-only\sql-server\test-database\test-database.bak

satya - Thursday, February 02, 2012 12:50:47 PM

what copy-only backups in sqlserver

Search for: what copy-only backups in sqlserver

satya - Thursday, February 02, 2012 12:54:18 PM

what are back up sets in sql server

Search for: what are back up sets in sql server

satya - Thursday, February 02, 2012 12:54:53 PM

What is the meaning backup set expiry in sql server?

Search for: What is the meaning backup set expiry in sql server?

satya - Thursday, February 02, 2012 1:02:56 PM

I have used copy-only

the default is not copy-only. In such a case the database state has changed to remember when the back up took place, and where it is, and if continual partial backups can be done on top.

In my case I wanted to replicate a state that is fixed at a particular time. So I hope the copy-only is the right thing to do

satya - Thursday, February 02, 2012 1:04:29 PM

How did I restore?

I went to 'databases' node and chose to restore. I have typed a new database name as opposed to picking an existing database. I have also chosen a "device" to restore from and chose the filename that was backed up earier using "copy-only".

this created a database called "test2". Original is "test". I can see both databases.

satya - Thursday, February 02, 2012 1:04:48 PM

What are good sqlserver database backup strategies

Search for: What are good sqlserver database backup strategies

satya - Thursday, February 02, 2012 1:08:05 PM

Notes from Bharat on that topic

for a one time backup and restore (in order to copy to another server), we do not need to worry about that.

But once in production, you want to consider this.

Essentially, a backup file or tape may contain backups from several different databases, or multiple backups from the same database. Each successful backup is called a backup set.

Specially when backing up on TAPE or a limited sized disk that will be reused, the system will not automatically overwrite the backup with a new backup unless the existing backupset is has passed its expire date.

So if we have a backup strategy where all the backups are set to expire after 8 days with:

1. weekly FULL Backup,
2. daily incremental backup,

then we only need 8 backup sets (1 full and 7 days of incremental). On the 8th day, it will start overwriting 8 day old backups. But you will still have last 7 days of backups. However, you may not be able to retrieve backups from before that.

So essentially you can figure out how much disk space you need to store the backup.

You could even do weekly full and daily differential - longer time to backup, but shorter time to restore, as it needs only the full and the latest differential. The space requirements will be more in this case.

IF WE DO NOT specify the expire date, they will never expire, which means we will have to manually delete those in order to save space. These are good for monthly backups - so you can have permanent backups. These should really be moved offsite for permanent storage.

satya - Saturday, February 04, 2012 2:55:28 PM

More notes

You will need a user id that has permissions for you to restore

The userid that you use to restore may not have permissions to open the database. You have to ask the sysadmin to grant these rights every time you restore. You may want to get a script that can do this everytime if you are doing this often.

Restore may not restore previous users. You may have to define users again to suit the access rights.

satya - Sat May 12 2012 12:57:47 GMT-0400 (Eastern Daylight Time)

Notes on May 5th backup attempt


Specifying a filename is tricky
choose copy one
Don't choose tape