Solved

Create Backup of SQL DB

Posted on 2009-06-30
18
646 Views
Last Modified: 2012-05-07
Hi,

I am looking to create a backup (an exact copy with data of a db) the db is hosted on server but I need to make the copy on my local laptop.

I looked up some other Q and A in EE but cannot find what I'm looking for, I think I need to use backup to and include Mirror to

The DB to backup (hosted server) is called 'Test1' - could someone please help?
0
Comment
Question by:sanjshah12
  • 7
  • 4
  • 3
  • +2
18 Comments
 
LVL 3

Expert Comment

by:prashantsabnekar
ID: 24744407
To backup a database, do the following
1. Log in to the MS Sqlserver Management Studio
2. In the object Explorer, expand the Databases highlight the database you want to backup
3. Right click on that database (in your case it is Test1)
4. From the menu generated Select Tasks > Backup
5. by clicking ok button the back up will be created , the back up of a database id actually a .bak file whose location will be shown in the destination section of the back up dialog box.

Prashant Sabnekar
0
 

Author Comment

by:sanjshah12
ID: 24744476
Prashant, Thanks for the reply.

For the destiniation how do I select my desktop PC or laptop which is different from the hosted machine?
0
 
LVL 3

Expert Comment

by:prashantsabnekar
ID: 24744502
hey,
from the back up window, in the destination section select add and then select your appropriate location.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24744511
Make sure you have access from SQL Server to your box and use a UNC path, as in:
\\YourComputer\YourShare\Some.bak
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24744518
P.S.  No points please.
0
 

Author Comment

by:sanjshah12
ID: 24744575
ace - cool!, now that I have the backup of the DB as backupdb.bak how can I add this to my local SQL do I need to 'attach'?
0
 
LVL 3

Expert Comment

by:prashantsabnekar
ID: 24744606
acperkins >>> P.S.  No points please.

I didn't understand this
0
 

Author Comment

by:sanjshah12
ID: 24744636
Apologies, I have made a copy frm the server to my local PC but it does not show up in the Management Studio, so do I need to rename and rightclick attach DB?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24744791
you can run following script and can mention path there, you can simply give database name in WHERE clause and you will have backup of all databases given there.
DECLARE @BackupFolder AS varchar(250);

DECLARE @Path AS varchar(250);

DECLARE @DBName AS varchar(250);

DECLARE @CurDT AS datetime;

DECLARE @Status AS int;

SET @CurDT = getdate();

SET @Path = '\\UNC-ServerName\SQL-BACKUP\';

DECLARE crDatabases CURSOR

FOR

 select name from master..sysdatabases where name in ('adventureworks')

OPEN crDatabases

FETCH NEXT FROM crDatabases INTO @DBName

WHILE (@@FETCH_STATUS = 0)

BEGIN

 SET @BackupFolder = @Path 

  SET @BackupFolder = @BackupFolder + @DBName+'_'+CAST(DATEPART(hour,@CurDT) AS varchar(2))+'.'+CAST(DATEPART(minute,@CurDT) AS varchar(2))+'.0'+'.bak';

 BACKUP DATABASE @DBName

 TO  DISK = @BackupFolder 

 WITH INIT,  

 NOUNLOAD, SKIP, NOFORMAT 

   FETCH NEXT FROM crDatabases INTO @DBName

END

CLOSE crDatabases

DEALLOCATE crDatabases

 

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:sanjshah12
ID: 24745353
Ritesh,

I tried your script but because the DB is on a hosted server the path is invalid, I baced up the DB locally and copied to my local machine and renamed, when I tried to attach Database I recieved an error:

E:\temp\Test1DB Backup.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=5171

Any ideas?

Thanks
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24745390
file you are trying to attach is .MDF file and from my script you will get .BAK file which you have to restore. how did you get .MDF file? are you trying to copy .MDF and .LDF both file and attach in your server?
0
 

Author Comment

by:sanjshah12
ID: 24745483
Ritesh, after your script I tried and got this message:

Msg 3201, Level 16, State 1, Line 17
Cannot open backup device '\\tsclient\E\temp\Test1_15.8.0.bak'. Operating system error 67(error not found).
Msg 3013, Level 16, State 1, Line 17
BACKUP DATABASE is terminating abnormally.

I am using RDP into the hosted server.

But then tried to backup on the hosted server itself and copy the .bak file to my PC, renamed and tried to attach but no luck
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24745499
your given path should be accessible (\\tsclient\E\temp\) from your SQL Server, is it? no matter from where you are talking backup, SQL Server will try to access above path from itself, it should have proper permission as well.
0
 

Author Comment

by:sanjshah12
ID: 24745819
Ritesh,

OK I now have the file as 'Test1_15.42.0.bak' how do I attach this to my local pc as a db that I use without a network connection?

Regards,

Sanjay
0
 
LVL 7

Assisted Solution

by:wilje
wilje earned 50 total points
ID: 24750652
Since you backed up the database - and copied the backup file to your machine, now you need to perform a restore.
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 200 total points
ID: 24751728
first create database (I guess it is Test1 in your case) in your server with same MDF and LDF file structure, than put your .BAK file anywhere in your server (may be at D drive, as I explained in below command) and run following script in your SSMS.


use master
RESTORE DATABASE Test1
FROM DISK = N'D:\Test1_15.42.0.bak'
WITH RECOVERY, RESTRICTED_USER
GO
0
 

Author Comment

by:sanjshah12
ID: 24759424
Ritesh thanks all working!

Wilje thanks also!

I tried both and both work!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24759470
You are welcome.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now