Create Backup of SQL DB

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?
sanjshah12Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Prashant SabnekarAVPCommented:
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
sanjshah12Author Commented:
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
Prashant SabnekarAVPCommented:
hey,
from the back up window, in the destination section select add and then select your appropriate location.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Anthony PerkinsCommented:
Make sure you have access from SQL Server to your box and use a UNC path, as in:
\\YourComputer\YourShare\Some.bak
0
Anthony PerkinsCommented:
P.S.  No points please.
0
sanjshah12Author Commented:
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
Prashant SabnekarAVPCommented:
acperkins >>> P.S.  No points please.

I didn't understand this
0
sanjshah12Author Commented:
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
RiteshShahCommented:
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
sanjshah12Author Commented:
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
RiteshShahCommented:
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
sanjshah12Author Commented:
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
RiteshShahCommented:
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
sanjshah12Author Commented:
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
wiljeCommented:
Since you backed up the database - and copied the backup file to your machine, now you need to perform a restore.
0
RiteshShahCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sanjshah12Author Commented:
Ritesh thanks all working!

Wilje thanks also!

I tried both and both work!
0
Anthony PerkinsCommented:
You are welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.