SQL Server backup Using Windows command line

Question

My Windows Server 2003 got corrupted and I'm trying to repair it but before that I'm trying to create a backup of my SQL Server databases.

Can anyone please tell me which files do I need to copy from the Windows command line as I'm not familiar with SQL Server. Database files from which I can restore data.

Its an old server but data is important.

And also if I repair Windows server 2003 using repair disk will it effect on SQL Server files ?

http://postimg.org/image/5jsstbqmd/

When I start server I get this error.

The best way would be to do the backup in SQL Server itself - copying out the data and structure into a .bak file for each database. Just fiddling around with the data files isn't the preferred way to go .... (might end up being in a non-recoverable state) — marc_s
– marc_s, Commented Feb 22, 2015 at 15:26

Tarik · Accepted Answer · 2015-02-22 15:30:26Z

2

You can use this SQL command (adapt to your specific case):

--Back up the files in SalesGroup1:
BACKUP DATABASE YourDBName
   TO DISK = 'Z:\SQLServerBackups\BackupFileName.bck';
GO

See Backup in Transact-SQL for more details.

To run a SQL script from command line:

sqlcmd -S myServer\instanceName -i C:\myScript.sql

Before messing with anything, you could take a complete image of your hard drive using a tool such as clonezilla.

edited Feb 22, 2015 at 15:30

answered Feb 22, 2015 at 15:23

Tarik

11.2k2 gold badges31 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2015-02-22 15:25:52Z

0

I would get to the root of your disk and run

dir /a /s *.mdf

The .mdf file is the file extension that SQL Server uses, and that command will tell you where they are located. The log files are usually in the same directory.

As per your second question, the disk repair will only affect your database files if they are part of the corruption that is happening; which is quite likely if you were running a high I/O database when it crashed. I would definitely try and copy those files off before running a disk check.

edited Feb 22, 2015 at 15:25

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Feb 22, 2015 at 15:23

Sean Perryman

2514 silver badges15 bronze badges

1 Comment

marc_s Over a year ago

And while you're at it - please also include the .ldf (transaction log file) and .ndf (secondary data) files !!

Collectives™ on Stack Overflow

SQL Server backup Using Windows command line

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related