Solved

Create Backup of SQL DB

Posted on 2009-06-30
18
705 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +2
18 Comments
 
LVL 4

Expert Comment

by:Prashant Sabnekar
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 4

Expert Comment

by:Prashant Sabnekar
ID: 24744502
hey,
from the back up window, in the destination section select add and then select your appropriate location.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 4

Expert Comment

by:Prashant Sabnekar
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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

752 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